SQL Server Wait statistics can be used to troubleshooting performance problem. Wait statistics are broken down into three type of waits:
- Resource waits: work thread is waiting for access to server resources. Such as lock, latch and disk I/O waits.
- Queue waits: work thread is idle and waiting for work to be assigned. Such as deadlock monitoring and deleted record cleanup.
- External Waits: SQL Server is waiting for external process to complete, like linked server query. Network wait is related to return large result set to client application.
Some common high waits that related to common performance issues:
- RESOURCE_SEMAPHORE: Query is waiting for memory to become available. This may indicates excessive memory grants. This can be caused by out-of-date statistics, missing indexes, excessive query concurrency.
- LCK_M_X: Indication of blocking problem. To reduce this wait, try using READ COMMITED SNAPSHOT isolation level or using indexing to reduce transaction time, or better transaction management in the T-SQL code.
- PAGEIOLATCH_SH: High waits indicates index problem or lack of useful index as SQL is scanning too much data. If the wait count is low but wait time is high, it indication of storage performance problem.
- SOS_SCHEDULER_YIELD: High wait indicates CPU pressure that might be caused by high number of large scans or missing indexes.
- CXPACKET: High wait may indicates of improper configuration with MaxDOP that caused small query go parallel. It may also indicates CPU pressure, often in conjuntion with SOS_SCHEDULER_YIELD.
- PAGEIOLATCH_UP: When high wait on data pages 2:1:1, it’s indication of TempDB contention on PFS (Page Free Space) data pages. Consider add more data files and configure all data file equal size.
Using waiting_tasks_count and wait_time_ms in sys.dm_os_wait_Stats to calculate average wait time for a given wait type.