The Microsoft Dynamics CRM Team Blog
News and views from the Microsoft Dynamics CRM Team
Upgrades of CRM databases from V4 are much faster in CRM 5.0 UR6 release. We've made improvements to the most time consuming parts of CRM upgrades that should significantly reduce the upgrade time for large CRM databases.
I wanted to share some options to make upgrades even faster. These options are more relevant for higher end hardware common to large production deployments of CRM. Since these options are hardware and deployment specific, they are implemented as registry key options(DWORDs) under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM.
1. MaxDopForIndexCreation - CRM recommends that the max degree of parallelism (maxdop) SQL option be set to 1, to avoid the potential pitfalls of allowing SQL to generate parallel query plans. But those concerns are not valid for upgrades, which usually happen in single user mode. Index creation is a lot faster when SQL is allowed to parallelize index creation. The MaxDopForIndexCreation registry key hook allows you to specify the maxdop value used for index creation. We got pretty good results using a value of 4 for this setting in internal testing.
2. SortInTempDB -- For indexing large tables (tens of millions of rows), SQL has to store intermediate sorted rows on disk. The SORT_IN_TEMPDB option makes the index creation go along much faster, assuming you have dedicated hardware for tempdb. For the upgrade, you may specify SortInTempDB registry key (set it to 1) to allow SQL to use tempdb for sorting index data on large tables.
3. NumThreadsForIndexCreation -- Production SQL machines for large CRM deployments tend to be very powerful, typically with 16 cores or higher. In addition to specifying the MaxDopForIndexCreation option, you can specify that the index creation happen in multiple threads. We got pretty good results, again, using 4 for the number of threads in internal testing.
The above registry key options greatly speed up the "Upgrade indexes" step of a CRM deployment (you can look at the upgrade logs to see where time was being spent if you weren't paying attention during the upgrade process). Index upgrade can take several hours for a large CRM database, and we've seen reductions of over 75% through the combined application of these options.
4. BatchAddAttributeUpdates -- This is an internal optimization that allows us to reduce the number of SQL queries we issue while adding non-nullable columns to existing tables. It will help if the metadata changes (DiffBuilder) stage of the upgrade is taking a long time. You may set this to a value of 1 for it to take effect.
These are all upgrade specific improvements, and aren't useful when the system is in normal use. We released these changes in UR6 because they were significant pain points for our larger customers, and they have not undergone comprehensive testing that happens with a full CRM release. We recommend that you leave them on only for the duration of the upgrade, and only if your upgrades are taking a long time. You may remove them once the upgrade is complete, to avoid any unintended consequences. Other settings you may experiment with as part of your upgrade planning include turning off high availability modes for your CRM database, and switching to simple recovery mode, again, only for the duration of the upgrade.
Using the upgrade performance improvements that are part of UR6, and some of the registry hooks described in this post, we were able to speed up upgrade of CRM from V4 by over 60% for a large internal deployment of CRM (over a terabyte in size). We hope UR6 with these options help with your upgrade planning to CRM 5.0, and significantly reduce your downtime windows for the upgrade.
CRM Upgrade Team
Adding a short comment to help make this page easier to find in noting that CRM 5.0 UR6 above is equivalent to CRM 2011 UR6 or CRM 2011 Update Rollup 6.
Note that you may have problems when enabling auditing after this if you use the NumThreadsForIndexCreation registry key. See support.microsoft.com/.../2714807, "Organization database updates fail during the import process of the organization in Microsoft Dynamics CRM 2011" for reference. Note that this will also apply if this registry key is in place and you perform any Microsoft Dynamics CRM 2011 Update Rollup installations that contain database updates.
This may be a bit circumstantial evidence, but I've had another support case where a customer was seeing 100% CPU Usage after they had upgraded to Microsoft CRM Server 2011 and after removing these registry keys and resetting their HKLM\Software\Microsoft\MSCRM\OLEDBTimeout key back to a more reasonable level of 600 decimal, the CPU Usage level on the Microsoft CRM Servers went back to a more normal level averaging less than 50% utilization.