A resource wait is Process running on SQL Server which is waiting for resources avaialability. like a process is waiting for the release of lock on specific object/record.
We can check all the waits encountered by threads that executed by querieng sys.dm_os_wait_stats DMV.
We can query sys.sysprocesses to know the current waittype of specific spid. lastwaittype coloumn provides waittype information.
Below is the list of some wait types:
| Type | Description |
| ASYNC_IO_COMPLETION | Occurs when a task is waiting for I/Os to finish. |
| ASYNC_NETWORK_IO | Occurs on network writes when the task is blocked behind the network. Verify that the client is processing data from the server |
| BACKUP | Occurs when a task is blocked as part of backup processing. |
| BACKUP_OPERATOR | Occurs when a task is waiting for a tape mount. To view the tape status, query sys.dm_io_backup_tapes. If a mount operation is not pending, this wait type may indicate a hardware problem with the tape drive. |
| BACKUPBUFFER | 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. |
| BACKUPIO | 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. |
| BAD_PAGE_PROCESS | Occurs when the background suspect page logger is trying to avoid running more than every five seconds. Excessive suspect pages cause the logger to run frequently. |
| BROKER_SERVICE | Occurs when the Service Broker destination list that is associated with a target service is updated or re-prioritized. |
| CHECKPOINT_QUEUE | Occurs while the checkpoint task is waiting for the next checkpoint request. |
| CLEAR_DB | Occurs during operations that change the state of a database, such as opening or closing a database. |
| CXPACKET | Occurs with parallel query plans when synchronizing the query processor exchange iterator, and when producing and consuming rows. If waiting is excessive and cannot be reduced by tuning the query (such as adding indexes), consider adjusting the cost threshold for parallelism or lowering the degree of parallelism. |
| CXROWSET_SYNC | Occurs during a parallel range scan. |
| DEADLOCK_ENUM_MUTEX | Occurs when the deadlock monitor and sys.dm_os_waiting_tasks try to make sure that SQL Server is not running multiple deadlock searches at the same time. |
| DEADLOCK_TASK_SEARCH | Large waiting time on this resource indicates that the server is executing queries on top of sys.dm_os_waiting_tasks, and these queries are blocking deadlock monitor from running deadlock search. This wait type is used by deadlock monitor only. Queries on top of sys.dm_os_waiting_tasks use DEADLOCK_ENUM_MUTEX. |
| DISKIO_SUSPEND | Occurs when a task is waiting to access a file when an external backup is active. This is reported for each waiting user process. A count larger than five per user process may indicate that the external backup is taking too much time to finish. |
| EXCHANGE | Occurs during synchronization in the query processor exchange iterator during parallel queries. |
| IO_AUDIT_MUTEX | Occurs during synchronization of trace event buffers. |
| IO_COMPLETION | Occurs while waiting for I/O operations to complete. This wait type generally represents non-data page I/Os. Data page I/O completion waits appear as PAGEIOLATCH_* waits. |
| IO_QUEUE_LIMIT | TBD |
| Applies to: SQL Server 2016 through SQL Server 2017. | |
| IO_RETRY | Occurs when an I/O operation such as a read or a write to disk fails because of insufficient resources, and is then retried. |
| LATCH_DT | Occurs when waiting for a DT (destroy) latch. This does not include buffer latches or transaction mark latches. A listing of LATCH_* waits is available in sys.dm_os_latch_stats. Note that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together. |
| LATCH_EX | Occurs when waiting for an EX (exclusive) latch. This does not include buffer latches or transaction mark latches. A listing of LATCH_* waits is available in sys.dm_os_latch_stats. Note that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together. |
| LATCH_KP | Occurs when waiting for a KP (keep) latch. This does not include buffer latches or transaction mark latches. A listing of LATCH_* waits is available in sys.dm_os_latch_stats. Note that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together. |
| LATCH_NL | Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. |
| LATCH_SH | Occurs when waiting for an SH (share) latch. This does not include buffer latches or transaction mark latches. A listing of LATCH_* waits is available in sys.dm_os_latch_stats. Note that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together. |
| LATCH_UP | Occurs when waiting for an UP (update) latch. This does not include buffer latches or transaction mark latches. A listing of LATCH_* waits is available in sys.dm_os_latch_stats. Note that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together. |
| LAZYWRITER_SLEEP | Occurs when lazywriter tasks are suspended. This is a measure of the time spent by background tasks that are waiting. Do not consider this state when you are looking for user stalls. |
| LCK_M_BU | Occurs when a task is waiting to acquire a Bulk Update (BU) lock. |
| LCK_M_X | Occurs when a task is waiting to acquire an Exclusive lock. |
| OGBUFFER | Occurs when a task is waiting for space in the log buffer to store a log record. Consistently high values may indicate that the log devices cannot keep up with the amount of log being generated by the server. |
| LOGMGR | Occurs when a task is waiting for any outstanding log I/Os to finish before shutting down the log while closing the database. |
| LOGMGR_FLUSH | Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. |
| LOGMGR_QUEUE | Occurs while the log writer task waits for work requests. |
| OLEDB | Occurs when SQL Server calls the SQL Server Native Client OLE DB Provider. This wait type is not used for synchronization. Instead, it indicates the duration of calls to the OLE DB provider. |
| ONDEMAND_TASK_QUEUE | Occurs while a background task waits for high priority system task requests. Long wait times indicate that there have been no high priority requests to process, and should not cause concern. |
| PAGEIOLATCH_DT | Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Destroy mode. Long waits may indicate problems with the disk subsystem. |
| PAGEIOLATCH_EX | Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Exclusive mode. Long waits may indicate problems with the disk subsystem. |
| PAGEIOLATCH_KP | Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Keep mode. Long waits may indicate problems with the disk subsystem. |
| PAGEIOLATCH_NL | Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. |
| PAGEIOLATCH_SH | 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. |
| PAGEIOLATCH_UP | Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Update mode. Long waits may indicate problems with the disk subsystem. |
| PAGELATCH_DT | Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Destroy mode. |
| PAGELATCH_EX | Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Exclusive mode. |
| PAGELATCH_KP | Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Keep mode. |
| PAGELATCH_NL | Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. |
| PAGELATCH_SH | Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Shared mode. |
| PAGELATCH_UP | Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Update mode. |
| WAITFOR | Occurs as a result of a WAITFOR Transact-SQL statement. The duration of the wait is determined by the parameters to the statement. This is a user-initiated wait. |
| WRITELOG | Occurs while waiting for a log flush to complete. Common operations that cause log flushes are checkpoints and transaction commits. |
No comments:
Post a Comment