Dynamics 365 Analyze and Improve Data Query Performance

A new feature that was just released this week can provide insight into query performance and provide an automated method of optimizing the query.

It is available for the online version of CRM 2016 (8.0, 8.1) and Dynamics 365 (8.2).

To get to the tool go to Systems, Administration, Data Performance.

From there you will be taken to the grid view.

Once there you will be able to see one or more long running entity queries. Unfortunately, or fortunately, while looking through our organizations and some of our clients’ I couldn’t find any examples of long running scripts that I could use as examples. I will see if we can create an offending query and illustrate it in the future blog article.

But lets go ahead anyway to review what you would see and what the column headings mean.

• Count. Indicates the number of times a query has been executed, which indicates the popularity of the query.
• Optimization Status. Indicates whether an optimization has been created for the entity. Optimization Available indicates that the entity does not have an optimization applied or that a new optimization is available for an entity that already has at least one optimization.
• Optimization Impact. All potential optimizations begin with an optimization impact value of 0. Potential optimizations have no impact measurement yet because the optimization hasn’t been applied. Over time, after an optimization is applied by clicking OPTIMIZE, an integer value is displayed that represents query performance impact. For example, after one week since the optimization was applied, the value that appears can help indicate whether an optimization has improved or degraded query performance for the given entity. A negative value suggests an improvement in query performance, whereas a positive number suggests a degradation in query performance. So, an optimization applied one week ago that has a value of -10% suggests a 10 percent improvement in query performance.

If you had a query that was misbehaving, just select in from the grid view and click on Optimize.

Optimizations take effect immediately after they are applied. You can check on the status of an optimization in the System Jobs grid view. Most execute immediately but if it is a large table it might take several hours.

See TechNet for the complete details and current info Analyze and improve data query performance.