QUERY STORE

When examining the performance of a query in SQL Server, the first thing that comes to our mind is to look at the execution plan of the query. In this way, we can decide whether this plan is the right plan by examining the plan that the query follows while working. However, the execution plans of queries can be deleted or changed for various reasons. With the “Query Store” feature that comes with SQL Server 2016, we can save the execution plans of queries. Thanks to this feature, it is possible to avoid performance problems that may arise from deleting or changing the execution plan. Most importantly, by comparing the execution plans of a query with the Query store, we can force the query to select the correct plan. The Query Store feature, which is turned off by default, can be activated on the basis of the database. After activation, queries begin to be collected by adjusting the settings. These operations can be performed both through the management studio and with the script.

ALTER DATABASE <database_name>

SET QUERY_STORE (OPERATION_MODE = READ_WRITE,

CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),

DATA_FLUSH_INTERVAL_SECONDS = 900,

INTERVAL_LENGTH_MINUTES = 30,

MAX_STORAGE_SIZE_MB = 1000,

QUERY_CAPTURE_MODE = ALL,

SIZE_BASED_CLEANUP_MODE = AUTO,

MAX_PLANS_PER_QUERY = 200,

WAIT_STATS_CAPTURE_MODE = ON)

Operation Mode(Requested): This feature is activated by reading/writing.

Data Flush interval: Determines how often Query Store data is written to disk.

Statistics Collection Interval: Refers to the time of data collection.

Max Plans Thu Query: Refers to the value of how many plans can be stored per query in the Query Store.

Max Size (MB): The maximum size that the Query Store can come to is determined. As soon as this value is exceeded, the Query Store goes into Readonly mode.

Query Store Capture Mode: “All” is selected so that all queries running in the database can be stored in the Query Store.

Based on You Cleanup Mode: ”Auto” allows you to start cleaning the Query Store when 90% of Max Size is reached.

Stale Query Threshold (Days): Determines how much data will be stored in the Query Store.

Wait Statistics Capture Mode: In order for the Query Store to collect wait statistics, it must be set to “On”.