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