Looking for PowerObjects? Don’t worry, you’re in the right place! We’ve been part of HCL for several years, and we’ve now taken the final step in our acquisition journey: moving our website to the HCL domain. Nothing else is changing – we are still fanatically focused on Microsoft Business Applications!

PowerObjects Blog 

for Microsoft Business Applications

CRM Asynchronous Service Performance: Code Mania

Post Author: Joe D365 |

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.

ObjectTypeCode ObjectTypCode Name
1 System Event
2 Bulk Email
3 Import File Parse
4 Transform Parse Data
5 Import
6 Activity Propagation
7 Duplicate Detection Rule Publish
8 Bulk Duplicate Detection
9 SQM Data Collection
10 Workflow
11 Quick Campaign
12 Matchcode Update
13 Bulk Delete
14 Deletion Service
15 Index Management
16 Collect Organization Statistics
17 Import Subprocess
18 Calculate Organization Storage Size
19 Collect Organization Database Statistics
20 Collection Organization Size Statistics
21 Database Tuning
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
45 Update Solution
46 Regenerate Entity Row Count Snapshot Data
47 Regenerate Read Share Snapshot Data
50 Outgoing Activity
51 Incoming Email Processing
52 Mailbox Test Access
53 Encryption Health Check
54 Execute Async Request
49 Post to Yammer
56 Update Entitlement States
StatusCode Description
0 Waiting For Resources
10 Waiting
20 In Progress
21 Pausing
22 Canceling
30 Succeeded
31 Failed
32 Canceled
StateCode Description
0 Ready
1 Suspended
2 Locked
3 Completed

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.

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]
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
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'

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
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:

CRM Asynchronous Service

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!

By Joe D365
Joe D365 is a Microsoft Dynamics 365 superhero who runs on pure Dynamics adrenaline. As the face of PowerObjects, Joe D365’s mission is to reveal innovative ways to use Dynamics 365 and bring the application to more businesses and organizations around the world.

PowerObjects Recommends