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

XE_BUFFERMGR_ALLPROCESSED_EVENT
Occurs when Extended Events session buffers are flushed to targets. This wait occurs on a background thread.

XE_BUFFERMGR_FREEBUF_EVENT
Occurs when either of the following conditions is true:
An Extended Events session is configured for no event loss, and all buffers in the session are currently full. This can indicate that the buffers for an Extended Events session are too small, or should be partitioned.
Audits experience a delay. This can indicate a disk bottleneck on the drive where the audits are written.

XE_DISPATCHER_CONFIG_SESSION_LIST
Occurs when an Extended Events session that is using asynchronous targets is started or stopped. This wait indicates either of the following:
An Extended Events session is registering with a background thread pool.
The background thread pool is calculating the required number of threads based on current load.

XE_DISPATCHER_JOIN
Occurs when a background thread that is used for Extended Events sessions is terminating.

XE_DISPATCHER_WAIT
Occurs when a background thread that is used for Extended Events sessions is waiting for event buffers to process.

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

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

XE_PACKAGE_LOCK_BACKOFF
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.

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 *