Information About Waits Stats in SQL Server Waits – Part 2

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 Service Broker waits.

BAD_PAGE_PROCESS
Occurs when the background suspect page logger is trying to avoid running more than every five seconds. Excessive suspect pages cause the logger to run frequently.

BROKER_CONNECTION_RECEIVE_TASK
Occurs when waiting for access to receive a message on a connection endpoint. Receive access to the endpoint is serialized.

BROKER_ENDPOINT_STATE_MUTEX
Occurs when there is contention to access the state of a Service Broker connection endpoint. Access to the state for changes is serialized.

BROKER_EVENTHANDLER
Occurs when a task is waiting in the primary event handler of the Service Broker. This should occur very briefly.

BROKER_INIT
Occurs when initializing Service Broker in each active database. This should occur infrequently.

BROKER_MASTERSTART
Occurs when a task is waiting for the primary event handler of the Service Broker to start. This should occur very briefly.

BROKER_RECEIVE_WAITFOR
Occurs when the RECEIVE WAITFOR is waiting. This is typical if no messages are ready to be received.

BROKER_REGISTERALLENDPOINTS
Occurs during the initialization of a Service Broker connection endpoint. This should occur very briefly.

BROKER_SERVICE
Occurs when the Service Broker destination list that is associated with a target service is updated or re-prioritized.

BROKER_SHUTDOWN
Occurs when there is a planned shutdown of Service Broker. This should occur very briefly, if at all.

BROKER_TASK_STOP
Occurs when the Service Broker queue task handler tries to shut down the task. The state check is serialized and must be in a running state beforehand.

BROKER_TO_FLUSH
Occurs when the Service Broker lazy flusher flushes the in-memory transmission objects to a work table.

BROKER_TRANSMITTER
Occurs when the Service Broker transmitter is waiting for work.

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 *