Overview of Query Store Feature---SQL Server 2016

Start Here

Get in touch with a
TriCore Solutions specialist

Blog | Sep 7, 2016

Overview of Query Store Feature---SQL Server 2016



Query Store Feature helps a great deal in simplifying performance troubleshooting by helping you to quickly find performance differences. This feature is available in all the editions of SQL Server 2016.


Introduction to Query Store Feature:

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 you to quickly find performance differences. This feature is available in all the editions of SQL Server 2016.

We can find information captured by query store even after a server restart as it persists to such information inside internal tables of the database, not in the memory. When the Query Optimizer compiles a new Execution Plan for a query that performs poorly vs the previous Execution Plan for that same query, it allows you to choose a specific execution plan to use rather than the default plan. It is similar to what  we do with force plans in earlier versions.

A Few Ccommon 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 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. 

Advantages:

  • It is integrated in the 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. As in we can get the information even after a server restart unlike during regular operation where a plan cache is stored in memory and is lost after server restart.
  • It can be enabled on an individual database for which we want to do 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.

SQL Query Support

Img Source: https://msdn.microsoft.com/en-us/library/mt631173.aspx 

Once query store option is enabled for a database,
it starts capturing query texts executed against that database. When SQL Server does compilation of a query, the compiled query Execution Plan is written to the Plan Store and 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. You 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. We can enable it either by using SSMS or TSQL.

Using SSMS:

  1. In Object Explorer, right-click a database, and then click Properties.
  2. In the Database Propertiesdialog box, select the Query Store
  3. 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, which can smartly help us to troubleshoot the performance problem 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. For any questions on the Query Store feature click below:

Ask Vaibhav