The Query Store feature was introduced in SQL Server 2016 (13.x), which helps DBAs & Developers identify and resolve performance issues related to queries. In this blog post, we’ll share a simple SQL query that will show if Query Store is enabled for all databases, and another that will show the Query Store Options for a db.
These SQL Scripts might be of particular use post migrating to SQL Server 2022 (16.x) or if using Azure SQL Databases, as Query Store is enabled by default for all new databases created on these versions. It is enabled by default in the READ_WRITE mode; I’ve seen issues with applications when dropping databases, I had to disable Query Store on the database for the app end to work.
For you, you may want to check if Query Store is enabled so you can enable it. If enabling, we should read the Microsoft Docs on Query Store, and check out the Query Store Catalog Views too.
1. How to Check if Query Store is Enabled
To check if Query Store is enabled for all databases, we can query the sys.databases table checking the is_query_store_on column. This system table is one a SQL DBA will commonly query to check various settings for the databases on a SQL Server Instance.
-- Check if query store is enabled for all dbs SELECT is_query_store_on, * FROM sys.databases;
Altenatively, to check if Query Store is enabled for a database in SSMS, we can right click, select Properties and go to the Query Store tab.
If you need to enable Query Store for a database you can use the Properties window above, or run ALTER DATABASE [Jupiter] SET QUERY_STORE = ON.
2. How to Check Query Store Options
To view Query Store Options for a database when it’s enabled, we can query the sys.database_query_store_options DMV. The MS Docs link provided contains documentation for all settings relating to this table.
-- Get Query Store Options for this 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 can run in read-only mode Query Store size exceeded the quota
READ_WRITE (2)
Query Store is enabled and capturing all queries.
ERROR (3)
Query Store is in Error State.. You may need to recover by disabling & enabling Query Store on the db by running ALTER DATABASE [Jupiter] SET QUERY_STORE = OFF/ON and clear the existing data.
Conclusion
One last thing while Query Store is on mind is the recommendation to review the Best Practices for Monitoring Workloads with Query Store by Microsoft. Also, for you to check out my other posts if you found this useful, ty!
Leave a Reply