Personalized Community is here!
Quickly customize your community to find the content you seek.
Have questions on moving to the cloud? Visit the Dynamics 365 Migration Community today! Microsoft’s extensive network of Dynamics AX and Dynamics CRM experts can help.
2021 Release Wave 1Discover the latest updates and new features to Dynamics 365 planned April 2021 through September 2021.
Release overview guides and videos Release Plan | Preview 2021 Release Wave 1 Timeline
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance and Operations TechTalks | Customer Engagement TechTalks | Upcoming TechTalks | All TechTalks
One of the primary settings that can be utilized to help with performance is EnableRetrieveMultipleOptimization or ERMO. The setting itself was originally present in CRM 4.0 with two different options, this expanded to a third option in CRM 2011 RTM and with the release of CRM 2011 Update Rollup 10 we see a fourth option made available.
The basic purpose of this setting is that it modifies how we retrieve data in CRM, modifying the queries that are being used. With CRM queries, one of the biggest challenges is to account for how security works. With that in mind we have a variety of settings as there are multiple scenarios for customers. As with everything in CRM regarding performance; this setting is not an exact science. Testing is always suggested to get a feel for exactly how the different values will impact a specific user base, business unit structure and configuration of security roles. Also, the best value may change and may be different by organization.
Below is an excerpt from the Performance Whitepaper that discusses the settings as they currently exist in CRM 2011 with Update rollup 10 applied:
0 = Default
Description: Defaults to a Common Table Expressions (CTE) format for security filtering.
When to consider: Use in most cases – especially useful with larger data sets in which the number of visible records varies a lot between different users. Additional detail will be provided on this value as well as some tips and tricks in a later article.
1 = Use Temp Table
Description: The IDs of all records a user has Read access to are initially selected into a temporary table in SQL Server, and then the overall results of the user query are filtered against the IDs in the temporary table.
When to consider: Because SQL Server selects the IDs of all records a user has Read access to, consider using this value only when users typically have Read access to a small set of records. Useful primarily in smaller data sets where a specific entity, or record type, has only a few thousand records at most.
2 = Use Security IDs
Description: Defaults to “OR”-based filtering for security, but the IDs of teams the user is a member of or business units the user has visibility into are passed in instead of being calculated through table joins in the query.
When to consider: Consider using when most users have Read access to most records without the use of sharing, and most users are members of a small number of teams and have access to records in a small number of business units.
3 = OR use Joins
Description: Defaults to “OR”-based filtering for security, and team membership and business unit visibility are always calculated through table joins. (Microsoft Dynamics CRM 4.0 used this type of security query structure.)
When to consider: For smaller organizations, this format of security filtering is often sufficient and may not require as much memory (RAM) on the SQL Server as the default ERMO setting.
For the values that were available for previous versions please review the articles below:
CRM 2011 RTM
There are currently two ways to enable the setting. One is for On Premise deployments only and will modify the setting for the entire deployment and the second will work for CRM Online or CRM On Premise and is set on an organizational basis. This can give us an advantage if for example there are multiple organizations of various sizes. At this point that is the preferred method to use so we’ll cover that first.
Setting ERMO via Orgdborgsettings tool:
This article discusses where to obtain the tool, how to configure the tool for a specific deployment type and then the commands. A couple of notes on this:
Be careful that you are selecting the correct deployment type for your config file, be it On Premise, IFD, CRM Online or CRM Online with Office 365.
ToUpdate: Microsoft.Crm.SE.OrgDBOrgSettingsTool.exe Update [/u] To Retrieve: Microsoft.Crm.SE.OrgDbOrgSettingsTool.exe Retrieve [/u] Note: /u Specifies that is the unique name. Without this option is assumed to be the friendlyname
Setting via the registry:
Troubleshooting Performance in CRM
Data Tier Part 1 - Capturing the Data
Data Tier Part 2 – The Tools
Data Tier Part 3 – Analysis
Benchmarking (Coming Soon…)
Thank you for your Geat Post;
I would like to know if we could apply this optimization for CRM 2013 SP1 UR3 ?
And if we need to it on CRM Server or CRM DB Server ?
Thanks in advance
Business Applications communities