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: http://support.microsoft.com/kb/968520/en-us


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

while(1=1)
begin
 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
 where 
  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
 where 
  W.AsyncOperationId = d.AsyncOperationId             
 delete BulkDeleteFailureBase From BulkDeleteFailureBase B, @DeletedAsyncRowsTable d
 where 
  B.AsyncOperationId = d.AsyncOperationId
 delete WorkflowWaitSubscriptionBase from WorkflowWaitSubscriptionBase WS, @DeletedAsyncRowsTable d
 where 
  WS.AsyncOperationId = d.AsyncOperationID 
 delete AsyncOperationBase From AsyncOperationBase A, @DeletedAsyncRowsTable d
 where 
  A.AsyncOperationId = d.AsyncOperationId
 /*If not calling from a SQL job, use the WAITFOR DELAY*/
 if(@DeleteRowCount > @rowsAffected)
  return
 else
  WAITFOR DELAY '00:00:02.000'
end

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;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s