Welcome to the first in a series of articles regarding the CRM Asynchronous Service and its inner workings.

The Microsoft Dynamics CRM Asynchronous service is important for maintaining efficient system performance and scalability. The service executes long-running operations independent of the main CRM core operations. It is a managed queue used for execution of things like workflows, asynchronous plug-ins, and other operations like bulk import and duplicate detection.

Have you ever found a large number of workflows in a Waiting Status when reviewing CRM System Jobs? Then this post is for you!

In today's installment, we are going to cover the various OperationTypeCodes, StatusCodes and StateCodes used in the service and offer some tips for troubleshooting when the AsyncOperationBase is experiencing issues with queries poorly performing because of the table's size.

Per the CRM SDK metadata documentation, here is a list of the OperationTypeCodes, StateCodes, and StatusCodes.

It is best to keep the AsyncOperationBase table under one million records if possible. To see the overall size of the AsyncOperationBase table, run the below T-SQL against the CRM organization database.

SELECT
SCH.name AS [SchemaName]
, SO.name AS [TableName]
, UT.RecordCount AS [RowCount]
, UT.ReservedPageCount* 8 / 1024 AS [Reserved_MB]
, UT.Data * 8 / 1024 AS [Data_MB]
, CASE WHEN UT.used > UT.data THEN UT.used - UT.data ELSE 0 END * 8 / 1024 AS [IndexSize_MB]
, CASE WHEN UT.ReservedPageCount > UT.used THEN UT.ReservedPageCount - UT.used ELSE 0 END * 8 / 1024 AS [Unused_MB]
FROM (
SELECT
PS.object_id,
SUM(CASE WHEN PS.index_id < 2 THEN row_count ELSE 0 END) AS [RecordCount],
SUM(PS.reserved_page_count) AS [ReservedPageCount],
SUM(CASE WHEN PS.index_id < 2 THEN PS.in_row_data_page_count + PS.lob_used_page_count + PS.row_overflow_used_page_count ELSE PS.lob_used_page_count + PS.row_overflow_used_page_count END) AS [Data],
SUM(PS.used_page_count) AS used
FROM sys.dm_db_partition_stats PS
GROUP BY PS.object_id
) AS UT
INNER JOIN sys.all_objects SO ON UT.object_id = SO.object_id
INNER JOIN sys.schemas SCH ON SO.schema_id = SCH.schema_id
WHERE SO.type <> 'S' and SO.type <> 'IT'
ORDER BY SO.name

If the number of records is equal to or greater than one million, the CRM asynchronous services can begin to show signs of degradation. If this is the case, run the below query to find out what OperationType and StatusCode is responsible for the higher record count.

SELECT OperationType, StatusCode, StateCode, COUNT(*) AS [RecordCount]
FROM AsyncOperationBase
GROUP BY OperationType, StatusCode, StateCode

Depending on the result, you might need to drill in further to determine what month and year the records began to increase on to better troubleshoot. You can run the below query to find out this information.

SELECT Name, DATEPART(YEAR,CreatedOn) AS [Year], DATEPART(MONTH,CreatedOn) AS [Month], OperationType, StatusCode, StateCode, Count(*) AS [RecordCount]
FROM AsyncOperationBase
WHERE StartedOn IS NOT NULL
GROUP BY Name, DATEPART(YEAR,CreatedOn), DATEPART(MONTH,CreatedOn), OperationType, StatusCode, StateCode

If you find a large number of waiting workflows in a Waiting StatusCode, it may be necessary to review the workflow to see if there is a logic issue or if the Waiting status is legitimate. From the above query for the above report, you can use the CRM Advanced Find to further drill in to the workflows. Here is an example of what criteria to enter in the window:

If the record count is at one million or greater and the there are no issues with the logic of the workflows in waiting, it is likely that the AsyncOperationBase is experiencing issues with queries poorly performing because of the table’s size. To address this issue, please follow the instructions in this Microsoft Dynamics CRM article to clean up OperationTypeCodes of 1, 9, 10, 12, 25, 27 with StatusCodes of 30, 32.

If you need assistance with implementing tips, please do not hesitate to reach out to our CRM Support Team by opening up a Support Request through our PowerCare Portal.

That’s all for the blog today! Make sure you stay tuned to the blog, our webinars, and our educational events so that you are up-to-date with the latest and greatest information about CRM 2016.

Happy CRM’ing!