If you are trying to execute an ALTER DATABASE command to change a Query Store option (e.g. turn it off, change a setting) and it is blocked, take note of the blocking session_id and what that session_id is executing. If you are trying to execute this ALTER command right after a failover or restart, you are probably blocked by the Query Store data loading.
As a reminder, when a database with Query Store enabled starts up, it loads data from the Query Store internal tables into memory (this is an optimization to make specific capabilities of Query Store complete quickly). In some cases this is a small amount of data, in other cases, it’s larger (potentially a few GB), and as such, it can take seconds or minutes to load. I have seen this take over 30 minutes to load for a very large Query Store (over 50GB in size).
Specifically, I was recently working with a customer with an extremely large Query Store. The customer had enabled Trace Flag 7752, which I have written about, so that queries were not blocked while Query Store loaded asynchronously. The tricky thing about that load is that there is no way to monitor the progress. You can track when it starts loading and then when it finishes using Extended Events, but there is no progress bar to stare at a on a screen. When trying to execute an ALTER DATABASE <dbname> SET QUERY_STORE statement while the load was occurring, the statement was blocked by a system session that was running the command Query Store ASYN. The ALTER DATABASE <dbname> SET QUERY_STORE command did complete once the Query Store data had been loaded.
If you do not have Trace Flag 7752 enabled, then if you try to execute ALTER DATABASE <dbname> SET QUERY_STORE after a restart or failover you might see the QDS_LOADDB wait_type for queries (again, this will depend the size of the Query Store). Again, there is no way to monitor the load, and you will see the same behavior if you try to run ALTER DATABASE <dbname> SET QUERY_STORE: the command will not complete until the Query Store load has completed.
In summary, regardless of whether the Query Store data is loading synchronously or asynchronously, you will not be able to execute an ALTER DATABASE <dbname> SET QUERY_STORE statement until the load is complete.
4 thoughts on “ALTER DATABASE SET QUERY_STORE command is blocked”
Small comment, although enabling TF 7752 enabled async QDS loading, we still faced an issue when the AG was unresponsive for quite a lot of time, and QDS was the one to blame for it.
99% of the waits we had were QDS_BCKG_TASK (After enablding the ASYNC TF 7752, previously it was QDS_LOADDB).
we had to switch to single user in order to let QueryStore finish with its initiation.
in other words, for very large Query store, even by enabling the Async TF, it doesnt really help.
Microsoft has confirmed that its a product bug and claimed that they`ll release a fix for that on 14-Feb (CU-14), unfortunately they have probably postponed the release.
Boris-
Right, to confirm, this problem was due to a software defect, it was *not* expected behavior. It is expected that normally, using TF 7752 will not cause a problem – with or without an AG. Thanks for the note!
Erin