What is query store, what information do we get from Query Store?

Problem: What is query store, what information do we get from Query Store?

Solution:

Starting from SQL Server 2016, Query store is a feature that is available in Standard Edition.  This feature makes our life easy when trouble shooting, if you don’t know where to look for in terms of performance or bottlenecks of a query or a stored procedure.

As we can see the query store feature is show under the database folders.

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. It separates data by time windows so you can see database usage patterns and understand when query plan changes happened on the server. 

There are few options in the Query store which gives us top resources and high cost operators as shown in the above picture. 

Query Store has a default settings of Interval length of time to capture the statistics of a query and it helps to narrow down and pin point the problem, for every time span specified in refresh interval the query store gets new information and flushes the old statistics and query plan.

The default of interval to look is 100, you can set a value in 1, 5, 10, 15, 30, 60, and 1440 minutes. No arbitrary values can be set.

There are few other Configuration properties that can be set in order to customize the query store watch window.

OPERATION_MODE – Read_Write/Read_Only.

CLEANUP_POLICY (STALE_QUERY_THRESHOLD_DAYS) –number of days to store.

DATA_FLUSH_INTERVAL_SECONDS – expiration of the query store.

MAX_STORAGE_SIZE_MB – Maximum limit of storage to use for query store.

INTERVAL_LENGTH_MINUTES – watch window to store query store.

SIZE_BASED_CLEANUP_MODE – clean up control based on a limit size.

QUERY_CAPTURE_MODE – filters the queries to capture.

MAX_PLANS_PER_QUERY – Maximum number of query plans to set.

Syntax:

ALTER DATABASE <DatabaseName> SET QUERY_STORE = ON; 

Example:

In this example, I would like to show we can enable the query store and where to look for some specific properties after the query store is enabled.

ALTER DATABASE Testuser SET QUERY_STORE =ON;

OUTPUT:

The Query store properties are show on the properties of a database. As you see below.

In the later articles, I would like to show we can use this in a scenario based situation.