Returning Waiting Workflows in CRM

Our CRM system seems to have slowed over time. One of the reasons is an increase in the number of workflows that are in a waiting state.

I have a SQL script that I ran against the company_MSCRM database to return a list of all the workflow names that were in the wait state, exported the list to Excel and subtotaled it to get a unique count for the number of each workflow. Sort by count and you get a good list of where you need to start optimizing workflows to hit and stop rather than wait for something to happen.

In our case, the number 1 culprit was a workflow that waits for the regarding attribute to be set to an account rather than a case. As a service organization, we try to push all of our service work thru cases. Instead of doing a check and then existing, the workflow ‘waits’ for the regarding to equal an account. This means that every open activity in CRM has a live workflow waiting for the regarding to equal an account.

Here’s the SQL script I used to return the waiting workflow names.

Select asyncoperation.Name
from asyncoperation
where StateCode = 1
    and RecurrenceStartTime is null
    and DeletionStateCode = 0
ORDER BY asyncoperation.Name

This code is based on an article by JonSCRM on MSDN.