Microsoft Dynamics CRM 4.0 Performance Optimization

When you run Microsoft Dynamics CRM 4.0, The asyncoperationbase table is greatly increased. If the table contains millions of records, the performance is slow.

See: http://support.microsoft.com/kb/968520/

However, this operation will delete the data involved in the expected sales report, affecting the normal use of the report. Therefore, after testing, I made the following changes for your reference only.

 

1) Stop IIS on the CRM server and back up your CRM Databases

 

2) create two indexes in the database:

 

Create nonclustered index crm_asyncoperation_cleanupcompleted

On [DBO]. [asyncoperationbase] ([statuscode], [statecode], [operationtype])

 

Go

 

Create nonclustered index crm_duplicaterecord_cleanupcompleted

On [DBO]. [duplicaterecordbase] ([asyncoperationid])

 

Go

 

3) Stop the CRM asynchronous service Microsoft Dynamics CRM async Service

 

4) Open SQL Server Management studio and run the following code:

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) and statecode = 3 and statuscode in (30, 32 ))

 

Or (operationtype in (10) and statecode = 3 and statuscode in (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

======================================

Appendix:

Optimizing and maintaining Microsoft Dynamics CRM 4.0

: Http://www.microsoft.com/downloads/details.aspx? Familyid = ba826cee-eddf-4d6e-842d-27fd654ed893 & displaylang = en