Information About Waits Stats in SQL Server related to Database Mirroring waits and parallelism waits


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.

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.

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.

Occurs during a parallel range scan.

Occurs while the dedicated administrator connection is initializing.

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

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.

Occurs when database mirroring waits for events to process.

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.

Indicates that the database mirroring worker task is waiting for more work.

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.

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 *