Query store was introduced in SQL Server 2016. It is often referred to as a “flight data recorder” for SQL Server. Its main function is that it captures the history of executed queries as well as certain statistics and execution plans. Furthermore, the data is persistent, unlike the plan cache in which the information is cleared upon a server restart or reboot. You can customize, within Query Store, how much and how long the query store can hold the data.
This feature is enabled on the database level. Though, I have strongly urged many to enable this by default by enabling it in the model database. That way every time you create a new database on a particular instance, query store will already be running.
The query store is made up of 3 components.
- Plan store for holding execution plan information
- Runtime stats for holding statistical information
- Wait for stats for holding wait statistics information
It’s great to have all this information in one location as opposed to gathering it separately with custom scripts.
The remainder of this article will present some use cases for query store. It isn’t meant to be overly technical (we can save that for another post). Rather, it will give us good talking points, especially when it comes to justifying to management why your environment could use this feature.
Why should I use the Query Store?
Query store can be used for a number of things. For example:
- Find out what was happening on my server last night?
- Identify and fix queries suffering from parameter sniffing or plan regression
- Testing (ie – when upgrading to a new version of SQL server, or development projects)
What happened on my server last night?
Query store is great for a historical view of what your server was doing. It’s often referred to as “a flight data recorder”.
There were so many times where I was asked “what happened to so and so’s process last night? why did it run so long etc…”
Query store would have saved me a ton of time to find out what was going on and ideas on how to correct it. Not only that, but the fact that we can identify it so quickly means that we can prevent this from happening again.
Once the query store is enabled you can look at the reports in the “query store” section of the databases. Please reference the image below.
Identify and Fix queries suffering from Parameter sniffing or Plan regression:
There are times when the Query Optimizer may choose a different plan due to certain values for parameters. And in rare cases, this new plan is not optimal for most inputs and thus gives us degraded performance.
In the past, this was difficult to identify and remedy. Query store has empowered us as DBAs and developers. Utilizing the “Regressed Queries” option from SQL Server Management Studio can be used as a starting point for analysis. Now you have what SQL has identified as queries in which the execution metrics have been degraded.
The graphical user interface makes it easy to see the multiple plans and which are more resource intensive. This visualization gives a starting point. From here you can see actual execution plan and make an educated decision as to which one you want to force (or not).
In order to force a plan, it’s as simple as selecting the query plan and pressing the “force plan” button highlighted below.
Testing
There are actually a couple of scenarios in regard to testing. One would be for an upgrade or migration project. Another could be for a development project.
With a migration, you can do a dry run and then run a workload against a database with query store enabled in the old compatibility mode. Once that information is collected, set the databases compatibility mode to the latest and continue testing. Check the query store to see if there have been any plan regression and tune accordingly.
The approach might look something like this:
- Migrate the database to a new version of SQL server. But leave the compatibility level under the older version of SQL
- Enable query store on said database
- Run workload against the database and let query store collect information. (make sure query store is sized properly depending on how much you think you need)
- Set the compatibility level for this database to the latest version of SQL Server
- Check query store for any query regressions. Once you’ve identified anything, make sure to document it
- From there either force plans or tune the query to run more efficiently
Look at the top consuming queries and see if you want to tune them or force a plan or just gather a baseline of statistics and execution plans for the major store procedures and queries.
Documentation is important, and we don’t do it enough. Gathering as much baseline information as possible early on will help tremendously as your database and workload increase over time.
You can utilize query store to analyze resources such as CPU, I/O and memory at the database level. This will allow you to see the patterns early on see how it either deviates over time or if there is a onetime occurrence that shows a change in the pattern.
This is an example of doing our due diligence as we prepare to migrate or upgrade databases to new versions of SQL. Take advantage of it, document it, and present it to management.
As with the above paragraph, the same approach can be used when working on a development project. You can identify queries, parameter sniffing ahead of time and either tune the queries or force the plan before the go-live date.
Query store for all?
One disadvantage of Query store is that it is only available for SQL Server 2016 and later versions. But thanks to the Open Query store project, developed by William Durkin, this functionality has been made available to everyone on SQL Server 2008 and higher.
Open Query store functionality is also included in ApexSQL Plan, a new tool for query plan analysis.
Conclusion:
I hope that you found this article informative. It was not meant to be overly technical, rather it’s intent was to give some ideas on how you can utilize query store in your environment. This is especially useful if you must bring it up to management so that they know how useful it will be to you as a DBA and in turn, how it will impact the business.
He started his IT career in helpdesk world and eventually moved into the networking/systems administrator side of things. After some time, he developed an affection for working with Databases (mainly SQL Server) and has focused his career on that for the past eight years. He has worked for various industries in both large and small environments all with different needs.
SQL Shack has provided him with an opportunity to contribute to a community that has given him so much throughout the years.
View all posts by Aamir Syed
- Is SQL Server Always Encrypted, for sensitive data encryption, right for your environment - July 27, 2018
- Use cases for Query Store in SQL Server - July 18, 2018
- When to Use SQL Temp Tables vs. Table Variables - February 21, 2017