Query Data Store Size Cleanup Retention

SQL Server's Query Store introduced in SQL Server azure and SQL Server 2016 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.

Once you enabled the Query Store for the database you can configure some Query Store settings, for more information: Query Store Settings and Limits.

One of these settings is the size based cleanup as you may notice in figure 1:

Figure 1: cleanup setting

Query Data store (QDS) size based cleanup controls whether cleanup will be automatically activated when total amount of data gets close to maximum size: 1 = OFF – size based cleanup won’t be automatically activated. This is the default configuration value. 2 = AUTO - size based cleanup will be automatically activated when size on disk reaches 90% of max_storage_size_mb. Size based cleanup removes the least expensive and oldest queries first. It stops at approximately 80% of max_storage_size_mb. https://msdn.microsoft.com/en-us/library/dn818146.aspx

How it works:

The setting Max Size(MB) means the limit size that QDS can reach, that is a user configurable value and you may see that in the Query Store settings menu found under the respective database properties window as shown in figure 1.

Once QDS reaches a certain percentage (approximately 80% ) of the maximum storage size (Max Size (MB) setting), the target amount of data that needs to be deleted will take in consideration some internals threshold and it will be calculated and deleted.

QDS Retention clean-up has two stages:

1)      In the first stage, old runtime stats are cleaned.

o   Interval based: Interval and all stats in the interval are deleted together.

o   The query with the lowest cost is deleted first; if two queries have the same cost, the one with lowest last execution time is deleted first

2)      In the second stage queries, together with associated plans and statistics are deleted.

o   Queries with the least resource usage. (The resource use is calculated as the average total (CPU usage or Duration) per day)

o   Oldest queries.

The second stage is invoked only if the first one doesn’t free up enough disk space.

Some of the following XEvents available for monitoring retention policy behaviour:

query_store_size_retention_cleanup_started

o   Database ID

o   Current QDS size

o   Max QDS size

o   Delete Data Size

query_store_size_retention_cleanup_finished

o   Database ID

o   Deleted Data Size

o   (Number of deleted intervals)

o   (Deleted Intervals Size)

o   Number of deleted queries

o   Number of deleted plans

o   (Deleted Query Size

query_store_size_retention_cleanup_skipped

o   Database ID

o   Time passed from the last clean-up

o   Cooldown period

Figure 2 shows the query store retention in extended events:

Figure 2: Extended Event QDS retention police

Aside from the size based retention policy QDS already has time based retention policy. This policy makes sure that all QDS data older than the user configurable setting Stale Query threshold (Days) is deleted from QDS.

Final considerations:

• It is possible to check current QDS size and limit with following T-SQL command (execute on the database that QDS is enable):

[sql]
SELECT current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options
[/sql]

• If it is necessary, you may also clear QDS data.

[sql]
USE master
GO
ALTER DATABASE [database that QDS is enable] SET QUERY_STORE CLEAR ALL
[/sql]

•  To activate or deactivate size-based cleanup police run the following statement:

   Activate(default)

[sql]
ALTER DATABASE [database_name]
SET QUERY_STORE (SIZE_BASED_CLEANUP_MODE = Auto);
[/sql]

For instance to activate size-based cleanup in the QueryStoreDB database using Alter Database T-SQL Command:

[sql]
ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (SIZE_BASED_CLEANUP_MODE = Auto); 
[/sql]

   Deactivate

[sql]
ALTER DATABASE [database_name] 
SET QUERY_STORE (SIZE_BASED_CLEANUP_MODE = off);
[/sql]

More information at msdn: https://msdn.microsoft.com/en-us/library/mt604821.aspx

• To manually remove a plan from QDS or Clear the QDS stats:

[sql]
sp_query_store_remove_plan – removes a single plan

sp_query_store_reset_exec_stats – clear runtime statistics for a given plan
[/sql]

• Clean up QDS goals:

Clean-up QDS disk space it is needed to keep QDS running and collecting stats.

Optimize the feature for cloud users, since the space constraint is much more prominent in that environment.

Allow other QDS workloads to run in parallel with the clean-up, and not to serialize on it.

Thanks again, Dejan Krakovic for the discussion that we had around this subject.

Liliam Leme

UK Data Platform PFE