Tag Archives: Asyncoperation

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

When you run Microsoft Dynamics CRM 4.0 or Microsoft Dynamics CRM 2011, the AsyncOperationBase table grows to be very large. When the table contains millions of records, performance is slow.

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.

Notes

  • For Microsoft Dynamics CRM 4.0 The SQL script in this Knowledge Base article is a one-time effort only. You can add this as a SQL job to run on a recurring nightly, weekly, or monthly basis. As your CRM runs, you have to either apply this article weekly, depending on your business needs, or apply the solution by writing custom BULK DELETE jobs. (Refer to our CRM SDK documentation on the BulkDeleteRequest.QuerySet property, on the BulkDeleteRequest class, and on the order of deletion).
  • For Microsoft Dynamics CRM 2011 The SQL script in this Knowledge Base article is a one-time effort only. You can add this as a SQL job to run on a recurring nightly, weekly, or monthly basis. As your CRM runs, you have to either apply this article weekly, depending on your business needs, or apply the solution by using BULK DELETE jobs by defining a job by using the BULK DELETE wizard.
  • Make sure that the AsyncOperation records for workflows and the corresponding records are deleted from theWorkflowLogBase object.
  • Make sure that all the corresponding bulkdeletefailure records are deleted.
  • 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

declare @DeleteRowCount int
Select @DeleteRowCount = 2000
declare @DeletedAsyncRowsTable table (AsyncOperationId uniqueidentifier not null primary key)
declare @continue int, @rowCount int
select @continue = 1
while (@continue = 1)
begin      
begin tran      
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 @rowCount = 0
      Select @rowCount = count(*) from @DeletedAsyncRowsTable
      select @continue = case when @rowCount <= 0 then 0 else 1 end      
        if (@continue = 1)        begin
            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             
            delete @DeletedAsyncRowsTable      
end       
commit
end
--Drop the Index on AsyncOperationBase
DROP INDEX AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted

 

Original Url

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>