Fast way to check message count

A fast way to check the count of messages in a queue is to run this query is to look at the row count of the underlying the b-tree that stores the messages:

select p.rows

from sys.objects as o

join sys.partitions as p on p.object_id = o.object_id

join sys.objects as q on o.parent_object_id = q.object_id

where q.name = ‘<queuename>’

and p.index_id = 1

This query is significantly faster than running a COUNT(*), specially on a busy system where many rows may be locked. In fact this can be used to retrieve also other counts fast:

Messages pending in transmission_queue:
select p.rows

from sys.objects as o

join sys.partitions as p on p.object_id = o.object_id

where o.name = ‘sysxmitqueue’

Total number of conversations in the database:

select p.rows

from sys.objects as o

join sys.partitions as p on p.object_id = o.object_id

where o.name = ‘sysdesend’

Note that all these will return a raw count, w/o concerning the state of individual messages or conversations. E.g if retention is turned on a queue, the retained messages will also be counted. The partition rows count is not guaranteed to match the actual count returned by a COUNT(*) operator, but the situations where it diverges are really transient border cases unlikely to be hit. Besides, most of the times such counts are interesting for monitoring and management, when the exact result is not so much important as to whether to determine if certain thresholds are being passed and notifications have to be sent (e.g. when the count of messages in transmission_queue is growing).