SQL Server Waits

SQL Server records every moment it spends waiting — on locks, latches, I/O, CPU coordination, memory and network calls. The DMV sys.dm_os_wait_stats is the scoreboard for the wait statistics. This dmv provides details about all waits encountered by executing threads, and it is essential for diagnosing performance issues with SQL Server and specific queries being run.

The only caveat is that the sys.dm_os_wait_stats is crowded with a lot of noise, so you need to filter and prioritize to be sure you are looking at what matters. Here are the waits that consistently give you usable performance insight.

1. Concurrency & Blocking
    LCK_M_% (locks)
    Meaning:  Sessions are blocked waiting on locks.
    Why it matters:  Points to blocking chains, poor indexing, or long transactions.
    Action:  Run blocking queries (XE or sys.dm_exec_requests), shorten 

                transactions, add missing indexes.
 

    PAGELATCH_%
    Meaning:  Latch contention, often in tempdb.
    Why it matters:  Classic sign of tempdb allocation contention.
    Action:  Add multiple equally sized tempdb files (usually 1 per 4 cores up to 

                8), check hot spots.

2. Parallelism
    CXPACKET / CXCONSUMER
    Meaning:  Threads coordinating parallel queries.
    Why it matters:  High values = skew or poor parallelism decisions.
    Action:  Review MAXDOP, update statistics, look at plan skew (one thread 

               does all the work).

3. I/O Bottlenecks
    WRITELOG
    Meaning:  Waiting to flush to the transaction log.
    Why it matters:  Log is bottlenecking throughput.
    Action:  Pre-size logs, fix VLF fragmentation, confirm storage latency, keep log 

               on its own disk/LUN.

    PAGEIOLATCH_%
    Meaning:  Waiting on data file I/O.
    Why it matters:  Points to slow disk or excessive scans.
    Action:  Tune queries, add indexes, check storage latency.

4. Memory Pressure
    RESOURCE_SEMAPHORE
    Meaning:  Queries are waiting for memory grants.
    Why it matters:  Memory-intensive operators (hash joins, sorts) can starve 

                             the system.

    Action:   Add missing indexes, reduce row estimates, break up big queries, or 

                 scale RAM.
    MEMORY_GRANT_PENDING (via XE)
    Meaning:  Same problem, live view.
    Action:  Use sys.dm_exec_query_memory_grants to see offenders.

5. Network & Client

    ASYNC_NETWORK_IO
    Meaning:  SQL is waiting for the client to fetch rows.
    Why it matters:  Not a SQL bottleneck — it’s the app pulling rows too slowly.
    Action:  Fix fetch size, batching, or chatty app design.

6. Ignore the Junk
    Don’t waste time on:

 SLEEP_TASK

 XE_TIMER_EVENT

 BROKER_ waits (unless you use Service Broker)

   

   These inflate totals but tell you nothing about performance. Filter them out 

   when running wait stats queries.

Quick Script: Top Useful Waits

SELECT TOP 10
    wait_type,
    waiting_tasks_count,
    wait_time_ms/1000.0 seconds,
    CAST(100.0 * wait_time_ms / SUM(wait_time_ms) OVER() AS DECIMAL(5,2)) pct
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    'SLEEP_TASK','BROKER_TASK_STOP','XE_TIMER_EVENT',
    'BROKER_TO_FLUSH','SQLTRACE_BUFFER_FLUSH'
)
ORDER BY wait_time_ms DESC;

 

* Focus on the top 2–3 by percentage, not just raw counts. This is where your users are feeling the pain. *

The sys.dm_os_wait_stats DMV is a very powerful tool for diagnosing SQL Server performance concerns.  Using it can help you to better understand your server's wait statistics and help you to more easily identify bottlenecks and optimize SQL Server's performance.