Information About Waits Stats in SQL Server Transaction Synchronization 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 Synchronization related waits.

TRAN_MARKLATCH_DT
Occurs when waiting for a destroy mode latch on a transaction mark latch. Transaction mark latches are used for synchronization of commits with marked transactions.

TRAN_MARKLATCH_EX
Occurs when waiting for an exclusive mode latch on a marked transaction. Transaction mark latches are used for synchronization of commits with marked transactions.

TRAN_MARKLATCH_KP
Occurs when waiting for a keep mode latch on a marked transaction. Transaction mark latches are used for synchronization of commits with marked transactions.

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

TRAN_MARKLATCH_SH
Occurs when waiting for a shared mode latch on a marked transaction. Transaction mark latches are used for synchronization of commits with marked transactions.

TRAN_MARKLATCH_UP
Occurs when waiting for an update mode latch on a marked transaction. Transaction mark latches are used for synchronization of commits with marked transactions.

TRANSACTION_MUTEX
Occurs during synchronization of access to a transaction by multiple batches.

UTIL_PAGE_ALLOC
Occurs when transaction log scans wait for memory to be available during memory pressure.

VIA_ACCEPT
Occurs when a Virtual Interface Adapter (VIA) provider connection is completed during startup.

VIEW_DEFINITION_MUTEX
Occurs during synchronization on access to cached view definitions.

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 *