SQL Server Query Store

Vitaliy Popovych

About me

• Vitaliy Popovych• Database Developer at Intapp

Agenda

• Query Store• How it works• Configuration Options• UI Management Studio• Query Store Catalog Views• Query Store Stored Procedures• Best Practice with the Query Store• Demo

Recompiling Execution Plans• Changes made to a table or view referenced by the query (ALTER TABLE and

ALTER VIEW).• Changes made to a single procedure, which would drop all plans for that

procedure from the cache (ALTER PROCEDURE).• Changes to any indexes used by the execution plan.• Updates on statistics used by the execution plan, generated either explicitly

from a statement, such as UPDATE STATISTICS, or generated automatically.• Dropping an index used by the execution plan.• An explicit call to sp_recompile.• Large numbers of changes to keys (generated by INSERT or DELETE

statements from other users that modify a table referenced by the query).• For tables with triggers, if the number of rows in

the inserted or deleted tables grows significantly.• Executing a stored procedure using the WITH RECOMPILE option.

What is Query Store?

Query Store

Compile MSG

Execute MSG

Query Store

Async Write-Back

Compile

Execute

SQL

Plan Store

Runtime Stats

Query Store

Schema

How to start

Common scenarios• 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.

Enable Query StoreBy Using Transact-SQL Statements

ALTER DATABASE [AdventureWorks2016CTP3] SET QUERY_STORE = ON(CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 31), DATA_FLUSH_INTERVAL_SECONDS = 900, INTERVAL_LENGTH_MINUTES = 5, MAX_STORAGE_SIZE_MB = 1024, QUERY_CAPTURE_MODE = ALL, SIZE_BASED_CLEANUP_MODE = AUTO)GO

By Using the Query Store Page in Management Studio- In the Database Properties dialog box, select the Query Store page.- In the Enable box, select True.

Configuration Options

OPERATION_MODE CLEANUP_POLICY DATA_FLUSH_INTERVAL_SECONDS MAX_STORAGE_SIZE_MB INTERVAL_LENGTH_MINUTES SIZE_BASED_CLEANUP_MODE MAX_PLANS_PER_QUERY

Configuration OptionsMAX_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) default value = 100 MB

OPERATION_MODE (Can be READ_WRITE or READ_ONLY) default value = READ_WRITE

CLEANUP_POLICY (Configure the STALE_QUERY_THRESHOLD_DAYS argument to specify the number of days to retain data in the query store)default value = 367 days

Configuration OptionsDATA_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) default value = 900 sec

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) default value = 60 min

Configuration OptionsQUERY_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) default value = all

MAX_PLANS_PER_QUERY (An integer representing the maximum number of plans maintained for each query) default value = 200

SIZE_BASED_CLEANUP_MODE (Controls whether the cleanup process will be automatically activated when total amount of data gets close to maximum size)default value = OFF

Information

Execution metric Statistic function

CPU time, Duration, Execution Count, Logical Reads, Logical writes, Memory consumption, Physical Reads

Average, Maximum, Minimum, Standard Deviation, Total

UI Management StudioRegressed Queries (Pinpoint queries for which execution metrics have recently regressed i.e. changed to worse)

Overall Resource Consumption (Analyze the total resource consumption for the database for any of the execution metrics)

Top Resource Consuming Queries (Queries which have the biggest impact to database resource consumption)

Tracked Queries (Track the execution of the most important queries in real-time)

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.

Overall Resource Consumption

Analyze 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.

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.

Query Store Catalog Views• sys.database_query_store_options (Returns the Query Store

options for this database)

• sys.query_store_plan (Contains information about each execution plan associated with a query)

• sys.query_store_query (Contains information about the query and aggregated runtime execution statistics)

• sys.query_store_query_text (Contains the Transact-SQL text and the SQL handle of the query)

• sys.query_store_runtime_stats (Contains information about the runtime execution statistics information for the query)

• sys.query_store_runtime_stats_interval (Contains information about the start and end)

Query Store Stored Procedures• sp_query_store_flush_db (Flushes the in-memory portion of the

Query Store data to disk)

• sp_query_store_force_plan (Enables forcing a particular plan for a particular query)

• sp_query_store_remove_plan (Removes a single plan from the query store)

• sp_query_store_remove_query (Removes the query, as well as all associated plans and runtime stats from the query store)

• sp_query_store_reset_exec_stats (Clears the runtime stats for a specific query plan from the query store)

• sp_query_store_unforce_plan (Enables unforcing a particular plan for a particular query)

Best Practice with the Query Store

• Use the Latest SQL Server Management Studio• Keep Query Store Adjusted to your Workload• Verify Query Store is Collecting Query Data

Continuously• Set the Optimal Query Capture Mode• Keep the Most Relevant Data in Query Store• Avoid Using Non-Parameterized Queries• Check the Status of Forced Plans Regularly

Demo

Resources• SQLServer Blog• MSDN• A flight data recorder for your database (Borko Novakovic)• The SQL Server Query Store (Benjamin Nevarez)

Question?

Thanks!

Vitaliy PopovychE-mail: vitaliy.popovych@outlook.com