Query Store captures query execution history, plans, and runtime statistics over time. If you need to stop this data collection (whether to reduce overhead, troubleshoot issues, or prepare for maintenance) you can disable Query Store on a per-database basis.
To disable Query Store on a database, use the ALTER DATABASE command:
ALTER DATABASE YourDatabaseName
SET QUERY_STORE = OFF;Replace YourDatabaseName with the name of your database. This immediately stops Query Store from capturing new query data.
What Happens When You Disable Query Store
Disabling Query Store stops all data collection, but it doesn’t delete anything. All previously captured queries, execution plans, and runtime statistics remain in the database. Your configuration settings are also preserved.
If you later re-enable Query Store with SET QUERY_STORE = ON, it will resume using the same configuration settings you had before disabling it (unless of course, you specify new configuration settings). The historical data will still be available for analysis.
Clearing Query Store Data
If you want to remove all captured data while keeping Query Store enabled:
ALTER DATABASE YourDatabaseName
SET QUERY_STORE CLEAR ALL;This deletes all queries, plans, and statistics but preserves your configuration settings. Query Store continues capturing new data immediately after the clear operation completes.
Forcing Query Store to Disable
In rare cases, you might need to force Query Store to disable even if there are active queries using forced plans:
ALTER DATABASE YourDatabaseName
SET QUERY_STORE = OFF (FORCED);The FORCED option aborts all running Query Store background tasks, and skips the synchronous flush when Query Store is turned off. It disables Query Store immediately. Use this only when the standard disable command fails due to dependencies.
Verifying Query Store is Disabled
To confirm Query Store is disabled:
SELECT actual_state_desc
FROM sys.database_query_store_options;If actual_state_desc shows OFF, Query Store is disabled and not capturing data.
Here’s an example of the actual output from that query:
actual_state_desc
-----------------
OFF
This confirms that Query Store has been disabled on the database.