Information About Waits Stats in SQL Server PREEMPTIVE Operating System Related Waits

SQLServerF1

SQL Server internally uses worker threads to execute SQL queries sent by various applications. While threads are executing they may be either running on CPU processing the request or would be waiting on a certain resource or waiting in waiting for its chance to run on CPU. SQL Server assigns a certain wait type to the worker thread that is waiting.

If a query is taking long time to complete, we can look at the query sessions wait type to get an understanding of what kind of resource it is the query waiting for and take appropriate action to avoid the queries to wait, thus making the queries complete fast.

Below are some of the SQL Server Wait Types related to PREEMPTIVE Operating System related waits.

PREEMPTIVE_ABR
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

PREEMPTIVE_AUDIT_ACCESS_EVENTLOG
Occurs when the SQL Server Operating System (SQLOS) scheduler switches to preemptive mode to write an audit event to the Windows event log.

PREEMPTIVE_AUDIT_ACCESS_SECLOG
Occurs when the SQLOS scheduler switches to preemptive mode to write an audit event to the Windows Security log.

PREEMPTIVE_CLOSEBACKUPMEDIA
Occurs when the SQLOS scheduler switches to preemptive mode to close backup media.

PREEMPTIVE_CLOSEBACKUPTAPE
Occurs when the SQLOS scheduler switches to preemptive mode to close a tape backup device.

PREEMPTIVE_CLOSEBACKUPVDIDEVICE
Occurs when the SQLOS scheduler switches to preemptive mode to close a virtual backup device.

PREEMPTIVE_CLUSAPI_CLUSTERRESOURCECONTROL
Occurs when the SQLOS scheduler switches to preemptive mode to perform Windows failover cluster operations.

PREEMPTIVE_COM_COCREATEINSTANCE
Occurs when the SQLOS scheduler switches to preemptive mode to create a COM object.

PREEMPTIVE_HADR_LEASE_MECHANISM
AlwaysOn Availability Groups lease manager scheduling for CSS diagnostics.
Applies to: SQL Server 2012 through SQL Server 2014.

PREEMPTIVE_SOSTESTING
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

PREEMPTIVE_STRESSDRIVER
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

PREEMPTIVE_TESTING
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

PREEMPTIVE_XETESTING
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Information About Waits Stats in SQL Server Physical I/O related SQL Server waits related Waits

SQLServerF1

SQL Server internally uses worker threads to execute SQL queries sent by various applications. While threads are executing they may be either running on CPU processing the request or would be waiting on a certain resource or waiting in waiting for its chance to run on CPU. SQL Server assigns a certain wait type to the worker thread that is waiting.

If a query is taking long time to complete, we can look at the query sessions wait type to get an understanding of what kind of resource it is the query waiting for and take appropriate action to avoid the queries to wait, thus making the queries complete fast.

Below are some of the SQL Server Wait Types related to Physical I/O related SQL Server waits, Network, Search and OLEDB related waits in SQL Server.

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.

MSQL_XP
Occurs when a task is waiting for an extended stored procedure to end. SQL Server uses this wait state to detect potential MARS application deadlocks. The wait stops when the extended stored procedure call ends.

MSSEARCH
Occurs during Full-Text Search calls. This wait ends when the full-text operation completes. It does not indicate contention, but rather the duration of full-text operations.

NET_WAITFOR_PACKET
Occurs when a connection is waiting for a network packet during a network read.

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.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Information About Waits Stats in SQL Server Transaction Log Latency related Waits

SQLServerF1

SQL Server internally uses worker threads to execute SQL queries sent by various applications. While threads are executing they may be either running on CPU processing the request or would be waiting on a certain resource or waiting in waiting for its chance to run on CPU. SQL Server assigns a certain wait type to the worker thread that is waiting.

If a query is taking long time to complete, we can look at the query sessions wait type to get an understanding of what kind of resource it is the query waiting for and take appropriate action to avoid the queries to wait, thus making the queries complete fast.

Below are some of the SQL Server Wait Types related to Transaction log latency related waits, SQL transaction related waits and MISCELLANEOUS SQL Server wait.

LOGBUFFER
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.

LOGGENERATION
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

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.

LOGMGR_RESERVE_APPEND
Occurs when a task is waiting to see whether log truncation frees up log space to enable the task to write a new log record. Consider increasing the size of the log file(s) for the affected database to reduce this wait.

LOWFAIL_MEMMGR_QUEUE
Occurs while waiting for memory to be available for use.

MISCELLANEOUS
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

MSQL_DQ
Occurs when a task is waiting for a distributed query operation to finish. This is used to detect potential Multiple Active Result Set (MARS) application deadlocks. The wait ends when the distributed query call finishes.

MSQL_XACT_MGR_MUTEX
Occurs when a task is waiting to obtain ownership of the session transaction manager to perform a session level transaction operation.

MSQL_XACT_MUTEX
Occurs during synchronization of transaction usage. A request must acquire the mutex before it can use the transaction.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Information About Waits Stats in SQL Server LOCK related Waits

SQLServerF1

SQL Server internally uses worker threads to execute SQL queries sent by various applications. While threads are executing they may be either running on CPU processing the request or would be waiting on a certain resource or waiting in waiting for its chance to run on CPU. SQL Server assigns a certain wait type to the worker thread that is waiting.

If a query is taking long time to complete, we can look at the query sessions wait type to get an understanding of what kind of resource it is the query waiting for and take appropriate action to avoid the queries to wait, thus making the queries complete fast.

Below are some of the SQL Server Wait Types related to LOCK related waits.

LCK_M_BU
Occurs when a task is waiting to acquire a Bulk Update (BU) lock.

LCK_M_BU_ABORT_BLOCKERS
Occurs when a task is waiting to acquire a Bulk Update (BU) lock with Abort Blockers. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_BU_LOW_PRIORITY
Occurs when a task is waiting to acquire a Bulk Update (BU) lock with Low Priority. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_IS
Occurs when a task is waiting to acquire an Intent Shared (IS) lock.

LCK_M_IS_ABORT_BLOCKERS
Occurs when a task is waiting to acquire an Intent Shared (IS) lock with Abort Blockers. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_IS_LOW_PRIORITY
Occurs when a task is waiting to acquire an Intent Shared (IS) lock with Low Priority. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_IU
Occurs when a task is waiting to acquire an Intent Update (IU) lock.

LCK_M_IU_ABORT_BLOCKERS
Occurs when a task is waiting to acquire an Intent Update (IU) lock with Abort Blockers. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_IU_LOW_PRIORITY
Occurs when a task is waiting to acquire an Intent Update (IU) lock with Low Priority. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_IX
Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock.

LCK_M_IX_ABORT_BLOCKERS
Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock with Abort Blockers. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_IX_LOW_PRIORITY
Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock with Low Priority. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_RIn_NL
Occurs when a task is waiting to acquire a NULL lock on the current key value, and an Insert Range lock between the current and previous key. A NULL lock on the key is an instant release lock.

LCK_M_RIn_NL_ABORT_BLOCKERS
Occurs when a task is waiting to acquire a NULL lock with Abort Blockers on the current key value, and an Insert Range lock with Abort Blockers between the current and previous key. A NULL lock on the key is an instant release lock. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_RIn_NL_LOW_PRIORITY
Occurs when a task is waiting to acquire a NULL lock with Low Priority on the current key value, and an Insert Range lock with Low Priority between the current and previous key. A NULL lock on the key is an instant release lock. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_RIn_S
Occurs when a task is waiting to acquire a shared lock on the current key value, and an Insert Range lock between the current and previous key.

LCK_M_RIn_S_ABORT_BLOCKERS
Occurs when a task is waiting to acquire a shared lock with Abort Blockers on the current key value, and an Insert Range lock with Abort Blockers between the current and previous key. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_RIn_S_LOW_PRIORITY
Occurs when a task is waiting to acquire a shared lock with Low Priority on the current key value, and an Insert Range lock with Low Priority between the current and previous key. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_RIn_U
Task is waiting to acquire an Update lock on the current key value, and an Insert Range lock between the current and previous key.

LCK_M_RIn_U_ABORT_BLOCKERS
Task is waiting to acquire an Update lock with Abort Blockers on the current key value, and an Insert Range lock with Abort Blockers between the current and previous key. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_RIn_U_LOW_PRIORITY
Task is waiting to acquire an Update lock with Low Priority on the current key value, and an Insert Range lock with Low Priority between the current and previous key. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_RIn_X
Occurs when a task is waiting to acquire an Exclusive lock on the current key value, and an Insert Range lock between the current and previous key.

LCK_M_RIn_X_ABORT_BLOCKERS
Occurs when a task is waiting to acquire an Exclusive lock with Abort Blockers on the current key value, and an Insert Range lock with Abort Blockers between the current and previous key. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_RIn_X_LOW_PRIORITY
Occurs when a task is waiting to acquire an Exclusive lock with Low Priority on the current key value, and an Insert Range lock with Low Priority between the current and previous key. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_RS_S
Occurs when a task is waiting to acquire a Shared lock on the current key value, and a Shared Range lock between the current and previous key.

LCK_M_RS_S_ABORT_BLOCKERS
Occurs when a task is waiting to acquire a Shared lock with Abort Blockers on the current key value, and a Shared Range lock with Abort Blockers between the current and previous key. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_RS_S_LOW_PRIORITY
Occurs when a task is waiting to acquire a Shared lock with Low Priority on the current key value, and a Shared Range lock with Low Priority between the current and previous key. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_RS_U
Occurs when a task is waiting to acquire an Update lock on the current key value, and an Update Range lock between the current and previous key.

LCK_M_RS_U_ABORT_BLOCKERS
Occurs when a task is waiting to acquire an Update lock with Abort Blockers on the current key value, and an Update Range lock with Abort Blockers between the current and previous key. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_RS_U_LOW_PRIORITY
Occurs when a task is waiting to acquire an Update lock with Low Priority on the current key value, and an Update Range lock with Low Priority between the current and previous key. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_RX_S
Occurs when a task is waiting to acquire a Shared lock on the current key value, and an Exclusive Range lock between the current and previous key.

LCK_M_RX_S_ABORT_BLOCKERS
Occurs when a task is waiting to acquire a Shared lock with Abort Blockers on the current key value, and an Exclusive Range with Abort Blockers lock between the current and previous key. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_RX_S_LOW_PRIORITY
Occurs when a task is waiting to acquire a Shared lock with Low Priority on the current key value, and an Exclusive Range with Low Priority lock between the current and previous key. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_RX_U
Occurs when a task is waiting to acquire an Update lock on the current key value, and an Exclusive range lock between the current and previous key.

LCK_M_RX_U_ABORT_BLOCKERS
Occurs when a task is waiting to acquire an Update lock with Abort Blockers on the current key value, and an Exclusive range lock with Abort Blockers between the current and previous key. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_RX_U_LOW_PRIORITY
Occurs when a task is waiting to acquire an Update lock with Low Priority on the current key value, and an Exclusive range lock with Low Priority between the current and previous key. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_RX_X
Occurs when a task is waiting to acquire an Exclusive lock on the current key value, and an Exclusive Range lock between the current and previous key.

LCK_M_RX_X_ABORT_BLOCKERS
Occurs when a task is waiting to acquire an Exclusive lock with Abort Blockers on the current key value, and an Exclusive Range lock with Abort Blockers between the current and previous key. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_RX_X_LOW_PRIORITY
Occurs when a task is waiting to acquire an Exclusive lock with Low Priority on the current key value, and an Exclusive Range lock with Low Priority between the current and previous key. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_S
Occurs when a task is waiting to acquire a Shared lock.

LCK_M_S_ABORT_BLOCKERS
Occurs when a task is waiting to acquire a Shared lock with Abort Blockers. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_S_LOW_PRIORITY
Occurs when a task is waiting to acquire a Shared lock with Low Priority. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_SCH_M
Occurs when a task is waiting to acquire a Schema Modify lock.

LCK_M_SCH_M_ABORT_BLOCKERS
Occurs when a task is waiting to acquire a Schema Modify lock with Abort Blockers. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_SCH_M_LOW_PRIORITY
Occurs when a task is waiting to acquire a Schema Modify lock with Low Priority. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_SCH_S
Occurs when a task is waiting to acquire a Schema Share lock.

LCK_M_SCH_S_ABORT_BLOCKERS
Occurs when a task is waiting to acquire a Schema Share lock with Abort Blockers. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_SCH_S_LOW_PRIORITY
Occurs when a task is waiting to acquire a Schema Share lock with Low Priority. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_SIU
Occurs when a task is waiting to acquire a Shared With Intent Update lock.

LCK_M_SIU_ABORT_BLOCKERS
Occurs when a task is waiting to acquire a Shared With Intent Update lock with Abort Blockers. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_SIU_LOW_PRIORITY
Occurs when a task is waiting to acquire a Shared With Intent Update lock with Low Priority. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_SIX
Occurs when a task is waiting to acquire a Shared With Intent Exclusive lock.

LCK_M_SIX_ABORT_BLOCKERS
Occurs when a task is waiting to acquire a Shared With Intent Exclusive lock with Abort Blockers. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_SIX_LOW_PRIORITY
Occurs when a task is waiting to acquire a Shared With Intent Exclusive lock with Low Priority. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_U
Occurs when a task is waiting to acquire an Update lock.

LCK_M_U_ABORT_BLOCKERS
Occurs when a task is waiting to acquire an Update lock with Abort Blockers. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_U_LOW_PRIORITY
Occurs when a task is waiting to acquire an Update lock with Low Priority. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_UIX
Occurs when a task is waiting to acquire an Update With Intent Exclusive lock.

LCK_M_UIX_ABORT_BLOCKERS
Occurs when a task is waiting to acquire an Update With Intent Exclusive lock with Abort Blockers. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_UIX_LOW_PRIORITY
Occurs when a task is waiting to acquire an Update With Intent Exclusive lock with Low Priority. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_X
Occurs when a task is waiting to acquire an Exclusive lock.

LCK_M_X_ABORT_BLOCKERS
Occurs when a task is waiting to acquire an Exclusive lock with Abort Blockers. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_X_LOW_PRIORITY
Occurs when a task is waiting to acquire an Exclusive lock with Low Priority. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Information About Waits Stats in SQL Server LATCH and LAZYWRITER Waits

SQLServerF1

SQL Server internally uses worker threads to execute SQL queries sent by various applications. While threads are executing they may be either running on CPU processing the request or would be waiting on a certain resource or waiting in waiting for its chance to run on CPU. SQL Server assigns a certain wait type to the worker thread that is waiting.

If a query is taking long time to complete, we can look at the query sessions wait type to get an understanding of what kind of resource it is the query waiting for and take appropriate action to avoid the queries to wait, thus making the queries complete fast.

Below are some of the SQL Server Wait Types related to LATCH and LAZYWRITER related waits.

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.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Information About Waits Stats in SQL Server HTTP, I/O and Kernel Waits

SQLServerF1

SQL Server internally uses worker threads to execute SQL queries sent by various applications. While threads are executing they may be either running on CPU processing the request or would be waiting on a certain resource or waiting in waiting for its chance to run on CPU. SQL Server assigns a certain wait type to the worker thread that is waiting.

If a query is taking long time to complete, we can look at the query sessions wait type to get an understanding of what kind of resource it is the query waiting for and take appropriate action to avoid the queries to wait, thus making the queries complete fast.

Below are some of the SQL Server Wait Types related to HTTP related waits, I/O related waits and SQL Server Kernel related waits.

HTTP_ENUMERATION
Occurs at startup to enumerate the HTTP endpoints to start HTTP.

HTTP_START
Occurs when a connection is waiting for HTTP to complete initialization.

IMPPROV_IOWAIT
Occurs when SQL Server waits for a bulkload I/O to finish.

INTERNAL_TESTING
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

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_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.

IOAFF_RANGE_QUEUE
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

KSOURCE_WAKEUP
Used by the service control task while waiting for requests from the Service Control Manager. Long waits are expected and do not indicate a problem.

KTM_ENLISTMENT
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

KTM_RECOVERY_MANAGER
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

KTM_RECOVERY_RESOLUTION
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Information About Waits Stats in SQL Server ALWAYSON or HADRON Waits

SQLServerF1

SQL Server internally uses worker threads to execute SQL queries sent by various applications. While threads are executing they may be either running on CPU processing the request or would be waiting on a certain resource or waiting in waiting for its chance to run on CPU. SQL Server assigns a certain wait type to the worker thread that is waiting.

If a query is taking long time to complete, we can look at the query sessions wait type to get an understanding of what kind of resource it is the query waiting for and take appropriate action to avoid the queries to wait, thus making the queries complete fast.

Below are some of the SQL Server Wait Types related to ALWAYSON related waits or HADRON waits.

GUARDIAN
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

HADR_AG_MUTEX
Occurs when an AlwaysOn DDL statement or Windows Server Failover Clustering command is waiting for exclusive read/write access to the configuration of an availability group.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_AR_CRITICAL_SECTION_ENTRY
Occurs when an AlwaysOn DDL statement or Windows Server Failover Clustering command is waiting for exclusive read/write access to the runtime state of the local replica of the associated availability group.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_AR_MANAGER_MUTEX
Occurs when an availability replica shutdown is waiting for startup to complete or an availability replica startup is waiting for shutdown to complete. Internal use only.

Note – Availability replica shutdown is initiated either by SQL Server shutdown or by SQL Server handling the loss of quorum by the Windows Server Failover Clustering node. Availability replica startup is initiated either by SQL Server startup or by SQL Server recovering from the loss of quorum by the Windows Server Failover Clustering node.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_ARCONTROLLER_NOTIFICATIONS_SUBSCRIBER_LIST
The publisher for an availability replica event (such as a state change or configuration change) is waiting for exclusive read/write access to the list of event subscribers. Internal use only.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_BACKUP_BULK_LOCK
The AlwaysOn primary database received a backup request from a secondary database and is waiting for the background thread to finish processing the request on acquiring or releasing the BulkOp lock.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_BACKUP_QUEUE
The backup background thread of the AlwaysOn primary database is waiting for a new work request from the secondary database. (typically, this occurs when the primary database is holding the BulkOp log and is waiting for the secondary database to indicate that the primary database can release the lock).
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_CLUSAPI_CALL
A SQL Server thread is waiting to switch from non-preemptive mode (scheduled by SQL Server) to preemptive mode (scheduled by the operating system) in order to invoke Windows Server Failover Clustering APIs.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_COMPRESSED_CACHE_SYNC
Waiting for access to the cache of compressed log blocks that is used to avoid redundant compression of the log blocks sent to multiple secondary databases.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_DATABASE_FLOW_CONTROL
Waiting for messages to be sent to the partner when the maximum number of queued messages has been reached. Indicates that the log scans are running faster than the network sends. This is an issue only if network sends are slower than expected.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_DATABASE_VERSIONING_STATE
Occurs on the versioning state change of an AlwaysOn secondary database. This wait is for internal data structures and is usually is very short with no direct effect on data access.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_DATABASE_WAIT_FOR_RESTART
Waiting for the database to restart under AlwaysOn Availability Groups control. Under normal conditions, this is not a customer issue because waits are expected here.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING
A query on object(s) in a readable secondary database of an AlwaysOn availability group is blocked on row versioning while waiting for commit or rollback of all transactions that were in-flight when the secondary replica was enabled for read workloads. This wait type guarantees that row versions are available before execution of a query under snapshot isolation.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_DB_COMMAND
Waiting for responses to conversational messages (which require an explicit response from the other side, using the AlwaysOn conversational message infrastructure). A number of different message types use this wait type.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_DB_OP_COMPLETION_SYNC
Waiting for responses to conversational messages (which require an explicit response from the other side, using the AlwaysOn conversational message infrastructure). A number of different message types use this wait type.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_DB_OP_START_SYNC
An AlwaysOn DDL statement or a Windows Server Failover Clustering command is waiting for serialized access to an availability database and its runtime state.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_DBR_SUBSCRIBER
The publisher for an availability replica event (such as a state change or configuration change) is waiting for exclusive read/write access to the runtime state of an event subscriber that corresponds to an availability database. Internal use only.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_DBR_SUBSCRIBER_FILTER_LIST
The publisher for an availability replica event (such as a state change or configuration change) is waiting for exclusive read/write access to the list of event subscribers that correspond to availability databases. Internal use only.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_DBSTATECHANGE_SYNC
Concurrency control wait for updating the internal state of the database replica.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_FILESTREAM_BLOCK_FLUSH
The FILESTREAM AlwaysOn transport manager is waiting until processing of a log block is finished.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_FILESTREAM_FILE_CLOSE
The FILESTREAM AlwaysOn transport manager is waiting until the next FILESTREAM file gets processed and its handle gets closed.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_FILESTREAM_FILE_REQUEST
An AlwaysOn secondary replica is waiting for the primary replica to send all requested FILESTREAM files during UNDO.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_FILESTREAM_IOMGR
The FILESTREAM AlwaysOn transport manager is waiting for R/W lock that protects the FILESTREAM AlwaysOn I/O manager during startup or shutdown.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_FILESTREAM_IOMGR_IOCOMPLETION
The FILESTREAM AlwaysOn I/O manager is waiting for I/O completion.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_FILESTREAM_MANAGER
The FILESTREAM AlwaysOn transport manager is waiting for the R/W lock that protects the FILESTREAM AlwaysOn transport manager during startup or shutdown.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_GROUP_COMMIT
Transaction commit processing is waiting to allow a group commit so that multiple commit log records can be put into a single log block. This wait is an expected condition that optimizes the log I/O, capture, and send operations.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_LOGCAPTURE_SYNC
Concurrency control around the log capture or apply object when creating or destroying scans. This is an expected wait when partners change state or connection status.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_LOGCAPTURE_WAIT
Waiting for log records to become available. Can occur either when waiting for new log records to be generated by connections or for I/O completion when reading log not in the cache. This is an expected wait if the log scan is caught up to the end of log or is reading from disk.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_LOGPROGRESS_SYNC
Concurrency control wait when updating the log progress status of database replicas.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_NOTIFICATION_DEQUEUE
A background task that processes Windows Server Failover Clustering notifications is waiting for the next notification. Internal use only.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_NOTIFICATION_WORKER_EXCLUSIVE_ACCESS
The AlwaysOn availability replica manager is waiting for serialized access to the runtime state of a background task that processes Windows Server Failover Clustering notifications. Internal use only.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_NOTIFICATION_WORKER_STARTUP_SYNC
A background task is waiting for the completion of the startup of a background task that processes Windows Server Failover Clustering notifications. Internal use only.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_NOTIFICATION_WORKER_TERMINATION_SYNC
A background task is waiting for the termination of a background task that processes Windows Server Failover Clustering notifications. Internal use only.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_PARTNER_SYNC
Concurrency control wait on the partner list.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_READ_ALL_NETWORKS
Waiting to get read or write access to the list of WSFC networks. Internal use only.
Note
The engine keeps a list of WSFC networks that is used in dynamic management views (such as sys.dm_hadr_cluster_networks) or to validate AlwaysOn Transact-SQL statements that reference WSFC network information. This list is updated upon engine startup, WSFC related notifications, and internal AlwaysOn restart (for example, losing and regaining of WSFC quorum). Tasks will usually be blocked when an update in that list is in progress.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_RECOVERY_WAIT_FOR_CONNECTION
Waiting for the secondary database to connect to the primary database before running recovery. This is an expected wait, which can lengthen if the connection to the primary is slow to establish.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_RECOVERY_WAIT_FOR_UNDO
Database recovery is waiting for the secondary database to finish the reverting and initializing phase to bring it back to the common log point with the primary database. This is an expected wait after failovers.Undo progress can be tracked through the Windows System Monitor (perfmon.exe) and dynamic management views.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_REPLICAINFO_SYNC
Waiting for concurrency control to update the current replica state.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_SYNC_COMMIT
Waiting for transaction commit processing for the synchronized secondary databases to harden the log. This wait is also reflected by the Transaction Delay performance counter. This wait type is expected for synchronized availability groups and indicates the time to send, write, and acknowledge log to the secondary databases.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_SYNCHRONIZING_THROTTLE
Waiting for transaction commit processing to allow a synchronizing secondary database to catch up to the primary end of log in order to transition to the synchronized state. This is an expected wait when a secondary database is catching up.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_TDS_LISTENER_SYNC
Either the internal AlwaysOn system or the WSFC cluster will request that listeners are started or stopped. The processing of this request is always asynchronous, and there is a mechanism to remove redundant requests. There are also moments that this process is suspended because of configuration changes. All waits related with this listener synchronization mechanism use this wait type. Internal use only.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_TDS_LISTENER_SYNC_PROCESSING
Used at the end of an AlwaysOn Transact-SQL statement that requires starting and/or stopping an availability group listener. Since the start/stop operation is done asynchronously, the user thread will block using this wait type until the situation of the listener is known.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_TIMER_TASK
Waiting to get the lock on the timer task object and is also used for the actual waits between times that work is being performed. For example, for a task that runs every 10 seconds, after one execution, AlwaysOn Availability Groups waits about 10 seconds to reschedule the task, and the wait is included here.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_TRANSPORT_DBRLIST
Waiting for access to the transport layer’s database replica list. Used for the spinlock that grants access to it.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_TRANSPORT_FLOW_CONTROL
Waiting when the number of outstanding unacknowledged AlwaysOn messages is over the out flow control threshold. This is on an availability replica-to-replica basis (not on a database-to-database basis).
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_TRANSPORT_SESSION
AlwaysOn Availability Groups is waiting while changing or accessing the underlying transport state.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_WORK_POOL
Concurrency control wait on the AlwaysOn Availability Groups background work task object.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_WORK_QUEUE
AlwaysOn Availability Groups background worker thread waiting for new work to be assigned. This is an expected wait when there are ready workers waiting for new work, which is the normal state.
Applies to: SQL Server 2012 through SQL Server 2014.

HADR_XRF_STACK_ACCESS
Accessing (look up, add, and delete) the extended recovery fork stack for an AlwaysOn availability database.
Applies to: SQL Server 2012 through SQL Server 2014.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Information About Waits Stats in SQL Server Waits – Part 5

SQLServerF1

SQL Server internally uses worker threads to execute SQL queries sent by various applications. While threads are executing they may be either running on CPU processing the request or would be waiting on a certain resource or waiting in waiting for its chance to run on CPU. SQL Server assigns a certain wait type to the worker thread that is waiting.

If a query is taking long time to complete, we can look at the query sessions wait type to get an understanding of what kind of resource it is the query waiting for and take appropriate action to avoid the queries to wait, thus making the queries complete fast.

Below are some of the SQL Server Wait Types related to Deadlock waits in SQL Server, Waits related to SQL Server Versioning.

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.

DEBUG
Occurs during Transact-SQL and CLR debugging for internal synchronization.

DISABLE_VERSIONING
Occurs when SQL Server polls the version transaction manager to see whether the timestamp of the earliest active transaction is later than the timestamp of when the state started changing. If this is this case, all the snapshot transactions that were started before the ALTER DATABASE statement was run have finished. This wait state is used when SQL Server disables versioning by using the ALTER DATABASE statement.

ENABLE_VERSIONING
Occurs when SQL Server waits for all update transactions in this database to finish before declaring the database ready to transition to snapshot isolation allowed state. This state is used when SQL Server enables snapshot isolation by using the ALTER DATABASE statement.

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.

DISPATCHER_QUEUE_SEMAPHORE
Occurs when a thread from the dispatcher pool is waiting for more work to process. The wait time for this wait type is expected to increase when the dispatcher is idle.

DLL_LOADING_MUTEX
Occurs once while waiting for the XML parser DLL to load.

DROPTEMP
Occurs between attempts to drop a temporary object if the previous attempt failed. The wait duration grows exponentially with each failed drop attempt.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Information About Waits Stats in SQL Server related to Database Mirroring waits and parallelism waits

SQLServerF1

SQL Server internally uses worker threads to execute SQL queries sent by various applications. While threads are executing they may be either running on CPU processing the request or would be waiting on a certain resource or waiting in waiting for its chance to run on CPU. SQL Server assigns a certain wait type to the worker thread that is waiting.

If a query is taking long time to complete, we can look at the query sessions wait type to get an understanding of what kind of resource it is the query waiting for and take appropriate action to avoid the queries to wait, thus making the queries complete fast.

Below are some of the SQL Server Wait Types related to Database Mirroring waits and parallelism waits.

CMEMTHREAD
Occurs when a task is waiting on a thread-safe memory object. The wait time might increase when there is contention caused by multiple tasks trying to allocate memory from the same memory object.

CXPACKET
Occurs with parallel query plans when trying to synchronize the query processor exchange iterator. 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.

DAC_INIT
Occurs while the dedicated administrator connection is initializing.

DBMIRROR_DBM_EVENT
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

DBMIRROR_DBM_MUTEX
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
– Although this is provided for informational purposes, this generally happens with database mirroring in both Synchronous and Asynchronous configuration. This some times can correlate with the huge amount of activity on the principal database or due to contention.

DBMIRROR_EVENTS_QUEUE
Occurs when database mirroring waits for events to process.

DBMIRROR_SEND
Occurs when a task is waiting for a communications backlog at the network layer to clear to be able to send messages. Indicates that the communications layer is starting to become overloaded and affect the database mirroring data throughput.

DBMIRROR_WORKER_QUEUE
Indicates that the database mirroring worker task is waiting for more work.

DBMIRRORING_CMD
Occurs when a task is waiting for log records to be flushed to disk. This wait state is expected to be held for long periods of time.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 
1 2 3