SQL Server Management Studio Standard Reports –Service Broker Statistics: [DatabaseName]

SQL Server Management Studio Standard Reports –Service Broker Statistics: [DatabaseName]

Rate This
  • Comments 2

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

Leave a Comment
  • Please add 1 and 8 and type the answer here:
  • Post
Page 1 of 1 (2 items)