Query store is a feature of SQL server that tracks and captures information about queries. Database administrators will find query store useful in trouble shooting performance issues. Most database scenarios query store is perfectly fine under default configuration. However every once in a while a DBA might want to specifically crack metrics where the default values either are too restrictive or 2 broad. In such cases the database administrator can now configure query store using a certain set of custom database scoped configurations.
The below query shows how you can configure query store under the custom capture mode.
ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON (
OPERATION_MODE = READ_WRITE
,CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90)
,DATA_FLUSH_INTERVAL_SECONDS = 900
,MAX_STORAGE_SIZE_MB = 1000
,INTERVAL_LENGTH_MINUTES = 60
,SIZE_BASED_CLEANUP_MODE = AUTO
,MAX_PLANS_PER_QUERY = 200
,WAIT_STATS_CAPTURE_MODE = ON
,QUERY_CAPTURE_MODE = CUSTOM
,QUERY_CAPTURE_POLICY = (
STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS
,EXECUTION_COUNT = 30
,TOTAL_COMPILE_CPU_TIME_MS = 1000
,TOTAL_EXECUTION_CPU_TIME_MS = 100
)
);
The most important setting to be configured in the above query is stale_capture_policy_threshold. It defines the time period during which query store evaluates the remaining parameters and decides if it wants to capture details of the query. With a default value of one day it assumes that you want to track queries that have executed for the last one day.
Once we tell the database to start tracking queries for the time period of one day the next power meter that needs to be configured is the execution count. The execution count basically enforces a minimum number of executions before a query can be entered into the query store. This helps ignore AdHoc queries that do not have any significant impact in your analysis. The next setting that the DBA will need to configure relates to CPU compile time and as its name suggests it tracks the compiled time of queries. This is an especially useful feature to identify queries that have significant compilation effort as a result of changing plans, parameter sniffing etc.Last but not least we have total execution CPU time in milliseconds. As its name suggests it tracks queries that have total CPU execution times greater than 100 milliseconds. Which typically includes queries that have parallelism or are CPU intensive as a result of the operations being performed on them. Most of the settings are self-explanatory and therefore should be easy to configure and fine tune by the DBA as needed.
It is however important to understand that by setting custom as the query store mode you are going to miss out on one off issues that may occur. Which is why I personally feel the defaults are good enough for a typical database administrative effort and DBAs should use custom only when they are looking for something specific or when they have identified scenarios where query store itself seems to have significant overhead on the server.
Please Consider Subscribing
