Today we are going to see how to analyze and improve the query performance of your Dynamics 365 using the new Data Performance feature which is available for Dynamics 365(8.2) and CRM 2016 (8.0, 8.1) versions.
Go to Settings > Administration and click on the Data Performance icon.
This will open the “All Data Performance Logs” view. What this view shows is the queries that have been called in your environment that could be improved or queries that have taken more than three seconds to resolve (also called long-running queries).
Therefore, if your view is empty that means that your queries are already running smoothly.
If you see something in your view, you can optimize it using the “Optimize” button. This can reduce the time it will take to resolve a query. What happens behind the scenes is that the Microsoft SQL Server Indexes are added. But first, let’s see what the view looks like:
- The Period drop down menu allows you to select what period want to analyze.
- The Entity column is the entity that triggers the query.
- Count shows how many times the query was run.
- Optimization Status indicates if an optimization is available (Optimization available.)
It is critical to understand the values returned in the Optimization Impact column because this will allow you to manage the Data Performance feature to improve query performance in our environment. Initially after a query is optimized using the OPTIMIZE command bar button the column will show no value, but over time after the optimized query is run, this column will show negative or positive integer values. Negative values indicate an improvement in query performance and positive values reveal that the optimization is actually degrading performance. If that is the case, the optimization can be removed using the REMOVE OPTIMIZATION command bar button.
There are some implications that should be considered when using optimization. Some entities will not allow more than twenty optimizations depending of the number of related tables. Only entities used frequently should be optimized because optimizations will take storage space.
If you want to see if an optimization was run, visit Settings > Systems Jobs where you will be able to see the process trigger by the OPTIMIZE button and job status. Take into account that optimization might not be being applied even if its system job appears as completed, the reason is that the indexing process will take place in the back-end storage engine and that is not reflected by the System Jobs view. Depending on entity table size optimizations, this can take more than two hours to have full effect.
And there it is, you are ready to check and improve query performance of your Dynamics 365 environment with very few clicks. Go and tell your friends!
Receive more Dynamics 365 blogs right in your inbox – subscribe to our blog today!
Happy Dynamics 365’ing!