Getting Fast Counts of Large Service Broker Queues

Getting Fast Counts of Large Service Broker Queues

This question regarding getting a fast count from a service broker queue came in through a discussion group.

Question: Fast way to get a count of records from an SSB queue

My Solution

Service broker queues are stored as internal tables. The queue is listed as the parent object of the hidden table. By linking the internal table to sys.partitions, you can use the sys.partitions.rows column as a quick count bearing in mind that it may not always be 100%. The accuracy is probably not an issue for you if you are using the nolock already as that may count records that have not been committed yet.

Select rows
From sys.partitions P
Inner Join sys.internal_tables IT On IT.object_id = P.object_id
Where IT.parent_object_id = object_id('<SB Queue Name>')
And P.index_id In (1, 0)

Edit: Another Version of Query

In follow-up conversation, I wrote a version of the query that can be used to query the count of service broker queues in a different database. Since the OBJECT_ID funtion does not accept a database ID parameter like OBJECT_NAME does, we instead need to join to the sys.service_queues view to link the queue to the internal table. For the below example query, I am querying for a service broker queue in msdb.

Select P.rows
From msdb.sys.partitions P
Inner Join msdb.sys.internal_tables IT On IT.object_id = P.object_id
Inner Join msdb.sys.service_queues SQ On SQ.object_id = IT.parent_object_id
Where SQ.name = '<Queue name>'
And P.index_id In (1, 0)