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.

This post was cross-posted from the main SQLSoldier blog. Please visit the source blog to read the whole post and leave a comment: http://www.sqlsoldier.com/wp/sqlserver/gettingfastcountsoflargeservicebrokerqueues