Sql-server – Undocumented Wait Type QDS Loaddb and an unusable database

sql server

Has anyone ever encountered the wait type QDS_Loaddb? I have found zero documentation for this wait. The description on the microsoft site says TBD.

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-wait-stats-transact-sql

I have an unusable database that shows QDS_Loaddb waits any time a query is run. It doesn't matter if the query is against system tables or user tables. The wait is the same QDS_Loaddb.

If I knew what this wait meant, I could have a shot at fixing it.

Best Answer

Has anyone ever encountered the wait type QDS_Loaddb?

I have not, however I did some digging.

I have found zero documentation for this wait. The description on the microsoft site says TBD.

Disclaimer: This is not meant to be an official documentation or replace any documentation that the official team publishes. It may or may not contain errors and is not meant to be an official source.

QDS_LoadDB is a wait type, associated with (you guessed it) Query Store. This is the wait type that will be encountered when Query Store is starting up for a specific database. Once startup is finished the wait should go away.

While query store is starting up, many different things happen... one of these seems to block queries against the database. Eventually, when startup is completed, those queries will be signaled and would continue to execute.

Please note that the above is a simplification of many processes and is not meant to be an exhaustive look at Query Store components.

I have an unusable database that shows QDS_Loaddb waits any time a query is run. It doesn't matter if the query is against system tables or user tables. The wait is the same QDS_Loaddb.

Part of this I explain above but let me go a little further.

First, thank you for the comments explaining a few things about what you did and the environment. Sadly, since this was production, immediate and drastic measures were taken. Also, there was no hint of any errors in the errorlog, which is odd.

What I believe happened is the database was brought online and Query Store was initialized. There was a failure of some sort (which we won't know now, why) on the initialization of Query Store which was suppressed from the errorlog (again, no idea why). Since the initialization fails, the queries are never signaled and thus you get into the state you are in. Again, this is my assumption of what happened based on what I looked into.

The interesting item is that there should be a timeout period, where if Query Store isn't loaded within said timeout - everything should just continue on and not wait any longer. You should have easily hit this timeout - which makes we not exactly sure this is what happened... again, though, not too much to go on.

There are a few extended events that we can monitor to see if this truly was the case or not.

  • query_store_load_started
  • query_store_loaded
  • query_store_database_initialization_failed
  • query_store_catch_exception
  • query_store_unloaded

So what is the fix?

Unfortunately it is very hard to say why it happened - and even if my assumption was correct, we'd need to know what error was being swallowed. Since we have neither of those, and just a wait type, I'm afraid the answer is "I don't know".

Having said that, since it has to deal with Query Store (and if my assumption is correct) then turning off Query Store - without digging into the issue further - would be the only good route to go.

Disclaimer: I work for Microsoft

If you run into this again (or anyone for that matter) I would highly advise you open up a support case with Microsoft.

Related Question