POWEROBJECTS

718 Washington Ave. N. Suite #101
Minneapolis, MN 55401
View map and all Locations

Send us a message

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

CRM Asynchronous Service

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.

ObjectTypeCodeObjectTypCode Name
1System Event
2Bulk Email
3Import File Parse
4Transform Parse Data
5Import
6Activity Propagation
7Duplicate Detection Rule Publish
8Bulk Duplicate Detection
9SQM Data Collection
10Workflow
11Quick Campaign
12Matchcode Update
13Bulk Delete
14Deletion Service
15Index Management
16Collect Organization Statistics
17Import Subprocess
18Calculate Organization Storage Size
19Collect Organization Database Statistics
20Collection Organization Size Statistics
21Database Tuning
22Calculate Organization Maximum Storage Size
23Bulk Delete Subprocess
24Update Statistic Intervals
25Organization Full Text Catalog Index
26Database log backup
27Update Contract States
28DBCC SHRINKDATABASE maintenance job
29DBCC SHRINKFILE maintenance job
30Reindex all indices maintenance job
31Storage Limit Notification
32Cleanup inactive workflow assemblies
35Recurring Series Expansion
38Import Sample Data
40Goal Roll Up
41Audit Partition Creation
42Check For Language Pack Updates
43Provision Language Pack
44Update Organization Database
45Update Solution
46Regenerate Entity Row Count Snapshot Data
47Regenerate Read Share Snapshot Data
50Outgoing Activity
51Incoming Email Processing
52Mailbox Test Access
53Encryption Health Check
54Execute Async Request
49Post to Yammer
56Update Entitlement States
StatusCodeDescription
0Waiting For Resources
10Waiting
20In Progress
21Pausing
22Canceling
30Succeeded
31Failed
32Canceled
StateCodeDescription
0Ready
1Suspended
2Locked
3Completed

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:

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!

Avatar for Joe D365

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.