Hi all,

Performance issues can have many root cause and this article will not cover this.

For general CRM 4.0 Performance Optimization, the first thing you need to do is to go through are these documents:

Optimizing and Maintaining Microsoft Dynamics CRM 4.0
http://www.microsoft.com/downloads/details.aspx?FamilyID=ba826cee-eddf-4d6e-842d-27fd654ed893&displaylang=en

and also if you run SQL 2008:

Improving Microsoft Dynamics CRM Performance and Securing Data with Microsoft SQL Server 2008
http://www.microsoft.com/downloads/details.aspx?FamilyID=b5bb47a4-5ece-4a2a-a9b5-5435264f627d&DisplayLang=en

Apart from that, there are several hotfixes included in the Update Rollup 2 and 3 you should pay attention to.

The action plan to take advantage of these hotfixes and to maintain your CRM Database is the following:

A°) Install CRM 4.0 Update Rollup 3 (kb961768)
B°) Enable performance related Hotfixes (Pro-Active Action)
C°) Run additional cleanup script to address existing Database problems.

NOTE: About C°) before running SQL Cleanup scripts you might need to do a backup of your database in case you would run into any problem.
Also you shall ensure that you have the Max Degree Of Parallelism option greater than Zero (1 is just fine) if you are running a multi CPU SQL Server. This option is under the Advanced options of your SQL Server:

MaxDegree

Here is a list of Hotfixes and Articles you need to review:

B-1°) Performance can be affected because your AsyncOperationBase table contains millions of unnecessary records.

B-1.1°) Workflow related records (OperationType=10) cause the AsyncOperationTable and WorkflowLogBase table in the MSCRM database to grow too large

Fix included in Update Rollup 3, here is the related KB article:

968755    The AsyncOperationBase and WorkflowLogBase tables grow very large and performance issues occur when you use many workflows in CRM 4.0
http://support.microsoft.com/default.aspx?scid=kb;EN-US;968755

Read this article carefully, you need to enable it via registry key.

And easy way to identify if you are running into this issue is to check how many records are in the AsyncOperationBaseTable by runnning a simple Count(*) and checking how many records you have of OperationType = ‘10’ and Succeeded/Completed.

Total count of records:

Select Count(*) from AsyncOperationBase

Total count of unnecessary records:

Select Count(*) from AsyncoperationBase Where OperationType = 10 AND StateCode = 3 AND StatusCode = 30

You can make a quick ration and see how bad you are affected by this.

There is a link in this KB to another article (KB968520) you need to execute the script mentioned there to cleanup your database. 

B-1.2°) KB957871 The Workflow Expansion Task records (Operation Type=1) cause the AsyncOperationTable table to grow too large in CRM 4.0
http://support.microsoft.com/default.aspx?scid=kb;EN-US;957871

If you have the Update Rollup 2 or 3 you already have this hotfix, however it needs to be enabled via registry (please check the article).
Also note that this hotfix is Pro-Active only and prevent the issue to occur after fix installation. If your SQL Table is already full of these records you need to address this issue by running KB968520 clean-up scripts.
And easy way to identify if you are running into this issue is to check how many records are in the AsyncOperationBaseTable by runnning a simple Count(*) and checking how many records you have of OperationType = ‘1’ and Succeeded/Completed.

Total count of records:

Select Count(*) from AsyncOperationBase

Total count of unnecessary records:

Select Count(*) from AsyncoperationBase Where OperationType = 1 AND StateCode = 3 AND StatusCode = 30

You can make a quick ration and see how bad you are affected by this.


B-2°) SQL Blocking can occur if you are using E-mails heavily in CRM (ActivityParty, ActivityPointer tables contains millions of records)

KB959248 Microsoft Dynamics CRM 4.0 slows to unacceptable levels when you process e-mail messages by using the Microsoft Dynamics CRM E-mail Router
http://support.microsoft.com/default.aspx?scid=kb;EN-US;959248

If you have the Update Rollup 2 you already have this hotfix, in addition, the article also contains some SQL instructions you will need to follow to create INDEXES in order to improve performance (please check the article).

 

B-3°) MSCRM Database size is growing due to an increasing number of BulkDeleteIndependent_<GUID> tables

Under certain circumstances BulkDeleteIndependent_<GUID> temporary tables used by CRM to perform Bulk Delete operation are never deleted.
This issue has been fixed inside Update Rollup 2 however, there is no KB article associated with it.

Like scenario 1°) the hotfix is PRO-Active only and if you are already running into this issue, you will need to use the following SQL Cleanup script to remove the tables.

   1: -- Drop the Bulk Delete non metadata tables that had been created with [non-dbo] as owner. 
   2: -- This is to clean up any hanging tables during upgrade that were left due to a bug fixed in UR2
   3:  
   4: DECLARE @name varchar(255)
   5: DECLARE @dropCommand varchar(255)
   6:  
   7: DECLARE tableCursor CURSOR FAST_FORWARD FOR
   8:     SELECT '[' + SU.name + '].' + SO.name name FROM dbo.sysobjects SO, dbo.sysusers SU 
   9:     WHERE SO.xtype='U'AND SO.name LIKE 'BulkDelete%[_]%' AND SO.UId = SU.UId AND SU.name <> 'dbo'
  10:  
  11: OPEN tableCursor
  12:  
  13: FETCH next FROM tableCursor INTO @name
  14: WHILE @@fetch_status=0
  15: BEGIN
  16:     SET @dropcommand = N'drop table ' + @name
  17:     EXECUTE(@dropcommand)
  18:     FETCH next FROM tableCursor INTO @name
  19: END
  20:  
  21: CLOSE tableCursor
  22: DEALLOCATE tableCursor

 

B-4°) The data column of some AsyncOperationBase table records is not cleared once these records are completed.

This issue is similar to 1°) the PRO-Active hotfix is included in the Update Rolllup 2 however there is a need for a SQL Scripts to clear up manually the Data field of impacted records.

The related KB article is this one:

954929 Performance is poor when you change a user from one business unit to another in Deployment Manager in Microsoft Dynamics CRM 4.0
http://support.microsoft.com/default.aspx?scid=kb;EN-US;954929

In order to know the number of records impacted by this issue, you can run the following script:

   1: select Count(*) from AsyncOperationBase Where
   2: (OperationType <> '17'    -- ImportingFile
   3: AND OperationType <> '23'  -- BulkDeleteChild
   4: AND OperationType <> '13') -- BulkDelete
   5: AND StateCode = '3'       -- Completed
   6: AND StatusCode = '30'     -- Succeeded
   7: AND Data is NOT NULL

In order to cleanup the Database from these records, we suggest you to run the script mentioned in KB968520

C°) General clean-up script you can run after you check above points:

968520 Performance is slow if the AsyncOperationBase table becomes too large in Microsoft Dynamics CRM 4.0
http://support.microsoft.com/default.aspx?scid=kb;EN-US;968520

NOTE: Once you will have checked all these steps, you might need to re-index and update the statistics of the two above mentioned tables.

- You must not do this during Working hours because it will take a lot of resources on the SQL Server.
- You need to ensure that the Recover Model is set to Simple during this operation to prevent the Log file to grow to much. This is a Database option

RecoveryModel

You can run the following script to perform this operation:

   1: -- Rebuild Indexes & Update Statistics on AsyncOperationBase Table
   2: ALTER INDEX ALL ON AsyncOperationBase REBUILD WITH
   3: (FILLFACTOR = 80, ONLINE = OFF,SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF)
   4: -- Rebuild Indexes & Update Statistics on WorkflowLogBase Table
   5: ALTER INDEX ALL ON WorkflowLogBase REBUILD WITH
   6: (FILLFACTOR = 80, ONLINE = OFF,SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF)

NB: Run one statement after the other, wait for the 1st one to complete before executing the other one

Hope this helps

Benjamin LECOQ