Improved Upgrade Performance in UR6

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

Atul Shenoy

Comment List
Anonymous
Related
Recommended