Network Management
Understanding SQL Server Wait Types
This collection of content will describe SQL Server wait types that can be encountered more or less commonly, which includes the description, steps that should be taken and additional resources that could be used for troubleshooting.
First published date
10/20/2021 9:53 AM
Last published date
2/23/2023 6:01 AM
Overview
Common Wait Types were seen from the Orion Insights
Product section
Network Performance Monitor
Cause
SQL WAIT Statistics
The wait times are captured and recorded by the SQL Server and all of this captured information is called wait statistics. It provides assistance to resolve problems that are related to the SQL Server performance.
Wait statistics are one of the most important indicators to identify performance issues in SQL Server. When we want to troubleshoot any performance issue, at first we need to diagnose the problem correctly because correctly diagnosing problems helps with half of the solution. Learn and know the wait statistics which helps to identify performance issues properly.
The wait times are captured and recorded by the SQL Server and all of this captured information is called wait statistics. It provides assistance to resolve problems that are related to the SQL Server performance.
Wait statistics are one of the most important indicators to identify performance issues in SQL Server. When we want to troubleshoot any performance issue, at first we need to diagnose the problem correctly because correctly diagnosing problems helps with half of the solution. Learn and know the wait statistics which helps to identify performance issues properly.
Resolution
Common SQL WAIT Statistic
- WRITELOG Sql Wait Type
- The WRITELOG wait time represents the time that accumulates while waiting for the content of the transaction log cache to be flushed to the physical disk that stores the transaction log file
- CXPACKET SQL WAIT Stats
- This indicates that SQL is waiting on a Parallel process to complete. This can be a sign of resource issues on the SQL server (CPU, MEM, DiskIO) or the query itself is poorly written.
- ASYNC_NETWORK_IO SQL WAIT Stats
- This is usually a sign of network latency between the client and the server.
- PAGEIOLATCH_EX SQL WAIT Stats
- Buffer latches including the PAGEIOLATCH_EX wait type are used to synchronize access to BUF structures and associated pages in the SQL Server database. This can be a sign of resource issues on the SQL server or a poorly written query. When seen in conjunction with the CXPACKET wait Index Fragmentation is often the cause.
- LCK_M_X SQL WAIT Stats
- This Occurs when a resource is in use by another query, usually, the result of an UPDATE, INSERT, or DELETE statement. LCK waits are usually caused by resource contention but may also be the results of process blocking from any of the above wait types
- BACKUPIO SQL WAIT Stats
- Occurs when a backup task is waiting for data or is waiting for a buffer in which to store data. This type is not typical, except when a task is waiting for a tape mount
- BACKUPBUFFER SQL WAIT Stats
- These wait stats will occur when you are taking the backup on the tape or any other extremely slow backup system.
- ASYNC_IO_COMPLETION SQL WAIT Stats
- Any tasks are waiting for I/O to finish. If by any means your application that’s connected to SQL Server is processing the data very slowly, this type of wait can occur. Several long-running database operations like BACKUP, CREATE DATABASE, ALTER DATABASE or other operations can also create this wait type
- SOS_SCHEDULER_YIELD SQL WAIT Stats
- Occurs when a task voluntarily yields the scheduler for other tasks to execute. During this wait the task is waiting for its quantum to be renewed.
- HADR_SYNC_COMMIT Sql Wait Type
- HADR_SYNC_COMMIT indicates the time between when a transaction-ready to commit in the primary replica, and all secondary synchronous-commit replicas have acknowledged the hardening of the transaction commit LSN in an AG. It means a transaction in the primary replica cannot be committed until the primary replica received greater hardened LSNs from all secondary synchronous-commit replicas.
- If transactions in the primary replica are slower than usual, and HADR_SYNC_COMMIT is unusually long, it means there is some performance issue in at least one Primary-Secondary replica data movement flow, or at least one secondary replica is slow in log hardening.
- PAGEIOLATCH_SH Sql Wait Type
- Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Shared mode. Long waits may indicate problems with the disk subsystem.
- LATCH_EX SQL Wait Type
- Latches are used to guarantee the consistency of non-buffer pool memory structures. This wait type is for when the request requires exclusive access to an object. Note that high values in this wait do not always mean a problem (e.g. in the case of parallelism)
- Preemptive Wait Types GETPROCADDRESS
- This wait accumulates while SQL Server is waiting for the Operating System to return the procedure address so it can execute an extended stored procedure. These procedures are deprecated and should not be used for further development. These procedures run in unmanaged memory space therefore can cause SQL Server to crash if not written correctly
The content posted herein is provided as a suggestion or recommendation to you for your internal use. The information set forth herein may come from third-party websites or customers. SolarWinds is not liable for any downtime or any issue that may occur if you perform the following suggestions on the link provided. Your organization should internally review and assess to what extent, if any, such custom scripts or recommendations will be incorporated into your environment.