Automatic Activation - Message is null/Monitoring of Readers
I have created a queue with automatic execution of a stored proc with the attribute Max_Queue_Readers = 5. While processing data, I can select from the queue and see that messages are backed up in the queue. I have two questions:
1) How do I monitor how many readers SQL is activating? I would like to at least confirm that SQL is indeed using 5 readers as messages back-up, and get some idea as to the overall resources being used.
2) I have noticed that after the RECEIVE TOP(1) command, occasionally the message is actually null! Not sure if this is to be expected or not. As it is automatically activated, I originally assumed that the recieve would always get a message. However, with 5 readers, perhaps another reader stole the message?
Here are the two relevant code samples:
Creation of the queue
CreateQUEUE CSPTokenQueuewith
ACTIVATION(
STATUS=ON,
PROCEDURE_NAME= CSP_TokenRecieveMsg,
MAX_QUEUE_READERS= 5,
ExecuteAS OWNER);
1) If you are simply interested in viewing a snapshot of how many activated tasks are currently running look at the sys.dm_broker_activated_tasks dynamic view. If you want to actually monitor how tasks are being started or stopped, you could use perfmon to view a variety of counters that the activation manager provides.
2) You may be receiving an empty result set because all the messages in the queue may be belonging to conversations whose conversation groups are locked. You are right, the lock could be held by other queue readers. To avoid activated tasks from going away immediately on receipt of an empty resultset, you could use WAITFOR (RECEIVE TOP(1)...).
In response to the issue of messages getting backed up.... remember that if the messages belong to the same conversation (or more generally speaking the same conversation group) then they will be serialized and only one queue reader can receive them at a time. The activation mechanism will not make additional queue readers available because they will not be able to help you in draining the queue.
 Remus Rusanu wrote: |
| 2) You don't receive a message (empty result set) or you receive a message with the message_body NULL? The later case is likely an end dialog message. You need to receive the message_type_name also, and act differently on error and end dialog messages. |
|
I seem to remember that (at least in earlier builds) the activation process was a bit "aggressive" and could fire even without any messages in the queue. In other words, in your activation proc, check that rowcount is greater than 0 after having done the receive, but before you process the message.
But yes, as Remus says, you should always check what message type you are recieving and be prepared to handle error messages/end dialog messages/conversation timer messages in your activation proc in addition to your specific message types.
Niels
