There are multiple methods to remove data from Query Store, both manual and automatic, and I’ve been asked about it several times. I finally took the time to detail the options here.
Automatic removal of data from Query Store is based on configuration, notably the
CLEANUP_POLICY (STALE_QUERY_THRESHOLD_DAYS), and
SIZE_BASED_CLEANUP_MODE settings. While these are detailed in another post, it’s worth reiterating that ideally, size-based cleanup never kicks in. A database’s Query Store should be sized to accommodate N days’ worth of data, where N is set by
CLEANUP_POLICY (STALE_QUERY_THRESHOLD_DAYS). When the size of the Query Store exceeds 90% of
MAX_STORAGE_SIZE_MB, and if
SIZE_BASED_CLEANUP_MODE is set to
AUTO, then clean up will kick in. This algorithm is not efficient, and it runs single-threaded. It looks for queries that are infrequently executed/less important and deletes those, one by one, until the size is less than 80% of
MAX_STORAGE_SIZE_MB. Avoid this type of cleanup if at all possible.
Automatic removal also occurs when size-based cleanup kicks in. This process removes data based on date (anything older than the
STALE_QUERY_THRESHOLD_DAYS value is removed), and while it is more efficient than the aforementioned method, it could be improved.
In these two scenarios, forced plans (and their queries) are not removed from Query Store.
Data can be removed manually using one of three methods:
EXEC sp_query_store_remove_query @query_id = ZZZ; EXEC sp_query_store_remove_plan @plan_id = YYY; EXEC sp_query_store_reset_exec_stats @plan_id = YYY; ALTER DATABASE [DBName] SET QUERY_STORE CLEAR;
The sp_query_store_remove_query procedure removes all information (query, plan, runtime statistics) for a query_id. The sp_query_store_remove_plan procedure removes the plan and runtime statistics for a specific plan_id. The third procedure, sp_query_store_reset_exec_stats, removes runtime statistics for the specified plan_id. The ALTER DATABASE command removes all data from Query Store; it truncates the tables that contain Query Store data, and re-seeds the identity columns (e.g. query_id, plan_id).
If you have a plan that is forced, it cannot be removed with sp_query_store_remove_plan. If you have a query with a forced plan, the query (and related data) cannot be removed with sp_query_store_remove_query.
However, if you use the ALTER DATABASE command to clear the Query Store data, everything is removed, forced plans and all.
If you need to remove data from Query Store on a regular basis, and want to automate it, you have another option. The team over at Channel Advisor has created a set of scripts for Query Store, named qdstoolbox, that you can download from GitHub. Within these scripts is a stored procedure for cleanup (QDSCacheCleanup), which relies on the stored procedures mentioned above, but provides additional flexibility via input parameters. For example, you can delete queries and plans that have not executed in the last N number of hours, internal SQL Server queries (e.g.
UPDATE STATISTICS) which are captured in Query Store, or ad hoc queries that are not part of any stored procedure. As an added benefit, the procedure can display or save a report that details what was removed.