Check out the latest features available in Dynamics 365 for Customer Engagement, including LinkedIn Connect, Voice of the Customer and Universal Resource Scheduling.
Dynamics 365 2019 release wave 2 plan Discover the latest updates to Dynamics 365.Release Plan | Weekly Deployment Notes
Ace your Dynamics 365 deployment with packaged services delivered by expert consultants.Explore service offerings
Connect with the ISV success team on the latest roadmap, developer tool for AppSource certification, and ISV community engagements.
ISV self-service portal
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance and Operations TechTalks | Customer Engagement TechTalks | Talent TechTalks
Hey everyone, as many of you may know we made some pretty big changes to how CRM retrieves data from SQL server, starting in UR10. Part of this was adding various measures and statistics to change the queries based on the dataset and the users access to records, we also added the ability to “hint” to CRM how it should structure a query based on a property called isQuickFindFields. The property was created specifically for quickfinds but if you’re like our CRM PFE’s you’ll want as much control as possible for how queries interact with SQL server. If you’ve been looking for a way, especially in extensibility scenarios, to better control performance via index tuning and smart querying then you’re going to love this!
Now for the comparison: I’ll show you the differences between a quickfind without this optimization (default pre-UR10) and a quickfind with this optimization (post UR10). You’ll find a super zoomed out clip of the query plans below, and while they’re impossible to read they still illustrate how the query plan is split up differently based on these settings. The union based query splits up such that it could be filtered in multiple spots and potentially leverage parallelism (big opportunity to put index tuning to work here). Compare that to the standard (preUR10) query which joins everything together and many times cannot filter the dataset until the end of the plan, this isn’t always the case but no matter what the SQL optimizer will do whatever it can to try and give us a decent query plan given the circumstances. Post UR10 CRM uses a Common Table Expression along with a Union effectively telling SQL that it can go process all the individual datasets separately (including filtering them) then come back and union the results together – meaning it will filter earlier in the process if it can.
Please note: this new query is *not* necessarily always more efficient, thus CRM will change the approach for various users and sizes of datasets, but in many cases this query offers us more flexibility when dealing with large datasets and leveraging custom indexes; essentially it’s another tool in our toolkit of options we can use when we’re tuning and optimizing. Another note, this is enabled by default for quickfind queries today and no other views by default. Want to know more? Want to get down to the fine details of what things can be tweaked? Read more here in the Optimization White Paper. I want to emphasize, query plans will change depending on your SQL server and all the statistics SQL uses to make informed decisions – these are common cases below.
Default out of box query prior to UR10:
Fig1: Common pre-UR10 query plan:
* notice how the data (thickness of the gray bars) continues to grow towards the upper left, it then all joined together using a hash join to put it all into one large dataset, then it is sorted and filtered. This illustrates how all the data is combined together then sorted before it can be filtered which can be less efficient, especially with some datasets (in others this is much less of a problem).
With the isQuickFindFields property enable (post UR10) here is the difference in the TSQL query and the plan – also note how there is now an inline count:
Fig2: Common post UR10 query plan:
At face value this looks much more complex, however if you dig in you’ll find the plan to be more efficient. In this case each portion of the plan can be processed and filtered independently from the other, which can have dramatic reductions in the amount of data that needs to be processed resulting in much less expensive queries.
Here are some questions I have received around this change:
Q: This is AWESOME – how do I get this? A: Great news – everyone can get it or already has it , install the latest rollup and make sure EnableRetrieveMultipleOptimization is set to default. If you’re already on UR10 or higher and have the feature set to zero (or not set at all) you will then have this feature enabled!
Q: What’s an “enable retrieve” … – whatever you said? A: It’s highlighted in the Optimizing CRM white paper and can be used to control CRM’s query behaviors. It can be set in the registry for onPrem customers or via your orgDbOrgSettings if you’re onPrem or Online – I personally prefer the orgDbOrgSettings because they aren’t server-specific and don’t require any registry editing. Also, be aware there are a few other settings documented in that white paper that can give you even more control over the query behavior – but most of the time the defaults are fine.
Q: What are OrgDbOrg settings? A: OrgDbOrgSettings are available on a per-organization basis and offer you greater flexibility and control of your CRM organization – no matter if you have Online, onPremise, or partner hosted – you all have orgDbOrgSettings for every CRM Organization. If you are unfamiliar with these, there is a command line tool you can use to change them – however I recommend using an easier to use, web based, editor posted here: http://orgdborgsettings.codeplex.com/. *hint* there may be a, new, much easier way to edit these settings in the near future. If I were you, I would keep an eye on your favorite CRM blog for more information in the near future! If you want to be kept up to date subscribe to the blog and it will be sure to email you when we post something.
Q: I’m a developer or admin and need to extend CRM, I want to use this to be used in my custom queries, where can I use the new isQuickFindFields property? A:More great news! Custom fetch reports can use this fetchXml isQuickFindFields property setting, it can also be used for any fetch statements you’re executing from code. Also, if you have a QueryExpression (in managed code, plugins, etc) you can set the “isQuickFindFields” property on the FilterExpression – be sure to use the latest SDK libraries for managed code scenarios as these will expose the new property. This query change is not used when querying via the REST endpoint.
Business Applications communities