Wait Statistics Query Performance SQL Script

SQL Wait Statistics

Posted by John Liu on Monday, November 22, 2021

SQL Server Wait statistics can be used to troubleshooting performance problem. Wait statistics are broken down into three type of waits:

  1. Resource waits: work thread is waiting for access to server resources. Such as lock, latch and disk I/O waits.
  2. Queue waits: work thread is idle and waiting for work to be assigned. Such as deadlock monitoring and deleted record cleanup.
  3. 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:

  1. 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.
  2. 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.
  3. 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.
  4. SOS_SCHEDULER_YIELD: High wait indicates CPU pressure that might be caused by high number of large scans or missing indexes.
  5. 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.
  6. 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.

FEATURED TAGS
ai api automation availability availability sets availability zones aws vm azure azure automation runbook azure blob azure cosmos db azure data lake azure function app azure openai azure sign-in azure site recovery azure sql database azure sql db azure subscription azure vm base64 certificate change data capture change tracking chrome clr container cte data api builder data conversion data gateway database role database size date table dax db config derived table diagram direct query disk management disk space docker downtime dtc dynamic m parameter embedding encrypted connection excel excel online execution plan extended events external data fabric fabric capacity failover cluster fk geometry hierarchy httpwebrequest hugo hyper-v incognito mode index infrastructure inline tvf json kql lakehouse linked server live query statistics locking m machine learning machine learning model machine learning services master key mcp mdx memory memory grant mermaid mirrored sql server network network card network category onedrive onnx runtime openrowset p2v parquet performance polybase power automate power bi power bi report tricks power platform power query powershell printer public ip address pyspark python qgis qt designer query performance query plan query troubleshooting r regex replication route s3 schema design scripting self-signed certificate server role sharepoint snowflake software development sofware development spark sql sql agent sql availability group sql error sql failover cluster instance sql index sql openjson sql permission sql recovery sql script sql security sql server sql server admin sql server config sql statistics ssis ssisdb ssl ssl/tls error ssms table expression tempdb tips troubleshooting unicode view visual studio visual studio code vmware wait statistics wi-fi connection issue windows settings