Tribridge Connections

A Technology, Cloud Solutions & Industry Expertise Blog


Microsoft CRM Update Rollup 10 Now Available

CRM Update Rollup 10 Provides Drastic Search & SQL Performance Improvement

Microsoft has just released CRM Update Rollup 10. One of the main areas we have been testing with is within quick searches using the Outlook Client. To further explain, Microsoft Dynamics CRM 2011 Outlook Client uses a completely different SQL query than its Web client counterpart prior to Update Rollup 7. This is a reason to immediately move to at least Update Rollup 7. Please note that when downloading CRM 2011 Server, Update Rollup 6 is now built-in to base code revision.

In our CRM Update Rollup 10 test scenario below, just updating from Update Rollup 5 to Update Rollup 8 provided immediate performance improvements right off the bat, and the query executing provided similar performance results in either the Outlook Client or the Web Client.

Example 1 CRM Update Rollup 10 Results

1.5 Million Rows, Quick Search Contact Name, Rollup 5, Outlook Client 37.6 seconds, Web Client 11.0 Sec
1.5 Million Rows, Quick Search Contact Name, Rollup 8, Outlook Client 8.6 seconds, Web Client 8.7 Sec

This is a drastic improvement just applying the Update Rollup 10. Note: This customer also has another issue that I will address in this post, so please continue on.

Unfortunately, due to the nature of how the scripts are dynamically created, there are still limited performance improvements with Update Rollup 8. CRM Update Rollup 10 is really where the magic takes place. Please note that we have tested ONLY an Outlook Client using Update Rollup 10 (this changes the query being sent by the client) and have noted significant improvements.

We do not recommend this approach for a production system as the server should ALWAYS be on a higher or equal Update Rollup than the client machines, and there could be harmful client issues.

CRM 2011 Update Rollup 10 completely changes how the dynamic SQL scripts are created and executed. With the old query structure, all of the columns were executed together, hence SQL Server was unable to generate a good query execution plan because of all the join statements required.

Now, with CRM Update Rollup 10, the dynamic SQL statements use separate SQL statements combined using SQL UNIONS. This change now allows for drastic performance improvements, taking advantage of SQL Server Native Execution Plans, giving us the ability to now further tune and build indexes for these additional unions avoiding complete SQL table scans. Old SQL Query

where ((((“contact0″.StateCode = @StateCode0)) and ((
“contact0″.ParentCustomerIdName like @ParentCustomerIdName0 or
“contact0″.MiddleName like @MiddleName0 or
“contact0″.LastName like @LastName0 or
“contact0″.FullName like @FullName0 or
“contact0″.FirstName like @FirstName0 or
“contact0″.EMailAddress1 like @EMailAddress10 or
“contact0″.new_ContactIdentifier like @new_ContactIdentifier0 or
“contact0″.Address2_Line1 like @Address2_Line10 or
“contact0″.Address1_City like @Address1_City0
New SQL Query
where ((((“contact0″.StateCode = @StateCode0)) and ([contact0].[ContactId] in (
SELECT [ContactId] from [ContactBase] as “contact0″ where
(“contact0″.ParentCustomerIdName like @ParentCustomerIdName0) OR
(“contact0″.MiddleName like @MiddleName0) OR
(“contact0″.LastName like @LastName0) OR
(“contact0″.FullName like @FullName0) OR
(“contact0″.FirstName like @FirstName0) OR
(“contact0″.EMailAddress1 like @EMailAddress10)
UNION SELECT [ContactId] from [ContactExtensionBase] as “contact0″ where
(“contact0″.new_ContactIdentifier like @new_ContactIdentifier0)
UNION SELECT [ParentId] from [CustomerAddressBase] as “contact0″ where
(“contact0″.Line1 like @Address2_Line10 and “contact0″.AddressNumber = 2 and “contact0″.ObjectTypeCode = 2) OR
(“contact0″.City like @Address1_City0 and “contact0″.AddressNumber = 1 and “contact0″.ObjectTypeCode = 2)))))

Example 2 CRM Update Rollup 10 Results

1.5 Million Rows, Quick Search Contact Name, Rollup 8, Outlook Client 8.6 seconds, Web Client 8.7 Sec
1.5 Million Rows, Quick Search Contact Name, Rollup 10, Outlook Client 2.6 seconds, Web Client 2.6 Sec

Again, another drastic search improvement across the board. Finally, still believing we can get even more performance out of the system, we researched more into the specific fields in the customers quick find view. The quick find view was using a search (find) field that was in the Contact Extension Base. Upon removing this field from the quick find view, the results where even more impressive:

Example 3 CRM Update Rollup 10 Results

1.5 Million Rows, Quick Search Contact Name, Rollup 10, Outlook Client 2.6 seconds, Web Client 2.6 Sec
1.5 Million Rows, Quick Search Contact Name, Rollup 10, Outlook Client 1.6 secs, Web Client 1.7 Secs

The response is so fast I can barely hit my stop watch and enter key at the same time!

In closing, further tuning of this field, now that we can identify the union, should allow us to re-add the field and continue SQL performance tuning using the Database Tuning Advisor. I would recommend reviewing any fields in the extension base and consider their value before adding them to the quick find, and at least trying to performance tune those results prior to release.

This is a quick way to lose system performance by a user adding fields to the quick find (search) that are outside the main entity.

I’d like to give special thanks to Eric Hagen from Microsoft CRM 2011 support, and my Tribridge team member Mike Hauck and his wife for working many late evenings with me, researching and testing CRM Update Rollup 10 for Microsoft Dynamics 2011 while putting his new born daughter to sleep on conference calls! Now that’s dedication!

Register for our October 11th CRM power-user series webinar on process management and workflows now.

Share Your Thoughts With Us

Load more comments
Thank you for the comment! Your comment must be approved first