Performance is slow if the AsyncOperationBase table becomes too large in Microsoft Dynamics CRM

Improving the performance of the deletion script

• To improve overall Microsoft Dynamics CRM performance, schedule the Microsoft Dynamics CRM Deletion Service to run during off-peak hours for Microsoft Dynamics CRM. By default, the service runs at the time that Microsoft Dynamics CRM was installed. However, you can set the service to run at 10:00 PM instead of at the default time. To do this, use the Microsoft Dynamics CRM ScaleGroup Job Editor. For more information, visit the following CodePlex website:

  http://crmjobeditor.codeplex.com/Notes

  1. This action does not directly affect the performance of the script.

  2. The job editor for Microsoft Dynamics CRM 4.0 has been depecrated and is no longer available.

• To improve the performance of the deletion scripts in this article and to improve the Microsoft Dynamics CRM Deletion Service code that runs similar deletions, add the following three indexes to the OrganizationName_MSCRM database before you run the deletion script in this article:
  

  CREATE NONCLUSTERED INDEX CRM_WorkflowLog_AsyncOperationID ON [dbo].[WorkflowLogBase] ([AsyncOperationID])
  GO 
  
  CREATE NONCLUSTERED INDEX CRM_DuplicateRecord_AsyncOperationID ON [dbo].[DuplicateRecordBase] ([AsyncOperationID])
  GO
  
  CREATE NONCLUSTERED INDEX CRM_BulkDeleteOperation_AsyncOperationID ON [dbo].[BulkDeleteOperationBase]
  (AsyncOperationID)
  GO
  

  Note If you do not add these indexes, the deletion script may take hours to run.
  
  

• Stop the Microsoft Dynamics CRM Asynchronous Processing Service while you run this script.

• Optional Rebuild the following indexes and update statistics:

  -- Rebuild Indexes & Update Statistics on AsyncOperationBase Table 
  ALTER INDEX ALL ON AsyncOperationBase REBUILD WITH (FILLFACTOR = 80, ONLINE = OFF,SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF)
  GO 
  -- Rebuild Indexes & Update Statistics on WorkflowLogBase Table 
  ALTER INDEX ALL ON WorkflowLogBase REBUILD WITH (FILLFACTOR = 80, ONLINE = OFF,SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF)
  
  GO

• Optional Update Statistics with Full Scan on all the tables that are involved with this query by using the following commands (preferably at off peak hours):

  UPDATE STATISTICS [dbo].[AsyncOperationBase] WITH FULLSCAN
  UPDATE STATISTICS [dbo].[DuplicateRecordBase] WITH FULLSCAN
  UPDATE STATISTICS [dbo].[BulkDeleteOperationBase] WITH FULLSCAN
  UPDATE STATISTICS [dbo].[WorkflowCompletedScopeBase] WITH FULLSCAN
  UPDATE STATISTICS [dbo].[WorkflowLogBase] WITH FULLSCAN
  UPDATE STATISTICS [dbo].[WorkflowWaitSubscriptionBase] WITH FULLSCAN

• Optional Change the MSCRM database's recovery model to Simple to avoid excess generation of Microsoft SQL Server logs. For SQL Server 2005, log on to the Microsoft SQL Server Management Studio as Administrator, right-click your <org_name>_MSCRM database, click Properties, click Options, and then click Recovery Model. Mark Simple, and then click OK. After you run this script the first time, the <org_name>_MSCRM database recovery model should be switched back to FULL for the best data-recoverability model.

• To increase performance of the script, the @DeleteRowCount value of 10,000 can be reduced

To determine the number of records to be deleted by the script in this article, run the following count script against the OrganizationName_MSCRM database:

                
Select Count(AsyncOperationId)from AsyncOperationBase WITH (NOLOCK)
where OperationType in (1, 9, 12, 25, 27, 10) 
AND StateCode = 3 AND StatusCode IN (30,32) 

Script error

When you run the cleanup script, you may receive an error message that resembles the following:

If you receive this error message, stop the cleanup script, and then follow these steps to remove the remaining WorkflowWaitSubscription records that exist for completed or canceled workflows. These records should no longer exist, because they should have been deleted when the workflows were completed or canceled. You should not see any records that are returned from this query. Anything left in the WorkflowWaitSubscriptionBase table that appears in this query is an orphaned record. You cannot delete these records through the UI because the Microsoft CRM Async process is in a canceled or completed state.

The following script will verify how many orphaned WorkflowWaitSubscriptionBase records exist for completed and canceled workflow records:

select count(*) from workflowwaitsubscriptionbase WITH (NOLOCK) 

where asyncoperationid in 

(Select asyncoperationid from AsyncOperationBase WITH (NOLOCK) 

where OperationType in (1, 9, 12, 25, 27, 10) 

AND StateCode = 3 AND StatusCode IN (30,32)) 

The following script will delete WorkflowWaitSubscriptionBase records for stranded WorkflowWaitSubscriptionBase records for completed and canceled workflow records:

delete from workflowwaitsubscriptionbase 
where asyncoperationid in(Select asyncoperationidfrom AsyncOperationBase
where OperationType in (1, 9, 12, 25, 27, 10) 
AND StateCode = 3 AND StatusCode IN (30,32))

After this delete statement is executed, the AsyncoperationBase and Workflow cleanup script will complete successfully.