Wait Statistics: What Is It Waiting On?

When elapsed time is much larger than CPU time, the query spent that gap waiting. SQL Server records what every waiting task is waiting for, so this question has a precise answer rather than a guess.

The mental model

A worker thread is in one of three states: running on a CPU, runnable (ready but queued for a CPU), or suspended (waiting for a resource). Every time a thread is suspended, the server increments a counter for the specific reason. Sum those counters and you have a ranked list of the things standing between your workload and its results.

Two columns matter beyond raw wait time:

• signal_wait_time_ms — time spent runnable after the resource arrived, waiting for a CPU. High signal waits as a share of the total indicates CPU pressure.
• resource wait (wait_time_ms - signal_wait_time_ms) — actual time waiting for the resource itself.

Session-scoped waits: cleaner than server-wide

Server-wide sys.dm_os_wait_stats is cumulative since startup and includes every workload on the instance. For diagnosing one report, session-scoped waits are far more useful — they answer "what did this query wait on."

-- Run in the SAME session as the query you are testing.
SET STATISTICS TIME ON;

-- 1. Snapshot before
SELECT wait_type, waiting_tasks_count, wait_time_ms
INTO   #waits_before
FROM   sys.dm_exec_session_wait_stats
WHERE  session_id = @@SPID;

-- 2. Run the slow report query here
SELECT COUNT_BIG(*) FROM sys.objects AS a CROSS JOIN sys.objects AS b;  -- placeholder

-- 3. Diff
SELECT  a.wait_type,
        a.waiting_tasks_count - ISNULL(b.waiting_tasks_count,0) AS tasks,
        a.wait_time_ms        - ISNULL(b.wait_time_ms,0)        AS wait_ms
FROM    sys.dm_exec_session_wait_stats AS a
LEFT JOIN #waits_before AS b ON b.wait_type = a.wait_type
WHERE   a.session_id = @@SPID
  AND   a.wait_time_ms - ISNULL(b.wait_time_ms,0) > 0
ORDER BY wait_ms DESC;

DROP TABLE #waits_before;
SET STATISTICS TIME OFF;

That diff is the honest answer to "why did my report take 40 seconds."

The waits you will actually see, and what they mean

CXPACKET / CXCONSUMER — parallelism coordination. Some is normal on any report query, which by nature goes parallel. CXCONSUMER in particular is mostly benign. Treat high CXPACKET as a symptom pointing at skew or a bad estimate rather than as a problem in itself. Reflexively setting MAXDOP to 1 to make this number go down usually makes reports slower.

PAGEIOLATCH_SH — reading data pages from disk into memory. Means you are reading more data than fits in the buffer pool, or the storage is slow. The fix is almost never faster disks; it is reading fewer pages, which means a better index or a more selective query.

LCK_M_S, LCK_M_IS, LCK_M_X — lock waits. Your report is blocked behind someone else's transaction. This is Module 6's territory: the correct fix is usually READ COMMITTED SNAPSHOT isolation, not NOLOCK.

RESOURCE_SEMAPHORE — waiting for a memory grant. Some other query (possibly this one on a previous run) asked for a huge grant and there is not enough to go around. Traces back to bad cardinality estimates producing oversized grants (Module 2).

SOS_SCHEDULER_YIELD — CPU pressure; threads yielding after their quantum. Combined with high signal wait time, this says the server is CPU-bound.

ASYNC_NETWORK_IO — the server has rows ready and the client is not consuming them fast enough. This one is important for your situation: it is the classic signature of a report engine pulling a large result set and processing rows as they arrive. High ASYNC_NETWORK_IO means the database is not your bottleneck; the client is. Do not spend a week on indexes when this wait dominates.

WRITELOG — transaction log flushes. Matters for write-heavy OLTP, rarely for a read report.

Server-wide, with a clean baseline

To see the instance's overall pattern you want waits since a known point rather than since the last restart. Clear and re-measure over a defined window (this affects only the counters, but do it with awareness on a shared server):

DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);
-- let the workload run for a representative window: 15-60 minutes
-- then run the top-waits query from the previous lesson

What waits do not tell you

Waits tell you what the server queued on, not which query caused it. A dominant PAGEIOLATCH_SH says "we are reading a lot from disk," not "your report is the culprit." Pair waits with dm_exec_query_stats or Query Store to attribute the cost to a specific statement. Waits point the direction; the query-level views name the offender.

The short version

Elapsed minus CPU is wait time, and sys.dm_exec_session_wait_stats diffed around the query tells you exactly what it waited on. LCK_* means blocking, PAGEIOLATCH_SH means you are reading too many pages, RESOURCE_SEMAPHORE means memory grants, and ASYNC_NETWORK_IO means the client is the bottleneck, not the server. That last one changes where the week's work goes.