A DBA's Role with Respect to Improving Microsoft Dynamics CRM 4.0 Performance

1linerForward: here's a webinar from Microsoft regarding this subject, the following is a summary of what was required to improve CRM performance.  

Microsoft's Customer Relationship Management platform runs on SQL Server and falls under most of the typical performance improvement techniques (and should be regularly checked with tools like Activity Monitor, or Server Statistics and Performance), but a few things came up for the DBA supporting CRM to focus on. Please note, this work is performed closely with your local friendly CRM application server system administrator.

First, a major server configuration preference we noticed, that may be different from your typical parameters, was setting the maximum degree of parallelism to 1. Memory wise, CRM is pretty memory intensive, so a typical instance should have at least 12GB to 18GB physical RAM available if the application is [hopefully since it is quite good] widely used.

As far as the CRM system admin's help is concerned, there are two particular optimisations regarding the AsynchOperations table (and its two related tables) that need to be done hand-in-hand with the Microsoft-created database optimisation script below, they are: http://support.microsoft.com/kb/957871/EN-US/ and http://support.microsoft.com/kb/968755/

Before you run the script mentioned below in this post, co-ordinate with your System Admin to make sure they stop the Microsoft CRM Asynchronous Processing Service (run during maintenance windows) and take a database backup just before purging the typical hundreds of thousands of records, thus assuming this would be the first time performed, and in the future by regular maintenance job.

USE [master]
GO
ALTER DATABASE [ORGNAME_MSCRM] SET RECOVERY SIMPLE WITH NO_WAIT
GO
-- now run the cleanup of the AsyncOperationBase table  
-- reference http://support.microsoft.com/kb/968520
-- Stop the Microsoft CRM Asynchronous Processing Service while you run this script. 
use [ORGNAME_MSCRM]
go 
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 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
--- after testing this being run in pre-prod, it took 17 hours

-- 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)
-- final optimisation, although done automatically usually
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

-- after everything is finished
USE [master]
GO
ALTER DATABASE [ORGNAME_MSCRM] SET RECOVERY Full WITH NO_WAIT
GO

 


For more information regarding a DBA's role in CRM optimisation:
Microsoft Dynamics CRM 4.0 Performance Toolkit from the Team Blog:  http://blogs.msdn.com/crm/archive/2008/02/29/microsoft-dynamicstm-crm-4-0-performance-toolkit.aspx

The performance toolkit itself - http://www.codeplex.com/crmperftoolkit