SQL SERVER – Unable to Disable Query Store for Always On Database

One of my clients enabled query store features to perform some troubleshooting by themselves. Since they were not able to find the cause and fix the issue, the contacted me via my famous service Comprehensive Database Performance Health Check. I was able to fix the issue for them and later they wanted to disable query store feature. They contacted me again not to fix perf issues but because they were not able to disable query store features for their database. In this blog, I would share my finding of cause of unable to disable Query Store for Always On Database.

Here is the command which they executed

ALTER DATABASE [PROD_DB]
SET QUERY_STORE = OFF

This query was getting blocked with background threads. These were the values in the command column in sys.dm_exec_requests for those threads.

• QUERY STORE APRC CHECK
• QUERY STORE BACKAGOUND FLUSH DB
• QUERY STORE ASYNC FLUSH

Here was their setting of query store for this database.

I then check “DBCC OPENTRAN” for this database and found one transaction. This SPID was the same SPID which was a background task doing “QUERY STORE BACKAGOUND FLUSH DB”

Transaction information for database ‘PROD_DB’.
Oldest active transaction:
SPID (server process ID): 54s
UID (user ID) : -1
Name : QDS nested transaction
LSN : (1302919:41223:132)
Start time : Mar 26 2020 1:18:49:863PM
SID : 0x0
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I suspected that this was due to QDS processing its async captures.

WORKAROUND/SOLUTION – Disable Query Store

I searched for Microsoft documentation which might help in query store data flush. I found trace flags about the query store. They are 7745 and 7752.

Here were the steps are done to fix the issue for them.

1. Enabled both trace flags as startup parameters on primary and secondary. No restart yet.
2. Restarted SQL Service on Node2 (secondary)
3. Performed failover of availability group from Node1 to Node2.
4. Executed ALTER command to disable Query Store.
5. Restart SQL Service on Node1.
6. Failed back availability group to Node1 (they wanted AG is run on this node)

By following the above steps, we were able to disable the query store for the database. Have you encountered such a situation? Please comment and share the steps which you have followed.

Reference: Pinal Dave (https://blog.sqlauthority.com)