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.
- Enabled both trace flags as startup parameters on primary and secondary. No restart yet.
- Restarted SQL Service on Node2 (secondary)
- Performed failover of availability group from Node1 to Node2.
- Executed ALTER command to disable Query Store.
- Restart SQL Service on Node1.
- 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)
We have encountered same problem. We were not able to run any alter statements on query store, Transaction log grew beyond 300gb, secondary replica wasn’t catching up, I had to remove database from Availability group , added trace flags 7745 and 7752, changed database to simple mode, took full backup of the effected database (at this point tlog is still over 300gb not able to shrink it) and restarted the instance. After Restart effected database stayed in recovery for few minutes and became online. Then I was able to turn off Query store. My query store size grew up to 1.48 GB even though I set Max size to 300 MB, don’t understand why it grew beyond 300MB.
Finally I have cleared old query store data.
I suggest anyone trying to enable query store, please apply latest CU per Microsoft suggestion.
These trace flags are important for use with Query Store. Thanks for highlighting them. Although for disabling Query Store you might also try the command
ALTER DATABASE [DBName] SET QUERY_STORE = OFF (FORCED)