Below Article provides the deletion script to improve the Performance by deleting the records with ASync Operation types if the status code is either Succeeded (30) or Canceled (32).

https://support.microsoft.com/en-us/kb/968520

We had a requirement where we need to delete the records which has status code of Failed (31), so we added the Failed status code to the script and executed on the Organization_MSCRM database which further reduced the records on the ASyncOperationBase table.

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,31,32)

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