SQL Server waits that matter, and the ones you can ignore

Start with waits

When a task in SQL Server cannot go on, because it needs a page from disk, a lock another session holds, a memory grant or a CPU, it waits. SQL Server records the wait type and the time in sys.dm_os_wait_stats. Summed over a busy period, that view tells you where the time went before you look at a single query.

Each wait type has two numbers worth knowing. wait_time_ms is the total time spent waiting. signal_wait_time_ms is the part spent after the resource became available, waiting in the queue for a CPU. The difference is the time spent waiting for the resource itself.

Read deltas, not totals

sys.dm_os_wait_stats is cumulative since the instance started, or since someone cleared it. An instance that has been up for 90 days carries every nightly backup and last month's incident in the same totals. Take two snapshots and subtract:

-- Snapshot, wait a minute, snapshot again: the difference is that minute
SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms
INTO #w1
FROM sys.dm_os_wait_stats;

WAITFOR DELAY '00:01:00';

SELECT w2.wait_type,
       w2.wait_time_ms - w1.wait_time_ms               AS wait_ms,
       w2.signal_wait_time_ms - w1.signal_wait_time_ms AS signal_ms,
       w2.waiting_tasks_count - w1.waiting_tasks_count AS waits
FROM sys.dm_os_wait_stats AS w2
JOIN #w1 AS w1 ON w1.wait_type = w2.wait_type
WHERE w2.wait_time_ms - w1.wait_time_ms > 0
ORDER BY wait_ms DESC;

One more thing the totals hide: a wait is added to sys.dm_os_wait_stats only when it ends. A session stuck behind a lock for ten minutes shows up in sys.dm_os_waiting_tasks and sys.dm_exec_requests while it waits, not here. Divide the wait time by the number of waits to get the average. A wait type with a large total and a tiny average is many short waits; one with a small count and a large average is a few long ones. They have different causes.

Filter the benign waits first

Many wait types belong to background tasks that wait by design: they sleep until there is work. They collect enormous wait times and mean nothing. Remove them before you rank anything. A short list, not a complete one:

• SLEEP_TASK, LAZYWRITER_SLEEP, SQLTRACE_BUFFER_FLUSH, DIRTY_PAGE_POLL
• XE_TIMER_EVENT, XE_DISPATCHER_WAIT, SP_SERVER_DIAGNOSTICS_SLEEP
• REQUEST_FOR_DEADLOCK_SEARCH, LOGMGR_QUEUE, CHECKPOINT_QUEUE
• BROKER_TO_FLUSH, BROKER_TASK_STOP, FT_IFTS_SCHEDULER_IDLE_WAIT
• QDS_PERSIST_TASK_MAIN_LOOP_SLEEP, HADR_FILESTREAM_IOMGR_IOCOMPLETION
• WAITFOR, which is a statement waiting on purpose

The families that point to something

Locks: LCK_M_*

A session wants a lock that another session holds. The suffix names the mode: LCK_M_S for shared, LCK_M_U for update, LCK_M_X for exclusive, LCK_M_IX and friends for intent locks. The fix is almost never in the session that waits. It is in the one holding the lock, so go to the blocking chain.

Reading pages: PAGEIOLATCH_SH and PAGEIOLATCH_EX

A query needs a data page that is not in the buffer pool. Large values mean one of three things: the storage is slow (check latency per file in sys.dm_io_virtual_file_stats), queries read far more than they should (scans an index would avoid), or the buffer pool is too small for the working set. Look at file latency first; it settles which of the three you are in.

Writing the log: WRITELOG

Commits wait for the transaction log to be flushed. Check write latency on the log file, and look for applications committing one row at a time in a loop.

Pages in memory: PAGELATCH_*

Not I/O, despite the name. Many sessions want the same page in memory at once: allocation pages in tempdb under heavy temporary table use, or the last page of an index whose key always increases, under heavy inserts.

CPU: SOS_SCHEDULER_YIELD and signal waits

Workers give up the CPU after their 4 ms quantum and wait to get it back. If signal waits are a large share of all wait time, sessions are queueing for CPU. Find the queries that burn it before you add cores.

Memory grants: RESOURCE_SEMAPHORE

Queries are waiting for memory to sort or hash. Usually a handful of queries ask for far more memory than they use, because the optimizer estimated far more rows than they return.

Parallelism: CXPACKET and CXCONSUMER

Threads of a parallel query waiting for each other. On their own they say that queries ran in parallel, not that anything is wrong. CXCONSUMER is generally harmless. A high CXPACKET with uneven work across threads is worth a look at MAXDOP and the cost threshold for parallelism.

The client: ASYNC_NETWORK_IO

SQL Server has the results ready and the client is not reading them. Often an application that fetches rows one at a time and does work between them. The database is not the bottleneck here.

Starvation: THREADPOOL

There is no worker thread free to run a new request. Rare and serious: usually heavy blocking holding workers, or a storm of new connections.

Then find the head of the blocking chain

Lock waits are a symptom. Every blocked request in sys.dm_exec_requests has a blocking_session_id. Follow it until you reach a session that blocks others and is not blocked itself. Often it is not running anything at all: it is a sleeping session with an open transaction, and an application that forgot to commit.

-- Who is blocked, and by whom
SELECT r.session_id, r.blocking_session_id, r.wait_type,
       r.wait_time AS wait_ms, r.wait_resource, t.text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0;

-- Sessions holding a transaction open while doing nothing
SELECT session_id, login_name, host_name, program_name,
       last_request_end_time, open_transaction_count
FROM sys.dm_exec_sessions
WHERE status = 'sleeping' AND open_transaction_count > 0;

How Logstag shows this

The agent reads these views with a login that has VIEW SERVER STATE, and samples sessions and blocking at short intervals. It drops the benign waits before ranking the top ones, and the Activity Explorer's Blocking Chains tab starts each chain at the root blocker. The SQL Server checks raise alerts for blocking and long-running sessions, each with its likely causes and what to do.