Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

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


I’m looking for tips or pointers on how to optimize for reading the count of records in an SSB queue. We’re seeing as the queue grows to 1M records a simple SELECT COUNT(*) WITH(NOLOCK) is taking an inordinate amount of time – i.e. 30-50 minutes under load.

Any pointers or suggestions?

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

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.