SQL Server’s Query Store accumulates query execution history, plans, and runtime statistics over time. Eventually you may need to remove this data to free up space, start fresh after troubleshooting, or clear out information that’s no longer relevant. Fortunately, you can clear Query Store data without disabling the feature entirely.
To clear all Query Store data, use the ALTER DATABASE command along with SET QUERY_STORE CLEAR ALL:
ALTER DATABASE YourDatabaseName
SET QUERY_STORE CLEAR ALL;Replace YourDatabaseName with the name of your database. This immediately deletes all captured queries, execution plans, and runtime statistics.
What Gets Cleared
The CLEAR ALL operation removes:
- All query text and identifiers
- All execution plans
- All runtime statistics (duration, CPU time, reads, writes)
- All wait statistics
- All forced plan associations
Your Query Store configuration settings are preserved. Query Store remains enabled and immediately begins capturing new data after the clear operation completes.
Clearing Data for Specific Queries
If you only want to remove data for specific queries rather than everything, use the built-in stored procedures.
Remove a specific query and all its associated plans and statistics:
EXEC sp_query_store_remove_query @query_id = 123;Remove a specific execution plan:
EXEC sp_query_store_remove_plan @plan_id = 456;The sp_query_store_remove_query procedure deletes the query and all its associated plans, runtime statistics, and wait statistics. The sp_query_store_remove_plan procedure removes only a specific plan while leaving the query and other plans intact.
These procedures handle all the foreign key relationships automatically, making them much safer than manually deleting from the Query Store system views.
For most scenarios, using CLEAR ALL and letting Query Store rebuild its data is simpler.
When to Clear Query Store Data
Common reasons to clear Query Store data include:
- After major application changes when historical data is no longer relevant
- When Query Store has filled up and you want to start fresh rather than increasing storage limits
- After completing performance troubleshooting and you no longer need the historical comparison data
- When testing Query Store behavior and you want to reset to a clean state
Keep in mind that clearing Query Store data is permanent. There’s no way to recover the information once it’s deleted. If you might need the historical data later, consider exporting it to a permanent table before clearing.