Query Store - Settings and Limits

SQL Server 2016 and SQL Azure represent the biggest leap forward in Microsoft data platform history with features that increase performance, simplify management, and transform your data into actionable insights—all on a database that has the least vulnerabilities of any major platform.

For those of us interested in the improvements made to the core engine, you will find an invaluable  source of information in the blog series, It Just Runs Faster from Bob Dorr and Bob Ward. I strongly recommend you spend some time reading through the information posted there.

For this series of posts, I am going to focus on one of my favourite new features available in SQL Server 2016 and SQL Azure: the SQL Server Query Store. And I’ll begin with an overview of the setup options.

Before I start to write about this, I would like to thank Bob Ward and Dejan Krakovic to help us organize the information related to this subject.

So, first the basics, What is Query Store?

“The SQL Server Query Store feature provides you with insight on query plan choice and performance. It simplifies performance troubleshooting by helping you quickly find performance differences caused by query plan changes. Query Store automatically captures a history of queries, plans, and runtime statistics, and retains these for your review.” MSDN definition: https://msdn.microsoft.com/en-US/library/dn817826.aspx

If you want to know, how to enable it, you can use same reference that was used for the Query Store Definition: https://msdn.microsoft.com/en-US/library/dn817826.aspx

In summary, The SQL Server Query Store provides a mechanism to track and persist all relevant data associated with a query’s execution.

However, what gets stored very much depends on the type of settings you choose when you enable the Query Store. So, let’s have a look at the various options.

 

Query Store Limits and Setting.

Query Store contains two stores:

• A plan store that persists all information relevant to the execution plan:
• Sys.query_store_query_text - Presents unique query texts executed against the database. (https://msdn.microsoft.com/en-us/library/dn818159.aspx )
• Sys.query_store_plan - Presents estimated plan for the query with the compile time statistics (https://msdn.microsoft.com/en-us/library/dn818155.aspx )
• sys.query_context_settings - Presents unique combinations of plan affecting settings under which queries are executed. (https://msdn.microsoft.com/en-us/library/dn818148.aspx )
• sys.query_store_query - Query entries that are tracked and forced separately in the Query Store.( https://msdn.microsoft.com/en-us/library/dn818156.aspx )
• A run-time stats store that persists the performance statistics such as CPU, I/O, memory, and other metrics.
• sys.query_store_runtime_stats_interval - Query Store divides time into automatically generated time windows (intervals) and stores aggregated statistics on that interval for every executed plan. (https://msdn.microsoft.com/en-us/library/dn818158.aspx )
• sys.query_store_runtime_stats - Aggregated runtime statistics for executed plans. (https://msdn.microsoft.com/en-us/library/dn818147.aspx )

The Plan Store is flushed every 15 minutes by default.

The default collection interval for the runtime statistics is 60 minutes.

That settings can be changed in the Query Store settings menu found under the respective database properties window

That settings can be changed in the Query Store settings menu found under the respective database properties window as shown in figure 1.

Figure 1: Database Query Store Properties

 

SQL Server will retain this data for the designated retention period or until the space allocated to Query Store is full:

• Retention: 30 days (default)
• Storage query store size (max): 100MB (default)
• Capture Mode: All, Auto, None
• Sized based cleanup of AUTO (Default is AUTO) = Remove oldest and least expensive

 

Max Size (MB): Specifies the limit for the data space that Query Store can consume within the database. This is the most important setting that directly affects operation mode of the Query Store.

While Query Store collects queries, execution plans and statistics, its size in the database grows until this limit is reached. When that happens, Query Store automatically changes the operation mode to read-only and stops collecting new data. You should monitor this closely to make sure you have sized the store appropriately to contain the full history you’d like to retain.

Stale Query Threshold (Days): Time-based clean-up policy that controls the retention period of persisted runtime statistics and inactive queries.

Size Based Clean-up Mode: Specifies whether automatic data clean-up will take place when Query Store data size approaches the limit.

Capture mode:

• All – Captures all queries. This is the default option.
• Auto – Infrequent queries and queries with insignificant cost are ignored. (Ad hoc recommended)
• None – Query Store stops capturing new queries.

 

Final Considerations:

• Query store is considered a query flight recorder meaning, that even in the case of SQL Server restart the data will be available because it has been persisted to disk. It does not reside only in memory.
• If you have a lot of Ad Hoc queries in your environment, enable Auto capture mode.
• Query store is enabled at the database level. It is not an instance level setting.
• Queries executed from a different database context will not be tracked.
• DDL statements will not be capture. Example: Alter database…
• It can store up to 200 unique plans per query.

Liliam Leme & Chris Leo

UK Data Platform PFE