SQL SERVER – QDS_LOADDB Wait and Asynchronous Load of Query Store

Today I am going to share a very interesting behavior discovered during Comprehensive Database Performance Health Check. The client who has SQL Server 2017, recently was facing a very high wait type of QDS_LOADDB. This wait type is usually visible when there is an issue with a synchronous load of the query store. The way to fix it is to enable the Asynchronous Load of Query Store.

SQL SERVER - QDS_LOADDB Wait and Asynchronous Load of Query Store asynchronous-load-800x211

Real-World Scenario

Recently a regular client reached out to me with help with their server startup. Every single time whenever they were restarting the server, failing over or restarting the services they were facing a very long time for the database to come online and start responding to the user queries.

This was particularly happening for the database which was extremely large in terms of size. After spending some time looking into what is going on, I had sent him SQL Wait Statistics Script which identified that the server is facing problems with QDS_LOADDB wait statistics.

For the database where we were facing issues that had query store enabled as the database was large and the workload for the server is very heavy. We tried to disable the query store and the issue disappeared and whenever we enabled the query store once again the issue was back. From this experience, it was very clear that the issue was related to the query store.

QDS_LOADDB Wait

Whenever a SQL Server starts up a query store enabled database, query store loads all its data synchronously preventing other queries from running till its loading is complete. If you have large data for your query store, it will take some time before the entire query store data is loaded into the database. This is indeed not a desirable situation as database queries can’t be run till the query store synchronous load completes.

Workaround/Solution – Asynchronous Load

If you are using SQL Server 2019, you will have to do nothing as this problem has been resolved by default.

If you are using SQL Server 2017 or earlier version you can enable trace flag 7745 for your server and it will enable Asynchronous Load of Query Store. Once the trace flag is enabled it will not prevent queries to run till the query store loading is completed leading to faster database recovery.

You can always send me the output of your SQL Wait Statistics Script and I will be happy to give you my feedback.

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

, ,
Previous Post
SQL SERVER – Total Number of Partitions Created by Partition Function
Next Post
SQL SERVER – Queries Waiting for Memory Grant – Performance Tuning

Related Posts

2 Comments. Leave new

  • I have Application and when trying to login using 5 to 10 users my all SP’s performance is good under 1-2 Sec. but When we enabled 200 users and trying to use same API call from Application and hits number of same SP’s some SP execution is increase to 10 Sec. and some time also Timeout error as well as connection error.

    Reply
  • mssqlo6766Garam
    October 7, 2020 7:29 pm

    @Sharaf Enable RCSI!

    Reply

Leave a Reply

Menu