Query Store Keeps Switching Itself Off, Getting it Moving Again

I recently received a complaint that Query Store for a particular database was turned off, which was strange as that particular database has seen quite a few performance issues and I know that I’d ensured Query Store was enabled in the past.

No problem, I flicked the switch and Query Store was enabled again.

Half an hour or so later and I’m being told that Query Store is again disabled. What’s going on? My first thought was that someone’s fiddling with things. I asked around but everyone denied touching it. So my next ida was to hit up sys.database_query_store_options .

SELECT actual_state_desc
FROM sys.database_query_store_options

The results came back…

I tried switching it back on again but a few minutes later, off it went and the actual state was back to ‘ERROR’. There was nothing in the error logs to indicate a problem and a search online didn’t really give any answers other then vague mentions of bugs in SQL versions that didn’t apply to me. All I could find in MS documentation was;

In extreme scenarios Query Store can enter an ERROR state because of internal errors. In SQL Server 2017 (14.x) and later versions, if this happens, Query Store can be recovered by executing the sp_query_store_consistency_check stored procedure in the affected database. If running sp_query_store_consistency_check doesn’t work, or if you’re using SQL Server 2016 (13.x), you need to clear the data by running ALTER DATABASE [YourDatabaseName] SET QUERY_STORE CLEAR ALL;

To Get Query Store Running Again

So, while I don’t have an answer to what is exactly happening here and causing things to error, there does seem to be a way to get things going again. If you’re running SQL2017 or above, you an try the following;

EXEC dbo.sp_query_store_consistency_check
ALTER DATABASE <database name> SET QUERY_STORE = ON
ALTER DATABASE <database name> SET QUERY_STORE (OPERATION_MODE = READ_WRITE)

Unfortuntaly this particular server is an old SQL2016 box so I didn’t have sp_query_store_consistency_check available to me. My only option was to clear down the Query Store data and reenable. Be aware, running SET QUERY_STORE CLEAR ALL will clear out all your Query Store data, delete any forced plans and reset all identify columns.

ALTER DATABASE <database name> SET QUERY_STORE CLEAR ALL
ALTER DATABASE <database name> SET QUERY_STORE = ON
ALTER DATABASE <database name> SET QUERY_STORE (OPERATION_MODE = READ_WRITE)

For me, that seems to have done the trick. I’m now nearly a week along and Query Store is still running happily.

Thanks for reading and if you find yourself in a similar situation I hope this helps you get going again, even if I can’t explain exactly why this failure was happening in the first place.