http://www.sqlservercentral.com/blogs/robert_davis/2011/01/18/GettingFastCountsofLargeServiceBrokerQueues/

Printed 2014/11/26 09:51AM

Getting Fast Counts of Large Service Broker Queues

By Robert Davis, 2011/01/18

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


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.