Information About Waits Stats in SQL Server In-Memory OLTP and Transactions 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 Hekaton related waits or In-Memory OLTP related waits, SQL Server Transactions related waits .

WAIT_FOR_RESULTS
Occurs when waiting for a query notification to be triggered.

WAIT_XTP_CKPT_CLOSE
Occurs when waiting for a checkpoint to complete.
Applies to: SQL Server 2014 through SQL Server 2014.

WAIT_XTP_CKPT_ENABLED
Occurs when checkpointing is disabled, and waiting for checkpointing to be enabled.
Applies to: SQL Server 2014 through SQL Server 2014.

WAIT_XTP_CKPT_STATE_LOCK
Occurs when synchronizing checking of checkpoint state.
Applies to: SQL Server 2014 through SQL Server 2014.

WAIT_XTP_GUEST
Occurs when the database memory allocator needs to stop receiving low-memory notifications.
Applies to: SQL Server 2014 through SQL Server 2014.

WAIT_XTP_HOST_WAIT
Occurs when waits are triggered by the database engine and implemented by the host.
Applies to: SQL Server 2014 through SQL Server 2014.

WAIT_XTP_OFFLINE_CKPT_LOG_IO
Occurs when offline checkpoint is waiting for a log read IO to complete.
Applies to: SQL Server 2014 through SQL Server 2014.

WAIT_XTP_OFFLINE_CKPT_NEW_LOG
Occurs when offline checkpoint is waiting for new log records to scan.
Applies to: SQL Server 2014 through SQL Server 2014.

WAIT_XTP_PROCEDURE_ENTRY
Occurs when a drop procedure is waiting for all current executions of that procedure to complete.
Applies to: SQL Server 2014 through SQL Server 2014.

WAIT_XTP_TASK_SHUTDOWN
Occurs when waiting for an In-Memory OLTP thread to complete.
Applies to: SQL Server 2014 through SQL Server 2014.

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.

WAIT_XTP_TRAN_DEPENDENCY
Occurs when waiting for transaction dependencies.
Applies to: SQL Server 2014 through SQL Server 2014.

XTPPROC_CACHE_ACCESS
Occurs when for accessing all natively compiled stored procedure cache objects.
Applies to: SQL Server 2014 through SQL Server 2014.

XTPPROC_PARTITIONED_STACK_CREATE
Occurs when allocating per-NUMA node natively compiled stored procedure cache structures (must be done single threaded) for a given procedure.
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.

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.

 

Leave a Reply

Your email address will not be published. Required fields are marked *