Tag: XACTWORKSPACE_MUTEX

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.

WAIT_XTP_TRAN_COMMIT
Occurs when execution of a natively compiled stored procedure is waiting for an XTP transaction to commit (waiting for transactions dependent on for instance).
Applies to: SQL Server 2014 through SQL Server 2014.

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.

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

WAITSTAT_MUTEX
Occurs during synchronization of access to the collection of statistics used to populate sys.dm_os_wait_stats.

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

WORKTBL_DROP
Occurs while pausing before retrying, after a failed worktable drop.

WRITE_COMPLETION
Occurs when a write operation is in progress.

WRITELOG
Occurs while waiting for a log flush to complete. Common operations that cause log flushes are checkpoints and transaction commits.

XACT_OWN_TRANSACTION
Occurs while waiting to acquire ownership of a transaction.

XACT_RECLAIM_SESSION
Occurs while waiting for the current owner of a session to release ownership of the session.

XACTLOCKINFO
Occurs during synchronization of access to the list of locks for a transaction. In addition to the transaction itself, the list of locks is accessed by operations such as deadlock detection and lock migration during page splits.

XACTWORKSPACE_MUTEX
Occurs during synchronization of defections from a transaction, as well as the number of database locks between enlist members of a transaction.