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
