Overview of Query Store Feature - SQL Server 2016
Contributions, SQL Server 2016

Overview of Query Store Feature – SQL Server 2016

Query Store automatically captures and retains a history of queries, query execution plans, and runtime execution statistics for troubleshooting performance problems caused by query plan changes. This feature helps a great deal in simplifying performance troubleshooting by helping us to quickly find performance differences.  Query Store is available in all the editions of SQL Server 2016.

We can find information captured by query store even after a server restart. This is because the information persists inside internal tables of the database, not in the memory. When the Query Optimizer compiles a new Execution Plan for a query that performs much worse than the previous Execution Plan for that same query, it allows us to choose a specific execution plan to use, rather than the default plan. It is similar to force plans in earlier versions.

Given below are the few common uses of query store defined by Microsoft:

  • 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 is 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.

 

Advantages-
  • It is integrated in SQL Server engine itself and once enabled it automatically starts capturing information about query runtime at source level.
  • It persists the captured information in internal tables inside the database for later use. This means that users can get the information even after a server restart. During regular operation, a plan cache is stored in memory and is lost after server restart.
  • It can be enabled on an individual database for performance troubleshooting.
  • All the information inside the query store can be accessed through DMVs and built-in reports.

 

Query Store Architecture

Query Store consists of two stores, the Plan Store and the Runtime Stats Store. These stores are in-memory objects that hold query information.

Overview of Query Store Feature - SQL Server 2016

Once query store option is enabled for a database, it starts capturing query texts executed against that database. After SQL Server does compilation of a query, the compiled query Execution Plan is written to the Plan Store. After execution of query, the runtime information of the query is captured in the Runtime Stats Store. All the information is stored in-memory initially. After a specific interval the information inside the in-memory stores is asynchronously written to disk to harden the information. Query store writes all the information about execution plans and runtime statistics into the primary filegroup of that database.

The interval (DATA_FLUSH_INTERVAL_SECONDS option) at which the Query Store flushes data to disk can be configured. The default value of 900 seconds (i.e. 15 minutes) is an optimal balance between query capture performance and data availability. In case of memory pressure, runtime statistics can be flushed to disk earlier than defined with DATA_FLUSH_INTERVAL_SECONDS. We should set this value appropriately as flushing more often can impact the performance. When enabling query store for the first time, it is common to see flushes occur more often since more new Execution Plans are encountered. The lower the interval, the higher the impact on performance. New Execution Plans that are first seen by the Query Store will be flushed to disk more aggressively than Execution Plans that already exist inside the Query Store.

Query Store access data from disk and in-memory simultaneously. A new table valued function sys.query_store_runtime_stats has been introduced which is used to merge in-memory and on-disk data to get a consolidated view of required information.

How to Enable Query Store

By default, this feature is not enabled for new databases. It can be enabled by using either SSMS or TSQL.

Using SSMS:
  1. In Object Explorer, right-click a database, and then click Properties.
  2. Next, in the Database Properties dialog box, select the Query Store
  3. At last, in the Operation Mode (Requested) box, select On.

 

Using TSQL:

            ALTER DATABASE <database_name> SET QUERY_STORE = ON;

 Below are the various configuration options for query store:

GUI Option T-SQL Option
Operation Mode (Requested) OPERATION_MODE (READ_WRITE or READ_ONLY)
Data Flush Interval (Minutes) DATA_FLUSH_INTERVAL_SECONDS
Statistics Collection Interval INTERVAL_LENGHT_MINUTES
Max Size (MB) MAX_STORAGE_SIZE_MB
Query Store Capture Mode QUERY_CAPTURE_MODE (ALL or AUTO or NONE)
Size Based Cleanup Mode SIZE_BASED_CLEANUP_MODE (AUTO or OFF)
Stale Query Threshold (Days) CLEANUP_POLICY (STALE_QUERY_THRESHOLD_DAYS)
  MAX_PLANS_PER_QUERY
Example:

ALTER DATABASE <database_name>

SET QUERY_STORE = ON
(
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), –No. of days to retain data in query store    DATA_FLUSH_INTERVAL_SECONDS = 900, — determines frequency at which data written to the query store is persisted to disk.    MAX_STORAGE_SIZE_MB = 1024, — Configures the maximum size of the query store.    INTERVAL_LENGTH_MINUTES = 5, —time interval at which runtime execution statistics data is aggregated into the query store.    SIZE_BASED_CLEANUP_MODE = AUTO, — Controls whether the cleanup process will be automatically activated when total amount of data gets close to maximum size.    QUERY_CAPTURE_MODE = ALL, — whether to capture all queries, or relevant queries based on execution count and resource consumption, or stops adding new queries and just tracks current queries.    MAX_PLANS_PER_QUERY = 200 — An integer representing the maximum number of plans maintained for each query.
)

Conclusion

Query Store is one of the new and very useful features introduced in SQL Server 2016. The feature troubleshoots performance problems caused by query plan changes. It retains the information about history of queries, execution plans & runtime statistics and we can query such information with the help of DMVs (Dynamic Management Views) or TDF (Tabular Data Function) as well as by using some built-in reports.

More Information:

Monitoring Performance By Using the Query Store

How Query Store Collects Data

About The Author