Have you ever tried to turn off Query Store when there was an issue, and you thought the problem might be related to Query Store, and the ALTER DATABASE statement was blocked? And then you couldn’t do anything but wait? Me too. Imagine my excitement when I discovered that the SQL Server team snuck a helpful back door into ALL versions for which Query Store is supported.
Wait, what?
If you check the documentation for ALTER DATABASE SET and review the QUERY_STORE section, you’ll see that OFF now includes the optional FORCED parameter.
If you are running:
- SQL Server 2016 SP2 CU14+
- SQL Server 2017 C21+
- or SQL Server 2019 CU6+
you have the ability to forcibly disable Query Store, even if it’s in the middle of something like flushing data to disk or purging data. The FORCED option stops all related background tasks that are currently running, and skips any flushing that would occur (to persist data that was in memory to disk). Essentially, Query Store is shut down as quickly as possible.
How to disable Query Store
Typically, if you wanted to turn off Query Store, you would run:
ALTER DATABASE [DBName] SET QUERY_STORE = OFF
In working with a customer who was running SQL Server 2017, their 100GB Query Store took almost 45 minutes to load (side note: that is way beyond the recommended 10GB maximum). When we tried to run the statement above to disable Query Store, it was blocked by a thread with a QDS_LOAD wait type (loading the data into memory), because Trace Flag 7752 was not enabled.
For this scenario, or any other where we want this feature turned off immediately, we can now run:
ALTER DATABASE [DBName] SET QUERY_STORE = OFF (FORCED)
Parting thoughts
Be aware that if you forcibly turn off Query Store, any data that is in memory that has not been flushed to disk will be lost. Depending on the issue, that data may have been helpful when troubleshooting.
I don’t expect this option to be used frequently in SQL Server 2019 because of numerous improvements and the new CUSTOM capture mode (and because Trace Flag 7752 functionality is enabled by default). But I think this option will be helpful for anyone running SQL Server 2016 or SQL Server 2017 who might not have their CAPTURE_MODE properly set, or who might have a high-volume ad hoc workload that isn’t suitable for Query Store unless the CUSTOM capture mode can be used.
5 thoughts on “How to Turn Off Query Store…in an emergency”
Thanks! Having watched procedure cache go on a starvation diet while query store bloated to 30 GB beyond its max size, after a new scalar function and other new code was called a bazillion times, I could have used FORCE. And 2019 would also have helped. Better late than never!
I can see “Setting database option query_store to read_write for database ‘MYDB’.” in the SQL Error Log. However, filtering on “query_store” does not show up any explicit SET QUERY_STORE = OFF, and there’s no reason why anyone would want to do this. It HAS been turned OFF on 2 consecutive days, and the SQL Log is bereft of help, it seems. What OTHER ways are there that could be nuking query_store? Or is the “OFF” logged without using the “query_store” string? Perplexed!
The only way to turn it OFF is manually (with the ALTER DB comment). It can go into a READ_ONLY state on its own, for a variety of reasons, but it will not automatically turn OFF. You could try tracking this with Extended Events, which I talk about at the end of this post: https://www.sqlskills.com/blogs/erin/are-changes-to-query-store-logged/
https://dba.stackexchange.com/questions/199023/database-corruption-querystore-internal-table – that turns QS OFF!
Saw this after replying…that’s a new one, haven’t seen corruption in the QS tables, but it’s possible just like corruption anywhere else. Thanks for sharing the solution!