We know that the Query Store is one of the biggest enhancements to the SQL Server 2016 database engine that will complete the performance tuning toolbox of database administrators. The query store is extremely helpful, but it does require some management where we must enable it on each user database individually and need to change the space allocated to the query store from the default of 100 MB per database. We can use Query Store in all databases without concerns, in even densely packed pools.
The Query Store is the combination of two stores known as Plan Store and Runtime Stats Store. The most interesting fact is that both stores are in-memory objects that hold query information and the default space allocation for Query Store is 100 MB.
Common scenarios for using the Query Store
There are many common scenarios for using the Query Store features as given below:
- Quickly find and fix a plan performance regression by forcing the previous query plan. Fix queries that have recently regressed in performance due to execution plan changes.
- Determine the number of times a query was executed in a given time window, assisting a DBA in troubleshooting performance resource problems.
- Identify top n queries (by execution time, memory consumption, etc.) in the past x hours.
- Audit the history of query plans for a given query.
- Analyze the resource (CPU, I/O, and Memory) usage patterns for a particular database.
In the Query Store, a Plan Store that persists the execution plans and a Run-Time Stats Store that persists the statistics surrounding query execution, such as CPU, I/O, memory, and other metrics. SQL Server retains this data until the space allocated to Query Store is full. The number of unique plans that can be stored for a query in the plan store is limited by the max_plans_per_query configuration option. To reduce the impact on performance, SQL Server writes information to each of these stores asynchronously.
Query Store Catalog Views
There are seven catalog views which can be used get the information such as return metadata and query execution history as given below:
- Query_Store_Runtime_Stats – This view will show the run-time execution statistics for queries.
- Query_Store_Runtime_Stats_Interval - This view will show the start and end times for the intervals over which run-time execution statistics are collected.
- Query_Store_Plan - This view will show the execution plan information for queries.
- Query_Store_Query - This view will show the query information and its overall aggregated run-time execution statistics.
- Query_Store_Query_Text - This view will show the query text as entered by the user, including white space, hints, and comments.
- Database_Query_Store_Options - Returns the Query Store options for this database.
- Query_Context_Settings - Contains information about the semantics affecting context settings associated with a query.
Reviewing information in the query store
After enabling Query Store for a database, you have access to the following four dashboards:
Regressed Queries - We can use this dashboard to review queries that might have regressed because of execution plan changes. The dashboard allows us to view the queries and their plans as well as to select queries based on statistics (total, average, minimum, maximum, and standard deviation) by query metric (duration, CPU time, memory consumption, logical reads, logical writes, and physical reads) for the top 25 regressed queries over the last hour.
Overall Resource Consumption- We can use this dashboard to visualize overall resource consumption during the last month in four charts: duration, execution count, CPU time, and logical reads. You have the option to toggle between a chart view and a grid view of the query store data.
Top Resource Consuming Queries - We can use this dashboard to review queries in the set of top 25 resource consumers during the last hour. We can filter the queries by using the same criteria available in the Regressed Queries dashboard.
Tracked Queries - We can use this Use this dashboard to monitor a specify query.
All the dashboards except Overall Resource Consumption allow you to view the execution plan for a query. In addition, we have the option to force an execution plan at the click of a button in the dashboard, which is one of the most powerful features of the query store. However, the plan must still exist in the query plan cache to use this feature.
We can use the Query Store in all databases without concerns, in even densely packed pools. All issues related to excessive resource usage, that might have occurred when Query Store was enabled for the large number of databases in the Elastic Pools, have been resolved.