SQL Server Management Studio Standard Reports –Service Broker Statistics: [DatabaseName] - Carpe Datum - Site Home

I'm continuing my series on the Standard Reports in SQL Server Management Studio, and today we’re in the database reports. You can find these reports by starting SQL Server Management Studio and right-clicking a database name. From the menu that appears, click the Standard Reports, and then select the title at the top of this post.

If you use the Service Broker, you know that monitoring the process is essential. If you’re new to the service Broker, you can read more about it here. To monitor service Broker, you normally use a lot of stored procedures and functions. This report is indispensable to that process, since it runs those commands for you and tracks them historically.

The first section of this report has the Service Broker ID, and the Status of the feature.

The second section of the report has a set of graphs that shows the following information:

The # Conversations shows the number of conversations on the database
The # Messages In Queues shows a graphical breakout of the types of messages in the queues on the database
The # Messages Pending in Transmission Queue shows a graph of the messages that are waiting to be transmitted – possibly one of the most important bits on the screen.

The first band of information is Services, grouped by the Service Name:

Column	Description
Service Name	The http reference for the service on this database
Owner	The SQL Server Principal that owns this service
Queue Name	The name of this particular queue
# Total Messages(In Service Queue)	The total number of messages in any state in this service’s queue
# Messages (Ready)	The number of messages in the queue that are ready to be sent.
# Messages (Received Retained)	The number of messages received by this service that have been received and not deleted
# Messages (Not Complete)	Messages that are in-progress
# Messages (Sent Retained)	The number of messages that have been sent by this service and not deleted
# Messages (Sent Pending)	The number of messages sent by this service that have been sent and are in a Pending state
Conversation Group ID	Identifier for the conversation group. Not NULLABLE.
Expiration Date	The expiration date of the message
Transmission Status	The reason this message is on the queue. This is generally an error message explaining why sending the message failed. If this is blank, the message hasn’t been sent yet. NULLABLE.

The second band of information is Queues, grouped by the queue Name:

Column	Description
Name	The name of the Queue
Status	The current status of the Queue
Retention	Maximum time for the message to remain active. When this reaches 0, the message is discarded. NULLABLE.
# Total Messages	The total number of messages in this queue
# Messages (Ready)	The total number of messages in this queue that are ready to be sent
# Messages (Received)	The total number of messages in this queue that have been received
# Messages (Not Complete)	The total number of messages in this queue that are still in transmission
# Messages (Retained Sent)	The total number of messages in this queue sent and not deleted from the queue
Activation	The type of activation
Activation Procedure	Name of the activated stored procedure. NULLABLE.
Max. Readers	The maximum number of readers associated with this queue
State	The current state of the queue
# Tasks Waiting	The number of messages and operations waiting to be executed – the “queue of the queue”
Last Time When Queue Was Empty	Timestamp of the last time the queue had no messages
Last Time When Queue Was Active	Timestamp of the last time when the queue was working
Procedure Name	The name of the procedure associated with this queue
CPU Time (ms.)	The amount of CPU time in milliseconds used by this queue
Memory Usage (KB)	The amount of memory used by this queue
# Reads	The number of read operations associated with this queue
# Writes	The number of write operations associated with this queue
Execute as	The SQL Server Principal this queue operates under

The final band of information is a simple chart that shows the overall statistics for the Task Activations:

Column	Description
Stored Procedures Invoked/sec	The number of stored procedures that the Service Broker is using each second
Task Limit Reached	How many times the task limit has been reached by Service Broker
Task Limit Reached/sec	The number of times the task limit has been reached by Service Broker each second
Tasks Aborted/sec	The number of tasks aborted by Service Broker each second
Tasks Running	The number of Service Broker tasks that are currently running
Tasks Started/sec	The number of Service Broker tasks that are started each second