SQL Server’s Query Store can capture wait statistics that show what queries are waiting on during execution. This includes things like locks, I/O, CPU, memory, and other resources. This can help you understand not just that a query is slow, but why it’s slow. Wait statistics are available in SQL Server 2017 and later.
Finding the Query ID
To get the wait statistics of a given query, you’ll need the query ID. You can use the following query to get that:
SELECT
q.query_id,
qt.query_sql_text,
q.last_execution_time
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE qt.query_sql_text LIKE '%your_table_name%'
ORDER BY q.last_execution_time DESC;In this example I used WHERE qt.query_sql_text LIKE '%your_table_name%', but you’ll need to replace your_table_name with text or a table name that you know is in the query. You’ll need to adjust the filter if it returns too many results.
Once you identify the right query, you can use its query_id in the following queries.
Viewing Wait Statistics
Once you’ve got the query ID, you can use the following query to view wait statistics for that query:
DECLARE @query_id INT = 123; -- Replace with your query_id
SELECT
ws.wait_category_desc,
SUM(ws.total_query_wait_time_ms) AS total_wait_time_ms,
AVG(ws.avg_query_wait_time_ms) AS avg_wait_time_ms,
MAX(ws.max_query_wait_time_ms) AS max_wait_time_ms
FROM sys.query_store_plan p
JOIN sys.query_store_wait_stats ws ON p.plan_id = ws.plan_id
WHERE p.query_id = @query_id
GROUP BY ws.wait_category_desc
ORDER BY total_wait_time_ms DESC;This shows which wait categories are consuming the most time for the query, helping you identify the primary bottleneck.
Here’s an example of output from this query:
wait_category_desc total_wait_time_ms avg_wait_time_ms max_wait_time_ms
------------------ ------------------ ---------------- ----------------
Parallelism 643 160.75 310
Preemptive 6 1.2 2
CPU 3 1.5 2
Memory 1 1 1
Finding Queries with the Most Wait Time
To identify which queries have the highest total wait time across your database:
SELECT TOP 20
q.query_id,
LEFT(qt.query_sql_text, 100) + '...' AS query_preview,
ws.wait_category_desc,
SUM(ws.total_query_wait_time_ms) AS total_wait_ms
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_wait_stats ws ON p.plan_id = ws.plan_id
GROUP BY q.query_id, qt.query_sql_text, ws.wait_category_desc
ORDER BY total_wait_ms DESC;This helps you prioritize which queries to investigate based on their overall wait time impact.
Viewing Wait Statistics Over Time
To see how wait patterns change over time for a query:
DECLARE @query_id INT = 123;
SELECT
rsi.start_time,
rsi.end_time,
ws.wait_category_desc,
SUM(ws.total_query_wait_time_ms) AS total_wait_ms
FROM sys.query_store_plan p
JOIN sys.query_store_wait_stats ws ON p.plan_id = ws.plan_id
JOIN sys.query_store_runtime_stats_interval rsi ON ws.runtime_stats_interval_id = rsi.runtime_stats_interval_id
WHERE p.query_id = @query_id
GROUP BY rsi.start_time, rsi.end_time, ws.wait_category_desc
ORDER BY rsi.start_time, total_wait_ms DESC;This shows wait statistics broken down by Query Store’s time intervals, helping you identify when specific wait types became problematic.
Comparing Wait Statistics Across Plans
When a query has multiple plans, you can compare their wait characteristics:
DECLARE @query_id INT = 123;
SELECT
p.plan_id,
ws.wait_category_desc,
SUM(ws.total_query_wait_time_ms) AS total_wait_ms,
AVG(ws.avg_query_wait_time_ms) AS avg_wait_ms
FROM sys.query_store_plan p
JOIN sys.query_store_wait_stats ws ON p.plan_id = ws.plan_id
WHERE p.query_id = @query_id
GROUP BY p.plan_id, ws.wait_category_desc
ORDER BY p.plan_id, total_wait_ms DESC;Different plans may exhibit different wait patterns. A plan with high Lock waits might perform better if changed to one with more CPU waits, indicating less blocking.
Here’s an example of output from this query:
plan_id wait_category_desc total_wait_ms avg_wait_ms
------- ------------------ ------------- -----------
29 CPU 1 1
31 Parallelism 310 310
31 Memory 1 1
31 Preemptive 1 1
33 Parallelism 117 117
33 Preemptive 1 1
34 Parallelism 120 120
34 Preemptive 2 2
35 CPU 2 2
35 Preemptive 1 1
36 Parallelism 96 96
36 Preemptive 1 1
Enabling Wait Statistics Capture
Wait statistics capture is controlled by the WAIT_STATS_CAPTURE_MODE setting. To verify it’s enabled:
SELECT wait_stats_capture_mode_desc
FROM sys.database_query_store_options;If it shows OFF, you can enable it by running this:
ALTER DATABASE YourDatabaseName
SET QUERY_STORE (WAIT_STATS_CAPTURE_MODE = ON);Note that wait statistics capture adds slight overhead compared to basic Query Store tracking, but the insights are usually worth it for troubleshooting performance issues.
Filtering by Specific Wait Types
You can focus on queries with specific wait types. For example, to find queries with significant lock waits:
SELECT TOP 20
q.query_id,
LEFT(qt.query_sql_text, 100) + '...' AS query_preview,
SUM(ws.total_query_wait_time_ms) AS total_lock_wait_ms
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_wait_stats ws ON p.plan_id = ws.plan_id
WHERE ws.wait_category_desc = 'Lock'
GROUP BY q.query_id, qt.query_sql_text
ORDER BY total_lock_wait_ms DESC;Replace 'Lock' with other wait categories to investigate different types of bottlenecks.
Understanding Wait Categories
Here are the categories that SQL Server groups waits into:
| Wait Type Category | Description |
| Unknown | Wait type doesn’t fit into other categories |
| CPU | Query is waiting for CPU time to become available |
| Worker Thread | Query is waiting for a worker thread to become available |
| Lock | Query is blocked by locks held by other transactions |
| Latch | Query is waiting on internal SQL Server synchronization objects |
| Buffer Latch | Query is waiting to access pages in the buffer pool |
| Buffer IO | Query is waiting for data pages to be read from disk into memory |
| Compilation | Query is waiting during query compilation or recompilation |
| SQL CLR | Query is waiting on CLR operations |
| Mirroring | Query is waiting on database mirroring operations |
| Transaction | Query is waiting on transaction-related operations |
| Idle | Query session is idle |
| Preemptive | Query is waiting on preemptive tasks |
| Service Broker | Query is waiting on Service Broker operations |
| Tran Log IO | Query is waiting for transaction log writes |
| Network IO | Query is waiting to send results to the client |
| Parallelism | Query is waiting on parallel query coordination |
| Memory | Query is waiting for memory grants |
| User Wait | Query is waiting on user-defined waits |
| Tracing | Query is waiting on tracing operations |
| Full Text Search | Query is waiting on full-text search operations |
| Other Disk IO | Query is waiting on disk I/O not covered by other categories |
| Replication | Query is waiting on replication operations |
| Log Rate Governor | Query is waiting due to log rate governance (Azure SQL Database) |
High waits in specific categories point to specific types of issues. For example, high Lock waits suggest blocking problems, while high Buffer IO waits indicate disk I/O bottlenecks.