Hi LuckyP,
If the AsyncOperationBase is slow due to a "Bulk Delete"
To resolve this problem, perform a cleanup of the AsyncOperationBase table by running the following script against the<OrgName>_MSCRM database, where the placeholder<OrgName> represents the actual name of your organization.
Warning Before you clean up the data, be aware that completed system jobs have business value in some cases and have to be stored for a long period. Therefore, you should discuss this with your organization's administration staff first.
System jobs that are affected:
•SQM data collection. Software Quality Metrics collects data for the customer experience program.
•Update Contract States SQL job. This job runs one time per day at midnight. This job sets the expired contracts to a state of Expired.
•Organization Full Text Catalog Index. Populates full text index in db for searching Microsoft Knowledge Base articles in CRM.
If recurring jobs were canceled, they will be removed.
•Make sure that only the following Async operation types are deleted if the state code of the types is 3 and the status code of the types is 30 or 32: •Workflow Expansion Task (1)
•Collect SQM data (9)
•PersistMatchCode (12)
•FullTextCatalogIndex (25)
•UpdateContractStates (27)
•Workflow (10
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)
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
Source: support.microsoft.com/.../en-us
Best Regards,
