QDS_LOADDB Wait: Slow Startup and Query Store Load

A QDS_LOADDB wait means a database waits for Query Store to load its data from disk at startup. On a large store, that wait can keep queries from running for a long time.

A Client With a Slow Startup

A client ran SQL Server 2017 with a huge database and a heavy workload. After every restart, failover or restart of the service, that database took a long time to come online. Users could not run queries while they waited. A wait statistics script showed a high QDS_LOADDB wait.

The database had Query Store on. The client turned it off, and the delay disappeared. They turned it on again, and the delay returned. That made the cause clear. Query Store loaded all its data in one block before it let queries run.

Read the Wait

The wait statistics view counts every wait since the last restart. One row belongs to this wait type. The average column divides the total by the number of waits. A store that loads quickly gives an average near a millisecond. A store that holds up a database gives an average of seconds.

SELECT wait_type, waiting_tasks_count, wait_time_ms,
       CONVERT(decimal(9,2), wait_time_ms * 1.0 / NULLIF(waiting_tasks_count, 0)) AS AvgMs
FROM sys.dm_os_wait_stats
WHERE wait_type = N'QDS_LOADDB';

This is one reading from a test instance. Its stores are tiny, so 348 loads cost 361 ms in total. Your numbers will differ. Reading the view needs the VIEW SERVER STATE permission, or VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later. A QDS_LOADDB wait with an average of seconds, or one that grows after every restart, is the case that matters. On SQL Server 2019 and later a small total is normal. Each Query Store database adds a wait at startup.

See a Store and Its Size

The time to load depends on the amount of data. The demo creates a database named QueryStoreLoadDemo and turns Query Store on. A loop runs 300 queries. Each one has a different comment, so Query Store records 300 different queries. Then the script flushes the store to disk.

IF DB_ID(N'QueryStoreLoadDemo') IS NULL CREATE DATABASE QueryStoreLoadDemo;
GO
ALTER DATABASE QueryStoreLoadDemo SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = ALL);
GO
USE QueryStoreLoadDemo;
GO
SET NOCOUNT ON;
DROP TABLE IF EXISTS dbo.Teas;
CREATE TABLE dbo.Teas (TeaID int NOT NULL PRIMARY KEY, TeaName varchar(40) NOT NULL);
INSERT dbo.Teas VALUES (1, 'oolong'), (2, 'assam'), (3, 'nilgiri');
DECLARE @i int = 0, @sql nvarchar(300);
WHILE @i < 300
BEGIN
    SET @sql = N'DECLARE @x int; SELECT @x = TeaID FROM dbo.Teas WHERE TeaName = ''assam'' /* marker ' + CONVERT(nvarchar(10), @i) + N' */;';
    EXEC sys.sp_executesql @sql;
    SET @i += 1;
END;
EXEC sys.sp_query_store_flush_db;

The options view shows the state, the space in use and the limit. A store that sits near its limit is the one that loads slowly.

SELECT actual_state_desc AS State, current_storage_size_mb AS UsedMB, max_storage_size_mb AS MaxMB,
       query_capture_mode_desc AS CaptureMode, stale_query_threshold_days AS StaleDays
FROM sys.database_query_store_options;

The store holds 1 MB of a 1,000 MB limit. A production store that holds several hundred megabytes is the kind that makes a startup wait. Now take the database offline and bring it back. The two readings of the wait counters come from the same view as before.

USE master;
GO
SELECT wait_type, waiting_tasks_count, wait_time_ms FROM sys.dm_os_wait_stats WHERE wait_type = N'QDS_LOADDB';
ALTER DATABASE QueryStoreLoadDemo SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE QueryStoreLoadDemo SET ONLINE;
SELECT wait_type, waiting_tasks_count, wait_time_ms FROM sys.dm_os_wait_stats WHERE wait_type = N'QDS_LOADDB';

In the demo run, both readings were the same, 349 waits and 361 ms. The counters had risen by one wait while the demo store was created. The offline and online round trip of a store of 1 MB added none. That fits the documented change to an asynchronous load. The wait matters for a large store on SQL Server 2016 and 2017.

Load Query Store Asynchronously

On SQL Server 2016 and 2017, on a current build, trace flag 7752 turns on the asynchronous load. Queries then run while Query Store loads in the background. SQL Server 2019 and later do this by default, so a flag is not needed there. A reader of the old post corrected its flag number. The right one is 7752. The flag 7745 does something else. It stops Query Store from writing the data still in memory to disk. That happens when the database shuts down or fails over.

The next query shows the state of both flags. On the test instance both are off. A status of 1 in the Global column means the flag is on for the whole server.

DBCC TRACESTATUS (7752, 7745, -1);

The statement below turns the flag on for a running server. It changes a server setting, so the demo does not run it. The flag lasts until the next restart. For a permanent change, add -T7752 to the startup parameters in SQL Server Configuration Manager. Try it on a test server first.

-- Turn the flag on until the next restart:
DBCC TRACEON (7752, -1);
-- Undo, until the next restart:
-- DBCC TRACEOFF (7752, -1);
-- For a permanent change, add -T7752 to the startup parameters, and remove it to undo.

Keep the Store Small

A smaller store loads faster on any version. Three settings do the work. Capture mode AUTO skips queries that are cheap or rare. A lower size limit stops growth. A shorter stale threshold removes old queries sooner. The statement below applies all three to the demo database, and the query after it reads them back.

ALTER DATABASE QueryStoreLoadDemo SET QUERY_STORE (
    QUERY_CAPTURE_MODE = AUTO, MAX_STORAGE_SIZE_MB = 200, SIZE_BASED_CLEANUP_MODE = AUTO,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30));
GO
USE QueryStoreLoadDemo;
GO
SELECT actual_state_desc AS State, max_storage_size_mb AS MaxMB, query_capture_mode_desc AS CaptureMode,
       stale_query_threshold_days AS StaleDays, size_based_cleanup_mode_desc AS SizeCleanup
FROM sys.database_query_store_options;

The stale threshold was already 30 days in this demo. A server that keeps a year of history has more to change. Do not set the limit below the size in use. Check State in the options view afterwards.

Should You Turn Query Store Off?

You could argue that turning Query Store off is the fix. The client’s test shows it works. It also throws away the history that makes Query Store useful. An asynchronous load or a smaller store keeps the history and removes the wait.

What to Remember

A high QDS_LOADDB wait means a slow Query Store load at startup. Read the wait and check the size of the store. On SQL Server 2016 and 2017, load it asynchronously with trace flag 7752. Keep the store small on every version.

When you finish the demo, remove the database.

USE master;
GO
IF DB_ID(N'QueryStoreLoadDemo') IS NOT NULL
BEGIN
    ALTER DATABASE QueryStoreLoadDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE QueryStoreLoadDemo;
END;

A wait type is not a bug, it is a queue you can finally see.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.