By Aakash Patel, Database Engineer, Rocket Mortgage

Abstract

Microsoft introduced one of the most desired features in SQL Server 2016, called Query Store. It provides a plethora of information on query execution statistics and execution plans, which are useful for troubleshooting, but not if you want to be more proactive. In this blog post, I’ll share a t-sql script I’ve written, based on Query Store data, which can be used as an alert to notify you when the latest or current execution plan is poorer than a previous execution plan.

This is the second part in a two-part series about execution plans. If you haven’t already, read Part 1 to learn more about how to read and work with execution plans.

What Is The Query Store?

The Query Store houses information on execution plans and query execution statistics. The best part of the Query Store is that data is available even after a server restart, unlike Dynamic Management Views (DMVs). Further, it’s available in all editions of SQL Server. (Yes, you read that right.) To learn more about execution plans, you can refer to my previous article on the topic.

A Script To Identify A Better Execution Plan

Query Store comes with a few built-in reports, which are handy for performance troubleshooting, but aren’t sufficient if you want to be more proactive. I’ve written a script that scans the Query Store data and lists out all QueryIDs for which a better (than current) execution plan exists. This script will help you to more quickly recognize when the execution plan is changed to a plan that is less efficient than the previous one.

In SQL Server 2017, Microsoft did introduce a feature called “Automatic tuning,” which offers automatic plan correction. However, enabling that feature gives complete control to SQL Server engine, so you may not be comfortable enabling it. The script I’ve developed gives you control by providing the ability to define the thresholds and take charge of plan correction.

While identifying a better execution plan, the script only focuses on CPU savings, which is the costliest resource for SQL Server. There are a few other variables the script considers before labeling the execution plan as better or worse. In the current form of the script, most of the variables have been hard-coded for simplicity. However, you can modify the script to parameterize them for flexibility. You can also add other criteria, such as logical reads.

I’ve added comments throughout the script to keep it self-explanatory. First, you need to feed values to four parameters in the first section of the script. The script will check if the server CPU is over the defined threshold before doing any additional work. In case of higher server CPU than defined threshold, script finds the databases hosting high CPU processes. It then loops through their Query Stores to check if any of its queries had a better execution plan than the current one based on defined thresholds.

Script Execution And Next Steps

Make sure your server has bandwidth to run this kind of additional monitoring queries.

Here is the screenshot of sample output of the query:

Figure 1 – Sample Output Of Alerting Query

If the script finds any query where the previous execution plan is better than the current one, you can inspect it using the “Tracked Queries” report under the Query Store folder, as shown in Figure 2.

Figure 2 – Tracked Queries Report

Feed the QueryID in the report and track down its historical executions, as shown in Figure 3. By default, the report shows historical executions only for the last day, and it has duration on the y-axis. You’ll want to reconfigure the report to show historical executions for a week, and to have CPU time on the y-axis . In Figure 3, each circle with a different color represents a unique execution plan for the query. So, the number of circles (with different colors) is equal to different execution plans. You can compare these execution plans using the “Compare Plans” button to try to understand why and when SQL Server is choosing one plan or the other. (This is a large topic and requires a deep understanding of Query Optimizer to dig into — a topic for another day.)

Figure 3 – Query Execution History

Please fine-tune the threshold values of the script according to your environment and use it to alert your Database Administrators group.

Conclusion

Query Store is a great monitoring and troubleshooting tool within SQL Server, providing a wealth of information on query execution statistics. However, to stay ahead of the curve, we can build additional in-house tools that can make this information even more actionable and impactful. In this article, we looked at one such script to identify CPU saving opportunities by comparing execution plans.