Time to read: 9 minutes By Arun Sirpal (Microsoft MVP), Editorial Contributor
The Query Store was introduced back in SQL Server 2016 and with the incoming release of SQL Server 2019, it remains a very important feature to know and understand to utilise in performance tuning exercises. Not only is this feature available with on-premises SQL Server but it is a key tool to use when troubleshooting performance issues in Azure SQL Database.
The Query Store is compared to that of an air flight black box recorder, more specifically when relating it to SQL Server it tracks what your queries are doing thus it is easy for you to see potential query plan changes that are impacting performance over varying time slice windows. Much information is available for performance tuning analysis which is made possible by two stores held within Query Store. The plan store which holds execution plan information and the runtime stats store which holds execution statistics information, both are in-memory objects. The goal of this tool is to allow you as the data professional to not only troubleshoot your workloads but to stabilise it if required.
With Query Store in your performance tuning tool-kit, you could follow a specific troubleshooting methodology as defined below by Microsoft, which will form the basis for the rest of the blog post.
Enabling Query Store
Query store is set up on a per database setting hence within the context of the master database for my database called Adventure DB I would execute the following code:
To confirm successful setup and to review the settings such as maximum storage space allowed and data flush interval rates you can run the following select statement.
Looking at the settings from SQL Server Management Studio (SSMS) you can see many configuration tweaks are possible.
There are two settings worth mentioning. The first configuration setting that I would suggest changing relates to increasing the size of the data space for Query Store, as you can see below I have changed the maximum size to 1GB.
The second setting to be aware of is called Query_Capture_Mode and there are subtle differences between on-premises SQL Server 2016 / 2017 and Azure SQL Database. The default value for SQL Server 2016 and SQL Server 2017 is ALL. For Azure SQL Database, the default value is AUTO. You will need to make your own decision as to what you think will suit your workload patterns because the side effect of setting this mode to ALL means everything is going to be collected, even the insignificant code. AUTO setting will capture the more “significant” parts of your workload and for this reason, I tend to opt for this setting.
Performance Tuning
For this example, I will fabricate an issue for demo purposes. I execute a stored procedure multiple times for different parameter values against a large table that is not evenly distributed, then I cause recompiles to force SQL Server to generate a different execution plan. The key objective here is what will we see via Query Store?
You will need to navigate to the Query Store node and you will see many views, for this example, we will select the top resource consuming queries.
Then the key information here is that there is Query ID 1 which has two plan IDs (1 and 2) as you can see plan 1 is the more optimal one.
The next recommended course of action is to compare the execution plans which you can do by clicking the button highlighted by the blue box above.
As you can see it is the same code but with very different execution plans.
Plan 1 shows that it decided to utilise an index scan. Plan 2 decided to utilise a seek and a lookup to return the rows. Further executions of the stored procedure the optimiser decided to use plan 2, this is NOT the optimal plan for the parameter I specifically passed in, as indicated by the sort / tempdb spills below and higher duration times.
You could also use the track queries option and enter the Query ID 1 to get another view of the difference between the plans where plan ID 2 is causing high query duration times.
Stabilizing Workloads
Plan forcing can resolve query performance regression caused by a plan change in a very short time. Before the world of Query Store, you would probably use plan guides to do such a task, but they were quite cumbersome to set up. With Query Store, all is needed is either one line of T-SQL or a click of a button. Here I want to force Plan ID 1 for Query ID 1.
The T-SQL command for such an action is shown below.
Or via SSMS (SQL Server Management Studio) you would click the Plan ID 1 and force.
You will see a confirmation of a successful force via a tick icon.
Any subsequent execution of the code will then use the forced plan and for this case, my stored procedure executed in 1 second rather than 50 seconds.
Re-checking Query Store you can see the last time I executed the query it was the quickest in terms of duration, indicated by the blue box below.
If you required to unforce the plan, then you would use either SSMS or TSQL code –
You are not only restricted to the GUI for analysing your workloads, SQL Server has many system tables and views for you to query to get general information about your workloads. For example, a common requirement is to know the most executed code on your server and because Query Store is enabled it is gathering this sort of information for you. More specifically you would need to execute code as shown below.
If plan stability is of utmost importance to you then you could run the query below to find if you have multiple plans for a query, again you could see this via SSMS. This is to show you that you can use TSQL if you need to develop some monitoring code to archive these details away.
Wait Statistics
Analysing wait statistics for a performance tuning exercise is one of the most important concepts to grasp. Starting with SQL Server 2017 and Azure SQL Database, wait statistics per query over time are available in Query Store which can hold very important information. From this, you will be able to drill down to the execution plan for those more prevalent waits on your system.
I set the chart to be based on average wait time over the past hour, as you can see, CPU is the biggest wait category.
You can then drill down by selecting the CPU bar to return the relevant execution Plan.
Mission Critical Servers
Many of you reading this blog post will be wondering what the performance impact of this tool is. Microsoft advises using two trace flags to improve performance and availability of your databases when Query Store is enabled. These are trace flags 7745 and 7752. Trace flag 7745 will prevent the default behaviour where Query Store writes data to disk before SQL Server can be shut down and trace flag 7752 enables asynchronous load of Query Store rather than synchronous behaviour which can prevent queries from executing before the Query Store has been recovered.
Query Store is a powerful feature and can make getting insights into your workloads very easy. Microsoft has made the user interface very friendly and efficient to use. Hopefully, this blog post will encourage you to enable this feature and use it.
References
https://blogs.technet.microsoft.com/dataplatform/2017/01/31/query-store-how-it-works-how-to-use-it/
