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.

 

Leave a Reply

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