In this blog, we highlight a lesson learned when we used a Dynamics 365 Customer Engagement online data source connector, called Instance Web API, for Power BI reports.
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.
|3||Import File Parse|
|4||Transform Parse Data|
|7||Duplicate Detection Rule Publish|
|8||Bulk Duplicate Detection|
|9||SQM Data Collection|
|16||Collect Organization Statistics|
|18||Calculate Organization Storage Size|
|19||Collect Organization Database Statistics|
|20||Collection Organization Size Statistics|
|22||Calculate Organization Maximum Storage Size|
|23||Bulk Delete Subprocess|
|24||Update Statistic Intervals|
|25||Organization Full Text Catalog Index|
|26||Database log backup|
|27||Update Contract States|
|28||DBCC SHRINKDATABASE maintenance job|
|29||DBCC SHRINKFILE maintenance job|
|30||Reindex all indices maintenance job|
|31||Storage Limit Notification|
|32||Cleanup inactive workflow assemblies|
|35||Recurring Series Expansion|
|38||Import Sample Data|
|40||Goal Roll Up|
|41||Audit Partition Creation|
|42||Check For Language Pack Updates|
|43||Provision Language Pack|
|44||Update Organization Database|
|46||Regenerate Entity Row Count Snapshot Data|
|47||Regenerate Read Share Snapshot Data|
|51||Incoming Email Processing|
|52||Mailbox Test Access|
|53||Encryption Health Check|
|54||Execute Async Request|
|49||Post to Yammer|
|56||Update Entitlement States|
|0||Waiting For Resources|
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.