Query Store Entering Error State

Extremely rarely Query Store can end up in ERROR state because of internal errors or due to race condition when failover/restart happens while QDS cleanup running.

Query Store can be recovered by executing sp_query_store_consistency_check stored procedure within the affected database.

Please remember if QDS in ERROR state in that case it may affect Automatic plan correction feature and may cause performance issue if you are using plan correction feature extensively.

For CRITICAL environment it’s very important to automatically detect this situation and mitigate immediately. You can consider creating a scheduled job to check the QDS status and take appropriate action based on actual state.

Here is the sample code we have created which you can test\customize based on your environment\requirement. As always please test following code in TEST\UAT environment before deploying on production.

If you would like to improve following T-SQL code then please update here:

https://github.com/vikasrana2006/Query-Store-Entering-Error-State/blob/master/QDS_ERROR_STATE_CORRECTION.sql

 

IF EXISTS (SELECT * FROM sys.database_query_store_options WHERE actual_state=3)
BEGIN
BEGIN TRY
ALTER DATABASE [QDS] SET QUERY_STORE = OFF
Exec [QDS].dbo.sp_query_store_consistency_check
ALTER DATABASE [QDS] SET QUERY_STORE = ON
ALTER DATABASE [QDS] SET QUERY_STORE (OPERATION_MODE = READ_WRITE)
END TRY
BEGIN CATCH
SELECT 
    ERROR_NUMBER() AS ErrorNumber 
    ,ERROR_SEVERITY() AS ErrorSeverity 
    ,ERROR_STATE() AS ErrorState 
    ,ERROR_PROCEDURE() AS ErrorProcedure 
    ,ERROR_LINE() AS ErrorLine 
    ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;  
END

 

 

Vikas Rana | Twitter | Linkedin | Escalation Engineer
Microsoft IGTSC