CRM – Performance – AsyncOperationBase Table

Quite a while back had some issues upgrading a CRM instance. It turned out the amount of ASync operations during the lifetime of the CRM application had generated quite a lot of entries. (millions). For some reason I had a hard time find the root cause of this, which turned out to be a lot more general than just relating to an upgrade. I rarely see CRM installations where a clean up job is created, so most CRM installations will actually suffer from large amount of async operations stored.

Have a look at your AsyncOperationBase table and see how many entries are there. You might be surprised.

Below are the SQL lines I ran/set up as a maintenance plan in order to reduce the number. Depending on the number of records, it might be necessary to run the scripts a few times.

Code is from referenced KB article:

IF EXISTS (SELECT name from sys.indexes
                  WHERE name = N'CRM_AsyncOperation_CleanupCompleted')
      DROP Index AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted
CREATE NONCLUSTERED INDEX CRM_AsyncOperation_CleanupCompleted
ON [dbo].[AsyncOperationBase] ([StatusCode],[StateCode],[OperationType])

 declare @DeleteRowCount int = 10000
 declare @rowsAffected int
 declare @DeletedAsyncRowsTable table (AsyncOperationId uniqueidentifier not null primary key)
 insert into @DeletedAsyncRowsTable(AsyncOperationId)
 Select top (@DeleteRowCount) AsyncOperationId from AsyncOperationBase
  OperationType in (1, 9, 12, 25, 27, 10) 
  AND StateCode = 3 
  AND StatusCode in (30, 32)
  /*AND CompletedOn <= DATEADD(mm, -1, GETDATE())*/
 select @rowsAffected = @@rowcount 
 delete poa from PrincipalObjectAccess poa 
   join WorkflowLogBase wlb on
    poa.ObjectId = wlb.WorkflowLogId
   join @DeletedAsyncRowsTable dart on
    wlb.AsyncOperationId = dart.AsyncOperationId
 delete WorkflowLogBase from WorkflowLogBase W, @DeletedAsyncRowsTable d
  W.AsyncOperationId = d.AsyncOperationId             
 delete BulkDeleteFailureBase From BulkDeleteFailureBase B, @DeletedAsyncRowsTable d
  B.AsyncOperationId = d.AsyncOperationId
 delete WorkflowWaitSubscriptionBase from WorkflowWaitSubscriptionBase WS, @DeletedAsyncRowsTable d
  WS.AsyncOperationId = d.AsyncOperationID 
 delete AsyncOperationBase From AsyncOperationBase A, @DeletedAsyncRowsTable d
  A.AsyncOperationId = d.AsyncOperationId
 /*If not calling from a SQL job, use the WAITFOR DELAY*/
 if(@DeleteRowCount > @rowsAffected)
  WAITFOR DELAY '00:00:02.000'

EDIT: 26-February-2015;
1: Support article code was updated for a more smooth execution.
2: Added required retention time on records of 1 month;