Dynamics CRM 2011 Quick Find Performance & Records Per Page Setting

Article
07/08/2014

Imagine this scenario in Dynamics CRM 2011. When a CRM user does search for *cond, for example, in the quick find box on Accounts filtered view, like below screenshot, the IE window hangs and after approx. 2mins 30secs the IE window comes back from un-responding and displays the result from the search.

When looking in the hang dump file in Debug Diag for example, we could see the below in one of the threads:

+0x008 bstrVal
0x0bc3deec "<grid><sortColumns>name:1 </sortColumns><pageNum>1</pageNum><recsPerPage>50</recsPerPage><dataProvider>Microsoft.Crm.Application.
Platform.Grid.GridDataProviderQueryBuilder</dataProvider><uiProvider>Microsoft.Crm.Application.Controls.GridUIProvider</uiProvider>
< cols/><max>1</max><refreshAsync>False</refreshAsync><pagingCookie/><enableMultiSort>true</enableMultiSort><enablePagingWhenOnePage>
true</enablePagingWhenOnePage><parameters><autorefresh>1</autorefresh><isGridFilteringEnabled>1</isGridFilteringEnabled><viewid>
{2D1187C4-23FE-4BB5-9647-43BB1C6DDBD1}</viewid><viewtype>1039</viewtype><RecordsPerPage>50</RecordsPerPage><viewTitle>Aktive Firmen</viewTitle><otc>1</otc><otn>account</otn><entitydisplayname>Firma</entitydisplayname><titleformat>{0} {1}</titleformat><entitypluraldisplayname>Firmen</entitypluraldisplayname><isWorkflowSupported>true
< /isWorkflowSupported><fetchXmlForFilters><fetch

The setting in Personal options – General - Records per page was set to 50. When this was changed to 250 records per page (as per below), and performed a quick search again on Accounts filtered view for *cond the results were returned in 2 seconds, instead of the 2mins 30 secs.

When comparing the SQL profiler traces, when set for 50 records per page it queries:

exec sp_executesql N'select top 51 "account0".Name as "name"

When set for 250 records per page it queries:

exec sp_executesql N'select top 251 "account0".Name as "name"

This indeed shows the increased query performance when set at 250 records per page and not as many queries needed to get the results back.

When looking at the execution plan of both, you may see that the quick search when set to 50 records per page has a lot of nested loops and seeks, whereas when set to 250, there are no nested loops and scans instead of seeks.

You may also see that on the execution plan in SQL for the search when set at 50 records per page, it suggests to add a missing index such as below example:

USE [db_MSCRM]

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]

ON [dbo].[AccountBase] ([StateCode])

INCLUDE ([AccountId],[TerritoryId],[Name],[AccountNumber],[WebSiteURL],[Telephone1],[TransactionCurrencyId])

Note:

In general practice, using an astrix (*) at the start of a search is not performance efficient.

When performing a quick find with a wildcard, it does not take advantage of any indexes, so you will never achieve optimal performance with this type of search. It is recommended to avoid wildcard searches when at all possible during a quick find search.

Dynamics CRM 2011 Quick Find Performance & Records Per Page Setting

Additional resources