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.
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)
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.