I had the recent pleasure of helping a client work through some very impactful performance issues with their on-premise CRM installation. These issues, which included SQL timeouts, excessive bandwidth consumption, outlook crashes and general slow performance, were causing poor user acceptance, lowered productivity, and negative impact on other business critical applications. We updated many database settings, tuned indexes, updated CRM registry settings and doubled the SQL Server RAM, but in the end the following 5 items provided the biggest performance boost to their Dynamics CRM instance.
1.) Reorganizing / Rebuilding SQL Indexes
When you install Microsoft CRM, several System jobs are set up to keep your database running smoothly. These routines include rebuilding fragmented indexes and cleaning up tables that contain data that is no longer needed. These jobs work great when your database is of moderate size, however if your database grows over 40 GB, these jobs may begin to fail. When this happens your database will begin to grow rapidly, and your indexes will quickly become fragmented. (My client had over 100 indexes fragmented at more than 80%, and a few tables that contained over 20 million unnecessary records.)These issues can lead toextremely slow performance and eventual SQL timeouts.
When you install Microsoft CRM, several System jobs are set up to keep your database running smoothly. These routines include rebuilding fragmented indexes and cleaning up tables that contain data that is no longer needed. These jobs work great when your database is of moderate size, however if your database grows over 40 GB, these jobs may begin to fail. When this happens your database will begin to grow rapidly, and your indexes will quickly become fragmented. (My client had over 100 indexes fragmented at more than 80%, and a few tables that contained over 20 million unnecessary records.)These issues can lead toextremely slow performance and eventual SQL timeouts.
Are You Experiencing These Symptoms?
If so, you should turn off the out-of-the box index maintenance jobs and develop your own maintenance plan. You can get the CRM Job Editortool from CodePlex to change the time that the maintenance plans run. (If you go this route, you’ll want to set the “Reindex All” and “Indexing Management” jobs to run sometime in the distant future, such as 12/31/2099.)
If so, you should turn off the out-of-the box index maintenance jobs and develop your own maintenance plan. You can get the CRM Job Editortool from CodePlex to change the time that the maintenance plans run. (If you go this route, you’ll want to set the “Reindex All” and “Indexing Management” jobs to run sometime in the distant future, such as 12/31/2099.)
Once you have disabled these jobs, you will need to set up your ownindex maintenance jobs. I recommend using the SQL Server Maintenance Plan Wizard ( http://msdn.microsoft.com/en-us/library/ms191002.aspx ). Alternatively, you can create a SQL job to run the CRM 2011 index maintenance procedure p_ReindexAll, leaving the MaxRunTime variable null to ensure the job will complete. The procedure variables are as follows:
| Order | Variable name | Default | Description |
| 1 | AllIndexTypes | 0 | 0:Clustered Index only, 1: Clustered and Non Clustered indexes |
| 2 | MaxRunTime | Null | Maximum allowed running time (in seconds) |
| 3 | FragRebuildPct | 30 | Percentage of fragmentation at which indexes are rebuilt |
| 4 | MinPages | 25 | do not touch tables less than xx pages |
| 5 | Verbose | 0 | 1: Print progress messages and detailed results |
Sample Procedure Call
EXEC p_ReindexAll 1,null,1,1,0
EXEC p_ReindexAll 1,null,1,1,0
2.) Deletion of Completed Workflows
Similar to the CRM re-indexing job, the CRM system jobs designed to keep CRM tables cleaned up may start failing if your database becomes too large. When this happens the AsyncOperationsBase and PricipalObjectAccess tables can grow extremely large (tens of millions of records) within a few months. There are a few methods you can employ to keep these tables in check.
Similar to the CRM re-indexing job, the CRM system jobs designed to keep CRM tables cleaned up may start failing if your database becomes too large. When this happens the AsyncOperationsBase and PricipalObjectAccess tables can grow extremely large (tens of millions of records) within a few months. There are a few methods you can employ to keep these tables in check.
If you want to delete all completed workflows immediately after they are completed, simply check the “Automatically delete completed workflow jobs” checkbox on each process.
However if you would like to remove completed workflows after some period of time (such as two weeks), you will need to create a SQL job to delete them in bulk. The Microsoft KB article 968520 contains the SQL required to do this.
3.) Delete Orphaned POA Records
If you were using CRM 2011 prior to Rollup 6 (or if your CRM database is over 40 GB), there is a good chance your database has a large number of orphaned records in the PrincipalObjectAccess table. This table can grow to be tens of millions of records and can have large performance impacts, especially if you are sharing records among teams. My client had over 15 million orphaned records in the table that we were able to remove. However, after deleting these records, they returned in just 3 months, so we’ve now set up a recurring job to delete these orphaned records each night.
If you were using CRM 2011 prior to Rollup 6 (or if your CRM database is over 40 GB), there is a good chance your database has a large number of orphaned records in the PrincipalObjectAccess table. This table can grow to be tens of millions of records and can have large performance impacts, especially if you are sharing records among teams. My client had over 15 million orphaned records in the table that we were able to remove. However, after deleting these records, they returned in just 3 months, so we’ve now set up a recurring job to delete these orphaned records each night.
The Microsoft KB Article 2664150 explains the problem and provides a script to safely clean this table. I recommend setting this up as a nightly or weekly job to keep this table clean. Be aware that this script will cause your transaction log to grow, and you’ll need ample disk space for the script to complete. To avoid this, I move the “BEGIN TRAN” statement to just above the batch delete statements.
BEGIN TRY
BEGIN TRAN t1
— delete PrincipalObjectAccess records in batches
exec(@deletestatement)
4.) Enabling Compression & SSL
The Outlook Client can provide a seamless and user friendly interface to your customer interactions. Tracking emails and automatically syncing contacts, appointments and tasks can be a great productivity gain for your users. While powerful, this tool is known to be a chatty, continuously sending a lot of data across your network. If not tuned properly, it can consume an excessive amount of your networks bandwidth, crippling other business critical applications and your team’s ability to function!
The Outlook Client can provide a seamless and user friendly interface to your customer interactions. Tracking emails and automatically syncing contacts, appointments and tasks can be a great productivity gain for your users. While powerful, this tool is known to be a chatty, continuously sending a lot of data across your network. If not tuned properly, it can consume an excessive amount of your networks bandwidth, crippling other business critical applications and your team’s ability to function!
The chart below from the Optimizing and Maintaining a Microsoft Dynamics CRM 2011 Server Infrastructure whitepaper shows that enabling Compression and SSL can reduce the Outlook Client network traffic by 88 percent! We were a little skeptical that this would be the case, however after deploying, our heaviest individual users Outlook Client bandwidth consumption dropped from GBs to MBs per day.
| Compression Enabledon HTTP | Compression Enabled on HTTPS | ||
| Bytes Sent | 105084 | 105084 (0% reduction) | 67586 (36% reduction) |
| Bytes Received | 219102 | 149424 (32% reduction) | 25837 (88% reduction) |
To manually configure IIS dynamic compression:
a.) In Internet Information Services Manager, click the <Server name>, scroll down in the Features View to the Management section, and then start the Configuration Editor.
b.) In the Configuration Editor, in the Section: address box, type system.webServer/httpCompression, click dynamicTypes, and then in the value cell, click the ellipses . . . (three dots).
c.) In the Collection Editor, click Add and verify that Enabled is set to True and the inclusion of an entry for mimeType of application/soap+xml; charset=utf-8. Note that the appearance here is different than it was when using the appcmd command, which has URL encoded the plus symbol.
d.) Click one of the Items: collection cells, and then close the Collection Editor.
e.) In the Configuration Editor, under Actions, click Apply to add configure the new setting httpCompression mimeType.
f.) Run an IISReset to ensure that the compression setting is enabled for the new mimeType.
5.) Define system wide Outlook Sync Filters
By default, the Outlook Client filters are set to synchronize all contacts and activities owned by the user. This configuration works well for many clients, but in many cases it will make sense to narrow the scope of the synchronization further. The most common need for this is to pre-program the Contacts synchronization filter so that it will not automatically pull all CRM Contacts into a new user’s Outlook Contacts folder. Modifying this default filter will allows the users to selectively set up synchronization so that they don’t accidentally download hundreds of new records to their Outlook Contacts.
By default, the Outlook Client filters are set to synchronize all contacts and activities owned by the user. This configuration works well for many clients, but in many cases it will make sense to narrow the scope of the synchronization further. The most common need for this is to pre-program the Contacts synchronization filter so that it will not automatically pull all CRM Contacts into a new user’s Outlook Contacts folder. Modifying this default filter will allows the users to selectively set up synchronization so that they don’t accidentally download hundreds of new records to their Outlook Contacts.
Unfortunately, there is no “supported” way reset these filters after the Outlook Client has been deployed. However, the default and user filters are stored in SQL tables and can be manipulated. (For more information on how to do this please see our recent blog post on how to Pre-Program or Reset Outlook Syncronization Filters.)
Summary
Keeping your CRM system tuned for optimal performance is a critical factor in user productivity and adoption of your CRM system. While there were many additional steps we took to improve performance, the 5 steps listed in this post made the biggest impact. I hope that the lessons we learned can help you avoid similar pitfalls and allow you to provide a better experience for your users.
Keeping your CRM system tuned for optimal performance is a critical factor in user productivity and adoption of your CRM system. While there were many additional steps we took to improve performance, the 5 steps listed in this post made the biggest impact. I hope that the lessons we learned can help you avoid similar pitfalls and allow you to provide a better experience for your users.
Interested in Learning More?
If you’re currently thinking about implementing Dynamics CRM 2011 or exploring other Customer Relationship Management solutions, reach out to us. We’d love to talk to you about your business needs and how the features and benefits of Dynamics CRM can work for you.
If you’re currently thinking about implementing Dynamics CRM 2011 or exploring other Customer Relationship Management solutions, reach out to us. We’d love to talk to you about your business needs and how the features and benefits of Dynamics CRM can work for you.
No comments:
Post a Comment