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_checkstored procedure in the affected database. If runningsp_query_store_consistency_checkdoesn’t work, or if you’re using SQL Server 2016 (13.x), you need to clear the data by runningALTER 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.
SQL Undercover 
Thanks, happening to me. Any ideas to the best way to check for this automatically rather than polling the state? Is there a recommended triggering mechanism? Also to avoid a clear if not needed (we have pinned plans we want to keep), would you simply check the state after the proc run and if it’s good then assume query store is good, if not then clear? Finally, like anyone else, would like to know why it’s happening, haven’t figured it out. Thanks again
LikeLike
To monitor, I’ve simply set up an agent job to check the state every 5 minutes and email if it sees a problem. Ideally I’d set up a custom alert in whatever monitoring tool is hooked up to the instance but for this particular server for one reason or another I’m very limited in what I can do monitoring wise.
The server I’m on is 2016 so I haven’t got the luxury or anything other that a clear down to get it moving again but if you can, I’d say run sp_query_store_consistency_check and see if that works for you first.
I’ve not found the cause for my issues, there are a number of vague mutterings around bugs in query store that can cause problems but not a lot of detail and nothing that really fits my symptoms.
LikeLike