In the previous post we learnt about some basic concepts of SQL Server query store and some of the usage scenarios. In this post we will see how Query Store can be setup and what are various options available to control its behavior.
While GUI interface is there that you can see by right clicking on the database and its properties, we will enumerate the corresponding T-SQL for various options available on the GUI dialog as shown below –
As we mentioned in the previous post that this is a database-level feature so can simply be turned on/off by ALTERing the database as follows –
ALTER DATABASE Scratchpad SET Query_Store = ON
ALTER DATABASE Scratchpad SET Query_Store = OFF
Note that you can’t turn off Query Store for Azure SQL Databases.
Various options that you see on GUI like the operating mode, data flush interval, capture mode etc. is set through T-SQL as follows. You can specify one or multiple options as indicated below. For any option that you don’t specify, a default value is taken by SQL.
ALTER DATABASE Scratchpad SET Query_Store = ON
(
OPERATION_MODE = READ_ONLY -- Operation Mode (Requested)
-- Read => only read only; no new capturing
, QUERY_CAPTURE_MODE = ALL -- Query Store Capture Mode; ALL, Auto, None, Custom
-- Auto means based on resoure consumption
-- None means no new queries to be captured
-- Custom - based on a custom policy
, MAX_STORAGE_SIZE_MB = 1024 -- Max Size (MB)
, INTERVAL_LENGTH_MINUTES = 60 -- equivalent to Statistics Collection Interval
-- 1, 5, 10, 15, 30, 60, 1440;
-- Granularity at which statistics are aggregated
, DATA_FLUSH_INTERVAL_SECONDS = 900 -- Data Flush Interval (Minutes);
-- By default 15 Minutes
, SIZE_BASED_CLEANUP_MODE = AUTO
, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30)
-- By Default 30 days
, MAX_PLANS_PER_QUERY = 200
)
Query_Capture_Mode: asks for what all queries needs to be captured e.g. All queries or some specific like resource taking etc. Capturing all queries on a very active transactional system is generally not a good idea. Default is Auto mode which ignores queries with insignificant compilation time and durations are ignored along with infrequent queries. This default mode was changed in SQL Server 2019. Earlier, All used to be the default mode. Custom mode allows you to capture queries based on certain metrics like execution count, compilation, CPU time etc. This mode is useful where most of the queries are unique to avoid rapid growth of store.
Data_Flush_Interval_Seconds: says how frequently data captured by Query Store needs to be written back to disk. We mentioned in the previous post that it’s an asynchronous process where frequency is controlled by this parameter.
At any point of time, if you want to purge the data captured by Query Store, you can clear it as follows –
-- Purge Query Data
ALTER DATABASE Scratchpad SET QUERY_STORE CLEAR
If size of the query store reaches Max Size, it gets converted into read-only mode until more space is added (by adjusting Max Size property) OR cleanup happens. If size based cleanup is configured then it will remove the queries older than the defined time-based cleanup.
To conclude the post, you might be wondering how much stress does it put on your database performance when you enable this feature. Not much, Microsoft says, it’s a trade-off of about 3-5% performance impact on your database with this enabled or disabled. In majority of the cases unless you have thousands of transactions per second, this trade-off is acceptable.