Information About Waits Stats in SQL Server Waits – Part 6


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 MSDTC waits.

Occurs when a task is waiting on an event that is used to manage state transition. This state controls when the recovery of Microsoft Distributed Transaction Coordinator (MS DTC) transactions occurs after SQL Server receives notification that the MS DTC service has become unavailable.
This state also describes a task that is waiting when a commit of a MS DTC transaction is initiated by SQL Server and SQL Server is waiting for the MS DTC commit to finish.

Occurs in a MS DTC worker session when the session is waiting to take ownership of a MS DTC transaction. After MS DTC owns the transaction, the session can roll back the transaction. Generally, the session will wait for another session that is using the transaction.

Occurs when a recovery task is waiting for the master database in a cross-database transaction so that the task can query the outcome of the transaction.

Occurs when a task is waiting on an event that protects changes to the internal MS DTC global state object. This state should be held for very short periods of time.

Occurs in a MS DTC worker session when SQL Server receives notification that the MS DTC service is not available. First, the worker will wait for the MS DTC recovery process to start. Then, the worker waits to obtain the outcome of the distributed transaction that the worker is working on. This may continue until the connection with the MS DTC service has been reestablished.

Occurs when recovery tasks wait for MS DTC to become active to enable the resolution of prepared transactions.

Occurs when a main task is waiting for a subtask to generate data. Ordinarily, this state does not occur. A long wait indicates an unexpected blockage. The subtask should be investigated.

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.

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 *