In this blog post, we will learn the basics of the Query Store feature in SQL Server.
Introduced in SQL Server 2016, the Query Store feature will store the Query plans, Queries, resource consumption information along with the run time stats information for your queries. This feature is not automatically enabled on your databases. It is a database-level option only. You need to enable the Query Store on the database to capture the queries and query information. You cannot enable the Query store on master or tempdb databases. The information captured by the Query Store is stored in the database you enabled the feature on, in the internal tables created in the PRIMARY filegroup and this cannot be changed. (Microsoft docs reference).
Query Store feature is enabled by default if you are using Azure SQL Database and disabled by default on Azure synapse analytical database.
So, what are the advantages if we enable the Query Store feature on your database?
- Very quickly identify the top resource consuming queries. Query Store captures CPU, IO, Memory, Duration, Logical Reads and Writes, Physical Reads, CLR Time, DOP, Row Count, Log Memory Used, Temp DB Memory Used, and Wait Time. You may say, I already have third party monitoring tool capturing the top resource consuming queries but look at the next several points to know Query store can offer.
- Query store captures the history of the execution plans for your queries. You can easily compare the difference between the plans by placing them side by side.
- If there are many execution plans generated for a query and you believe one of the plans is working better than others, you can force an execution plan of your choice with out changing any applications. This is very beneficial to quickly solve the query performance issues.
- Query Store feature can be used to capture the performance baselines. Personally, I used this feature when we upgraded the databases to new versions of SQL Server.
- We will be learning about the Automatic plan correction in the upcoming posts, available from SQL Server 2017 which depends on the Query Store feature that is enabled on the database. Automatic plan correction feature will monitor the regression between the execution plans and force the last good plan for your queries automatically.
- If you are in Azure, Query store hints is currently in preview. Just like the Query hints, you can use the Query Store hints to a Query by using its Query_id but without modifying the underlying code. Query hints can be used by using a stored procedure sys.sp_query_store_set_hints.
Plan cache will also capture the execution plans, right? So, what is the difference?
Plan cache Vs Query store
| Plan cache | Query Store |
| Flushed out on every server restart | Retains the data (CLEANUP_POLICY) |
| Stores latest plans | Collects plans over time |
| Evicted on memory pressure | Flush out intervals |
| Difficult to know why the plan changed | Useful when troubleshooting performance problems |
How to enable Query Store?
You can enable it by using the GUI or using T-SQL.
Operation modes:
Read write: This mode will read the information from the Query store (reading for any forced plan information or reading information by automatic plan correction etc) and write the queries, related resource consumption information into the Query store.
Read only: This mode will only read the information from the Query store. Queries will not be captured and written to the Query Store.
Off: Query Store is in a turned-off state.
How to set what Queries can be captured?
We can set this policy by using the capture modes available:
All: Will capture every query including every execution of ad hoc queries. Remember, this can quickly fill up your query store max size if you have many ad-hoc queries running on the database. This is not a recommended capture mode.
Auto: Will does not capture the queries that are not worth consideration. The threshold of what queries are captured is decided by Microsoft based on the query execution count, compile, and runtime duration. This is the recommended capture mode from Microsoft.
None: No queries will be captured by the Query Store.
Custom: You can customize the capture policy. During the mentioned capture policy time threshold, the conditions are evaluated, and based upon that the query will be captured in the Query Store.
Conclusion: In this blog post, we have gone through the overview of what the Query store feature is and the benefits of it being enabled on your user databases, the difference between the plan cache and Query Store, different operation modes, and capture modes. In the coming part, we will learn about the query store options and how the query store works behind the scenes.
Thank you for reading!
Resources:
Deepthi Goguri's SQL Server Blog 