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
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)