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

Article ID: 968520 - Last Review: July 23, 2009 - Revision: 4.0

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

Hotfix Download Available
View and request hotfix downloads

View products that this article applies to.

On This Page

• SYMPTOMS
• RESOLUTION
  • Improving the performance of the deletion script
• MORE INFORMATION

Expand all | Collapse all

SYMPTOMS

When you run Microsoft Dynamics CRM 4.0, the AsyncOperationBase table grows to b...

When you run Microsoft Dynamics CRM 4.0, the AsyncOperationBase table grows to be very large. When the table contains millions of records, performance is slow.
Additionally, errors that resemble the following are logged on the Microsoft Dynamics CRM server in the application event log:

Event Type: Error
Event Source: MSCRMDeletionService
Event Category: None
Event ID: 16387
Date: 2009/01/26
Time: 11:41:54 AM
User: N/A
Computer: CRMSERVER
Description: Error: Deletion Service failed to clean up table=CleanupInactiveWorkflowAssembliesProcedure For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Back to the top

RESOLUTION

To resolve this problem, perform a cleanup of the AsyncOperationBase table by ru...

To resolve this problem, perform a cleanup of the AsyncOperationBase table by running the following script against the <OrgName>_MSCRM database, where <OrgName> is a placeholder for the actual name of your organization.
WARNING: Before cleaning up the data please note that completed system jobs have business value in some cases and need to be stored for a long period, so please discuss with your orgnization's administration staff first.
System Jobs effected:

• SQM data collection. - Software Quality Metrics which 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 kb articles in CRM.

If recurring jobs have been cancelled they will be removed.
Notes

• The SQL script in this KB article is one-time effort only. You can add this as a SQL job to run on a recurring basis on either a nightly, weekly, or monthly basis. As your CRM runs, you need to either apply this KB article on a weekly basis depending on your business needs, or apply the solution by writting custom BULK DELETE jobs (please refer to our CRM SDK documentation on the BulkDeleteRequest.QuerySet Property, BulkDeleteRequest Class, and Order of Deletion).
• Make sure that the AsyncOperation records for workflows and the corresponding records are deleted from the WorkflowLogBase 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)

declare @DeleteRowCount int
Select @DeleteRowCount = 2000
declare @DeletedAsyncRowsTable table (AsyncOperationId uniqueidentifier not null)
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 AsyncOperationBase From AsyncOperationBase A, @DeletedAsyncRowsTable d
            where A.AsyncOperationId = d.AsyncOperationId            
 
            delete @DeletedAsyncRowsTable
      end
 
      commit
end

Back to the top

Improving the performance of the deletion script

• To improve overall Microsoft CRM performance, schedule the Microsoft CRM Deletion Service to run during off-peak hours for Microsoft CRM. By default, the service runs at the time that Microsoft CRM was installed. However, you can run the service to run at 10:00 PM instead of at the default time. To do this, use the Microsoft CRM ScaleGroup Job Editor. For more information, visit the following MSDN Web site:

  http://code.msdn.microsoft.com/ScaleGroupJobEditor/Release/ProjectReleases.aspx?ReleaseId=676 (http://code.msdn.microsoft.com/ScaleGroupJobEditor/Release/ProjectReleases.aspx?ReleaseId=676)

  Note This action does not directly affect the performance of the script.

• To improve the performance of the deletion scripts in this article and to improve the Microsoft CRM DeletionService code that runs similar deletes, add the following four indexes to the OrganizationName_MSCRM database before you run the deletion script in this article:

  CREATE NONCLUSTERED INDEX CRM_AsyncOperation_CleanupCompleted
  ON [dbo].[AsyncOperationBase] ([StatusCode],[StateCode],[OperationType])
  GO
  
  CREATE NONCLUSTERED INDEX CRM_WorkflowLog_AsyncOperationID ON [dbo].[WorkflowLogBase] ([AsyncOperationID])
  GO 
  
  CREATE NONCLUSTERED INDEX CRM_DuplicateRecord_AsyncOperationID ON [dbo].[DuplicateRecordBase] ([AsyncOperationID])
  GO
  
  CREATE NONCLUSTERED INDEX CRM_BulkDeleteOperation_AsyncOperationID ON [dbo].[BulkDeleteOperationBase]
  (AsyncOperationID)
  GO

  Note If you do not add these indexes, the deletion script may take hours to run.

• Stop the Microsoft CRM Asynchronous Processing Service while you run this script.
• OPTIONAL: Rebuild the following indexes and update statistics:

  -- Rebuild Indexes & Update Statistics on AsyncOperationBase Table 
  ALTER INDEX ALL ON AsyncOperationBase REBUILD WITH (FILLFACTOR = 80, ONLINE = OFF,SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF)
  GO 
  -- Rebuild Indexes & Update Statistics on WorkflowLogBase Table 
  ALTER INDEX ALL ON WorkflowLogBase REBUILD WITH (FILLFACTOR = 80, ONLINE = OFF,SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF)
  
  GO

• OPTIONAL: Update Statistics with Full Scan on all the tables involved with this query using the below commands (preferably at off peak hours):

  UPDATE STATISTICS [dbo].[AsyncOperationBase] WITH FULLSCAN
  UPDATE STATISTICS [dbo].[DuplicateRecordBase] WITH FULLSCAN
  UPDATE STATISTICS [dbo].[BulkDeleteOperationBase] WITH FULLSCAN
  UPDATE STATISTICS [dbo].[WorkflowCompletedScopeBase] WITH FULLSCAN
  UPDATE STATISTICS [dbo].[WorkflowLogBase] WITH FULLSCAN
  UPDATE STATISTICS [dbo].[WorkflowWaitSubscriptionBase] WITH FULLSCAN

• OPTIONAL: Change the MSCRM database's recovery model to Simple to avoid excess generation of SQL server logs. For SQL server 2005, login to the Microsoft SQL Server Management Studio as Administrator, right click your <org_name>_MSCRM database, click Properties, click Options, and then click Recovery Model. Mark Simple, and then click OK. After running this script the initial time, the <org_name>_MSCRM database recovery model should be switched back to FULL for the best data recoverability model.

To determine the number of records to be deleted by the script in this article, run the following count script against the OrganizationName_MSCRM database:

Select Count(AsyncOperationId)
from AsyncOperationBase WITH (NOLOCK)
where OperationType in (1, 9, 12, 25, 27, 10) 
AND StateCode = 3 AND StatusCode IN (30,32) 

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