4

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.

  • Do you see anything abnormal in: 1) sys.databases for the status? 2) sys.master_files for any of the files? 3) Errorlog? – Sean Gallardy May 1 '17 at 17:36
  • This first started happening after a planned reboot last week and happened again this morning after a brief network outage. I was able to bring the db back online by going emergency-single user-repair with data loss and back to multi user. There was nothing in the error log aside from the usual database startup, log backup, etc. – BillBikesInCleveland May 1 '17 at 17:48
  • Until someone mentioned query store, I was at a loss as to why the database wasn't responding even as it was reporting no errors. It's a production database and with people asking when is it going to be back up, I had to do something. The only idea I had was to run a repair. – BillBikesInCleveland May 1 '17 at 18:14
6

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.

1

There's an issue with Query Store cleanup in Standard and Express Editions where automatic cleanup doesn't happen.

You have to do it manually:

ALTER DATABASE [DatabaseName] SET QUERY_STORE CLEAR;

You maybe running into that if you're not patched, and on one of those editions.

  • I'm starting to lean to the query store being the problem. It was not getting cleaned up, like the other databases on the server, and had grown to 20.2 GB. Additionally, the alter database command you have there isn't purging the data in this particular database's query store. – BillBikesInCleveland May 1 '17 at 18:05
  • @BillBikesInCleveland that's the command MS provides. You may want to open a ticket with them. – Erik Darling May 1 '17 at 18:12
0

I've encountered the same issue on SQL 2016 Enterprise (13.0.4422.0) after restoring a database that has Query Data Store (QDS) enabled. Although the size limit was set to 200MB, the query store had grown to 20GB+ which is probably a large part of the problem. As soon as I went to run my post-restore data obfuscation scripts, they got this wait type despite there being no other queries running on the server.

Although it took a little while since the server began to be very slow at this point, I was able to fix the issue by disabling query store:

USE [master]
GO
ALTER DATABASE [database] SET QUERY_STORE = OFF
GO

I'm going to keep looking into this though, as this is far from an ideal solution.

  • Interesting. I examined the query store for this database and it was full and was not releasing any space when I tried to purge the query store data. I've since turned it off and will keep it off until I can get the purge to work. – BillBikesInCleveland May 1 '17 at 17:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.