sp_QueryStoreCleanup | Darling Data

sp_QueryStoreCleanup – Query Store Noise Removal Tool

Welcome to the home of sp_QueryStoreCleanup, a stored procedure for identifying and removing duplicate and noisy queries from SQL Server Query Store.

Query Store is great, but it collects a lot of noise. System DMV queries, index maintenance, statistics updates, and other background operations all pile up as duplicate entries, wasting space and making it harder to find the queries you actually care about.

This procedure uses text pattern matching and hash-based deduplication to find the junk, and removes it using sp_query_store_remove_query. Queries with forced plans are always protected from removal.

View the full README on GitHub

Overview

sp_QueryStoreCleanup finds and removes duplicate, noisy, and unwanted queries from Query Store in any database on your server.

By default, it targets system queries (FROM sys.%) and maintenance operations (index rebuilds, statistics updates, DBCC commands, etc.), then finds all copies with duplicated query or plan hashes and removes them.

You can customize every aspect of the cleanup:

What to target (system queries, maintenance operations, custom patterns, or everything)
How to deduplicate (by query hash, plan hash, both, or skip deduplication entirely)
Whether to actually remove or just report what would be removed
Age-based filtering to only remove queries that haven’t executed recently

Cleanup Targets

The @cleanup_targets parameter controls which queries are identified by text pattern matching:

system – Queries containing FROM sys.%
maintenance (or maint) – Index operations (ALTER INDEX, CREATE INDEX, ALTER TABLE), statistics operations (UPDATE STATISTICS, CREATE STATISTICS, SELECT StatMan), DBCC commands, and parameterized maintenance queries (@_msparam)
custom – Uses your @custom_query_filter LIKE pattern
all – system + maintenance combined; also applies @custom_query_filter if provided
none – No text filtering; deduplication is purely hash-based across all queries

The maintenance patterns are sourced from sp_QuickieStore, which uses them to filter out background noise from Query Store results.

Deduplication Strategy

The @dedupe_by parameter controls how duplicates are identified after text filtering:

query_hash – Find queries with duplicate query_hash values (same query compiled multiple times)
plan_hash – Find queries with duplicate query_plan_hash values (different queries producing identical plans)
all – Both query_hash and plan_hash
none – Skip hash deduplication entirely; send all text-matched queries directly to removal

Hash deduplication removes all copies of duplicated hashes, not all-but-one. This is intentional, as the queries targeted are noise that will be recaptured by Query Store if they execute again.

Parameters

sp_QueryStoreCleanup offers the following parameters:

@database_name – The database to clean Query Store in (default: current database)
@cleanup_targets – What to target: all, system, maintenance (or maint), custom, none (default: all)
@custom_query_filter – Custom LIKE pattern for query text filtering; also applied when @cleanup_targets = 'all'
@dedupe_by – Deduplication strategy: all, query_hash, plan_hash, none (default: all)
@min_age_days – Only remove queries whose last execution is older than this many days (default: NULL, no age filter)
@report_only – Report what would be removed without removing (default: 0)
@debug – Prints dynamic SQL and diagnostics (default: 0)
@help – Displays help information (default: 0)
@version – OUTPUT; for support
@version_date – OUTPUT; for support

Usage Examples

Here are some common usage examples for sp_QueryStoreCleanup:

-- Default: remove all system + maintenance duplicates from the current database
EXECUTE dbo.sp_QueryStoreCleanup;

-- Target a specific database
EXECUTE dbo.sp_QueryStoreCleanup
    @database_name = N'StackOverflow2013';

-- Report what would be removed without removing anything
EXECUTE dbo.sp_QueryStoreCleanup
    @database_name = N'YourDatabase',
    @report_only = 1;

-- Only clean up system DMV queries
EXECUTE dbo.sp_QueryStoreCleanup
    @database_name = N'YourDatabase',
    @cleanup_targets = 'system';

-- Only clean up maintenance operations
EXECUTE dbo.sp_QueryStoreCleanup
    @database_name = N'YourDatabase',
    @cleanup_targets = 'maint';

-- Remove all text-matched queries without hash deduplication
EXECUTE dbo.sp_QueryStoreCleanup
    @database_name = N'YourDatabase',
    @cleanup_targets = 'system',
    @dedupe_by = 'none';

-- Use a custom text filter
EXECUTE dbo.sp_QueryStoreCleanup
    @database_name = N'YourDatabase',
    @cleanup_targets = 'custom',
    @custom_query_filter = N'%some_noisy_query%';

-- Only remove queries not executed in the last 30 days
EXECUTE dbo.sp_QueryStoreCleanup
    @database_name = N'YourDatabase',
    @min_age_days = 30;

-- Debug mode to see the generated dynamic SQL
EXECUTE dbo.sp_QueryStoreCleanup
    @database_name = N'YourDatabase',
    @debug = 1;

Safety Features

sp_QueryStoreCleanup includes several safety features to prevent accidental data loss:

Forced plan protection – Queries with forced plans are always excluded from removal, regardless of other filters
Report-only mode – Use @report_only = 1 to preview what would be removed before committing
Age filtering – Use @min_age_days to only remove queries that haven’t been executed recently
Progress output – Real-time progress messages show each removal attempt with success or failure and error details
Error resilience – Individual removal failures don’t stop the process; errors are captured and reported
Query Store state validation – The procedure validates that Query Store is enabled on the target database before proceeding

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.