Checking if Query Store is Enabled in SQL Server

This post shares the simple steps and SQL queries to check if Query Store is enabled in SQL Server, and view its options for a database.

The Query Store feature was introduced in SQL Server 2016 (13.x) and enabled by default in SQL Server 2022 (16.x). It helps DBAs and developers identify and resolve query performance issues, and its easy to setup.

1. Check if Query Store is Enabled

To check if Query Store is enabled for all databases, query the sys.databases system table and review the is_query_store_on column. This is a table I query very often as a SQL Server Database Administrator to check database properties.

-- Check if query store is enabled for all dbs
SELECT is_query_store_on, *
FROM sys.databases;

Alternatively, in SSMS, you can right-click the database, select Properties, and go to the Query Store tab:

2. Check Query Store Options

To view the Query Store options for a database, we can query the Query Store Catalog Views:

-- View Query Store Options for a database  
SELECT *  
FROM sys.database_query_store_options WITH (NOLOCK) OPTION (RECOMPILE);  

Query Store State Descriptions:
> OFF (0): Not enabled.
> READ_ONLY (1): Query Store is in read-only mode (quota exceeded).
> READ_WRITE (2): Query Store is capturing all queries.
> ERROR (3): Query Store is in an error state. To recover, disable and re-enable Query Store:

ALTER DATABASE [YourDatabase] SET QUERY_STORE = OFF;  
ALTER DATABASE [YourDatabase] SET QUERY_STORE = ON;  

I say recover from, as it’s easy to enable and is enabled by default as of SQL Server 2022, as mentioned at the top of this post.

When using Query Store, review Microsoft’s Best Practices for Monitoring Workloads with Query Store to ensure optimal performance. If you found this useful, check out my other posts for more SQL Server tips!