Query Store new feature in SQL Server 2016

Query Store new feature in SQL 2016

Query store feature is new functionality to look into query plan history for performance tuning, it helps to find out the performance degradation by analysing performance difference in query plan.

 

How Query store helps:

Collects all query texts along with relevant properties

Store all plan choices and performance metrics.

Allows you to force plans from history. (One can force database to choose particular plan instead sql engine do it for you)
Identify queries that have gotten slower recently
Make sure this works across server restart, upgrade and query recompiles.

All the compiled or executed query plan will be stored in user database with statistics with history data as well.

One can choose how much data it will store and what would be analysing time for query store.

 
Use ‘Alter Database’ or query store properties in GUI for database statement to enable query store.

eg: Alter Database QueryDB Set Query_store = ON;  (not possible for Master and tempdb database)

 

Execution plan for query over time get costlier or evolved due to multiple reason like statistics changes, Schema Changes, index modification etc. he procedure cache (where cached query plans are stored) only stores the latest execution plan. Plans also get evicted from the plan cache due to memory pressure. As a result, query performance regressions caused by execution plan changes can be non-trivial and time consuming to resolve.

Since a query store can store multiple execution plans per query processor one can force query to use particular plan to execute.

There are 4 different option available for query plan  troubleshooting under QUERY STORE option in GUI

Regressed Queries	Pinpoint queries for which execution metrics have recently regressed (i.e. changed to worse). Use this view to correlate observed performance problems in your application with the actual queries that needs to be fixed or improved.
Top Resource Consuming Queries	Choose an execution metric of interest and identify queries that had the most extreme values for a provided time interval. Use this view to focus your attention on the most relevant queries which have the biggest impact to database resource consumption.
Tracked Queries	Track the execution of the most important queries in real-time. Typically, you use this view when you have queries with forced plans and you want to make sure that query performance is stable.
Overall Resource Consumption	Analyse the total resource consumption for the database for any of the execution metrics. Use this view to identify resource patterns (daily vs. nightly workloads) and optimize overall consumption for your database.

By using compare option one can compare the multiple plan of single query and find the problem.

To force a plan, select a query and plan, and then click Force Plan. You can only force plans that were saved by the query plan feature and are still retained in the query plan cache.

Configuration option available for Query Store:

OPERATION_MODE: Can be READ_WRITE or READ_ONLY.

CLEANUP_POLICY:  Configure the STALE_QUERY_THRESHOLD_DAYS argument to specify the number of days to retain data in the query store.

DATA_FLUSH_INTERVAL_SECONDS: Determines the frequency at which data written to the query store is persisted to disk. To optimize for performance, data collected by the query store is asynchronously written to the disk. The frequency at which this asynchronous transfer occurs is configured via DATA_FLUSH_INTERVAL_SECONDS.

MAX_STORAGE_SIZE_MB: Configures the maximum size of the query store. If the data in the query store hits the MAX_STORAGE_SIZE_MB limit, the query store automatically changes the state from read-write to read-only and stops collecting new data.

ALTER DATABASE QueryDB SET QUERY_STORE (MAX_STORAGE_SIZE_MB = );

INTERVAL_LENGTH_MINUTES: Determines the time interval at which runtime execution statistics data is aggregated into the query store. To optimize for space usage, the runtime execution statistics in the Runtime Stats Store are aggregated over a fixed time window. This fixed time window is configured via INTERVAL_LENGTH_MINUTES. 

ALTER DATABASE QueryDB SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 15);

SIZE_BASED_CLEANUP_MODE: Controls whether the clean-up process will be automatically activated when total amount of data gets close to maximum size.

QUERY_CAPTURE_MODE: Designates if the Query Store captures 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: An integer representing the maximum number of plans maintained for each query.

To set all option in single query:

ALTER DATABASE QueryDB

SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY =
    (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 3000,
    MAX_STORAGE_SIZE_MB = 500,
    INTERVAL_LENGTH_MINUTES = 15,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_PLANS_PER_QUERY = 1000
);
 

Following Catalog views and stored procedure to use Query store function:

sys.database_query_store_options (Transact-SQL)

·         sys.query_context_settings (Transact-SQL)
·         sys.query_store_plan (Transact-SQL)
·         sys.query_store_query (Transact-SQL)
·         sys.query_store_query_text (Transact-SQL)
·         sys.query_store_runtime_stats (Transact-SQL)
·         sys.query_store_runtime_stats_interval (Transact-SQL)

·         sp_query_store_flush_db (Transact-SQL)
·         sp_query_store_reset_exec_stats (Transact-SQL) (to clear runtime statistics for a given plan)
·         sp_query_store_force_plan (Transact-SQL) (To force a query with specific plan id)
      EXEC sp_query_store_force_plan @query_id = 12, @plan_id = 32;

·         sp_query_store_unforce_plan (Transact-SQL) (To unforce a query with specific plan id)
   EXEC sp_query_store_force_plan @query_id = 12, @plan_id = 32;

·         sp_query_store_remove_plan (Transct-SQL)  (use plan id)
·         sp_query_store_remove_query (Transact-SQL) (use query id)

Query Store internal tables are created in the PRIMARY filegroup during database creation and that configuration cannot be changed later. If you are running out of space you might want to clear older Query Store data by using the following statement.

ALTER DATABASE QueryDB SET QUERY_STORE CLEAR;

Time based clean-up of data can be set like:

ALTER DATABASE QueryDB SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 14));