Wait Statistics

SQL Server’s built-in mechanism for recording the resources that sessions are waiting for, serving as the primary diagnostic tool for identifying the root cause of SQL Server performance issues.

• Wait tracking: Whenever a SQL Server thread waits for a resource such as CPU, I/O, memory, locks, latches, or network operations, SQL Server records the wait type and duration in the sys.dm_os_wait_stats dynamic management view.
• Cumulative statistics: Wait statistics are cumulative from the last SQL Server restart. DBAs typically analyze the delta over a specific time period to identify the waits contributing to current performance problems.
• Common wait types: Important wait categories include PAGEIOLATCH (disk I/O bottlenecks), CXPACKET and CXCONSUMER (parallelism), LCK_ waits (locking and blocking), SOS_SCHEDULER_YIELD (CPU pressure), and ASYNC_NETWORK_IO (slow client applications consuming query results).
• Performance troubleshooting: Examining wait statistics should be the first step in most SQL Server performance investigations, as they identify the primary resource bottleneck and help guide further diagnostic efforts.
• Query-level analysis: SQL Server 2017 and later provide query-level wait statistics through Query Store and sys.dm_exec_query_stats, allowing DBAs to attribute waits to individual queries instead of relying solely on server-wide statistics.
• Relevant Idera tools: SQL Diagnostic Manager visualizes historical wait statistics trends, helping DBAs correlate performance incidents with spikes in specific wait types.
• Related terms: Root Cause Analysis, Latency, Blocking, Latch Wait, Buffer Pool.