Information About Waits Stats in SQL Server Transaction Log Latency 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 log latency related waits, SQL transaction related waits and MISCELLANEOUS SQL Server wait.

LOGBUFFER
Occurs when a task is waiting for space in the log buffer to store a log record. Consistently high values may indicate that the log devices cannot keep up with the amount of log being generated by the server.

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

LOGMGR
Occurs when a task is waiting for any outstanding log I/Os to finish before shutting down the log while closing the database.

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

LOGMGR_QUEUE
Occurs while the log writer task waits for work requests.

LOGMGR_RESERVE_APPEND
Occurs when a task is waiting to see whether log truncation frees up log space to enable the task to write a new log record. Consider increasing the size of the log file(s) for the affected database to reduce this wait.

LOWFAIL_MEMMGR_QUEUE
Occurs while waiting for memory to be available for use.

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

MSQL_DQ
Occurs when a task is waiting for a distributed query operation to finish. This is used to detect potential Multiple Active Result Set (MARS) application deadlocks. The wait ends when the distributed query call finishes.

MSQL_XACT_MGR_MUTEX
Occurs when a task is waiting to obtain ownership of the session transaction manager to perform a session level transaction operation.

MSQL_XACT_MUTEX
Occurs during synchronization of transaction usage. A request must acquire the mutex before it can use the 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 *