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:
| Vikas Rana | Twitter | Linkedin | Escalation EngineerMicrosoft IGTSC |