Understanding Queue Monitors

A major class of Service Broker applications have nothing to do with the distributed application capabilities SSB was designed for. Instead they leverage SSB’s capability to run a stored procedure outside the context of the user connection. This capability enables to schedule execution and don’t wait for it to finish, or to schedule multiple procedures to execute in paralel. Often the developers that use these capabilities don’t care for the reliable message delivery SSB offers and many see the complex infrastructure of message types, contracts and services as a mere hurdle. Nonetheless sooner or later they have to troubleshoot an issue and then is when many of you find my blog and my articles. Troubleshooting activated procedure is not difficult, but one simply has to know where to look and what to look for.

In this entry I want to shed some light in the soul that drives the activation process: the Queue Monitors.

A Queue Monitor is a state machine that resides in the SQL Server and decides when to launch a new instance of activated procedure into execution. The sys.dm_broker_queue_monitors DMV is used to view the Queue Monitors:

This DMV is described in BOL at http://msdn.microsoft.com/en-us/library/ms177628.aspx where the columns types and decriptions can be found.

There is a Queue Monitor for each queue with activation enabled in the SQL Server instance. The DMV does not contain the database name nor the queue name, but you can join with sys.service_queues on queue_id to get the queue names. Note that even when you run this query on a brand new install of SQL Server you will get two Queue Monitors: InternalMailQueue and ExternalMailQueue. These are the queues placed in msdb used by the Database Mail feature of SQL Server.

Queue Monitor States

To start troublesooting an activation problem you need to understand the Queue Monitor states:

• INACTIVE: there are no messages available to be received in the monitored queue.
• NOTIFIED: messages are available and the activated procedure was launched into execution.
• RECEIVES_OCCURING: the activated procedure is issuing RECEIVE command against the monitored queue.

The important thing to notice here is that the Queue Monitor will go into NOTIFIED state when an activated procedure was launched and will not launch again any procedure until a RECEIVE statement is issued against the monitored queue. This is done in order to prevent situations when a queue is incorrectly configured and launches the wrong stored procedure into execution. Altough in practice is the activated procedure that is expected to execute the RECEIVE statement, this is not enforced by the Queue Monitor. Any connection that executes a RECEIVE while the Queue Monitor is in NOTIFIED state will cause the Queue Monitor to enter the RECEIVES_OCCURING state.

This activation state machine also handles sending the QUEUE_ACTIVATION events for Event-Based Activation. The only difference that instead of launching a stored procedure into execution the Queue Monitor sends a message of type http://schemas.microsoft.com/SQL/Notifications/EventNotification to the notification subscriber. Again, same as with internal activation, once the notification is sent the Queue Monitor enters the NOTIFIED state and will not send more notifications messages until a RECEIVE statement is executed on the monitored queue.

The Queue Monitor will go back to INACTIVE state when there are no more messages ready to be received in the queue.

Queue Monitor Timers

The Queue Monitors are also responsible for handling the activation ramp up for handling a higher load by launching more instances of the activated procedure. Once the Queue Monitor enters the RECEIVES_OCCURING state (ie. a RECEIVE statement is issued) it will start an internal timer. Each time a RECEIVE statement on the monitored queue returns an empty rowset this timer is reset. If this timer ever reaches a threshold then a new instance of the activated procedure is launched. The reasoning behind this mechanism is that activated procedures are supposed to issue RECEIVE statements and process the received messages. If RECEIVE does not find any available messages and returns an empty rowset the activation timer is reset because it means that there are no messages available for receive, so there is no reason to launch a new instance of the procedure. The timer threshold is 5 seconds and currently is hardcoded.

This timers are also used for sending new QUEUE_ACTIVATION event messages if the Event-Based Activation is used instead of internal activation.

Troubleshooting

If you find yourself looking at Queue Monitors in sys.dm_broker_queue_monitors is usualy because of one reason: your activated procedure is failing to launch.

If there is no record for your queue in sys.dm_broker_queue_monitors then this indicates that the Service Broker activation is not even monitoring the queue. For activation to monitor the queue several settings have to be properly configured:

• The queue has to be enabled. Check is_receive_enabled and is enqueue_enabled in sys.service_queues.
• Activation has to be enabled on the queue. Check is_activation_enabled in sys.service_queues.
• Activation has to be properly configured. Check activation_procedure, max_queue_readers and execute_ss_principal_id in sys.service_queues.
• The Service Broker in the database has to be enabled. Check is_broker_enabled in sys.databases.

If there is a record for your queue in sys.dm_broker_queue_monitors and its state is INACTIVE then there are no messages in the queue ready to be received. Check the content of the queue with a SELECT * FROM [<your_queue_name>]. Messages ready to be received have to be unlocked and the status has to be 1 ready. Note that the BOL documentation of RECEIVE erroneously shows the state 0 as ready, which is incorrect.

If the Queue Monitor for your queue is in NOTIFIED and it stays in that state is an indication that your activated procedure is not issuing the correct RECEIVE statement. Check your activated procedure. Note that if you change the procedure the Queue Monitor will not detect this change and launch again the procedure. The easiest thing to do is to recycle the Queue Monitor by disabling and then enabling activation on the queue:

ALTER QUEUE [<your_queue>] WITH ACTIVATION (STATUS = OFF);
ALTER QUEUE [<your_queue>] WITH ACTIVATION (STATUS = ON);

Finally if the Queue Monitor for your queue is in the state RECEIVES_OCCURING then it indicates that the RECEIVE statement is being issued against the queue. Make sure your assesment that the procedure is not being launched is correct. Check if there are other applications or procedures that are erroneously receiving from your queue.

Demo Caution

I will end with a cautionary tale. I was at a presentation where the speaker was showing a very nice monitoring facility he had built around the event notifications for DDL that he was using to monitor a farm of SQL Server instances. As he was making his way through the demo, he showed how it attaches a stored procedure to the queue to monitor for the notification messages. But in order to not perturb the presentation flow (at moment focused on setting up activation) he did not create the whole procedure, just a stub for it. Something like this:

CREATE PROCEDURE usp_NotificationProcessor
AS
BEGIN
--TODO: add code here
END;


The speaker then continued with his demo and attached the procedure to the queue for activation. Now since he had already created the subscription for all DDL events on the demo database, his queue was receiving the notification messaged as he was creating and altering objects in the database.

He finaly reaches the point when he changes the stub procedure to actually process the notifications and then the audience eagerly awaits the results. And nothing happens.

Can you guess what was wrong? Go back and read this for a hint.

Since activation was enabled and there were messages to received, the Queue Monitor has launched the procedure altough it was nothing but a stub. The Queue Monitor has entered the NOTIFIED state and would not launch again the procedure until a RECEIVE was issued. Later when the stub procedure was changed to do the actual processing, the procedure would not be launched by the Queue Monitor because it would still wait for RECEIVE. This can be easily fixed by turning activation on the queue off and back on, as I showed before.