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.
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.
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)
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.
@Sharaf Enable RCSI!
Hi Pinal! Thanks for your explanation. But I think, you mention wrong trace flag: for asynchronous load data into Query Store use trace flag 7752, not 7745 (bypass writing any Query Store data still in memory to disk).