Script to analyse space usage by your Service Broker

By Art on April 16, 2012

I believe that service broker is one of the best features from SQL server 2005, well along with the xml data type, and I use service broker a lot for messaging between solutions. Over the years it has proven to be extremely reliable; only really stopping because of a poison message or other environment type issue outside the control of the broker.

Service broker itself uses a number of internal tables which it uses to store details regarding conversations and routing, and there are also any number of user defined message queue tables as well of which any of these can rapidly fill up with data if a problem goes unchecked.

When I see a potential problem with service broker within one of my databases, or if I just want to monitor the space being used by the broker in a particular database then one of the first scripts I reach for is the following which returns the amount of space allocated to all of the broker internal tables and message queues and the number of rows in all of those tables. This information can quickly highlight any areas of concern which can then be followed up by further investigations.

I use the script in two ways: Firstly, it is part of my monitoring set of scripts to alert me when some tables reach a threshold and secondly, as a starting point to diagnose any reported issues relating to the service broker.

WITH xCTE ([ObjectName], [PartitionId], [Rows], [Type]) AS
    (
        --Get the partition information for all internal tables that we are interested in
        SELECT so.name, p.partition_id, p.row_count, so.type
        FROM sys.objects so
        LEFT JOIN sys.dm_db_partition_stats p ON p.object_id = so.object_id
        WHERE so.name IN ('sysdercv', 'sysdesend', 'sysxmitqueue', 'sysconvgroup', 'sysremsvcbinds')
            AND p.index_id = 1 --Only care about clustered index

        UNION ALL

        --Get the partion information for all the service queues in the db
        SELECT so.name, p.partition_id, p.rows, so.type
        FROM sys.objects so
        LEFT JOIN sys.objects so2 ON so.object_id = so2.parent_object_id
        LEFT JOIN sys.partitions p ON p.object_id = so2.object_id
        WHERE so.type='SQ'     --type "SQ" = Service Queue
            AND p.index_id = 1    --Only care about clustered index
            AND so.is_ms_shipped = 0    --Do not care about MS shipped broker queues
    )
    SELECT ObjectName, Type
        , CAST((reserved_page_count * 8.0)/1024.0 AS DECIMAL(10, 2)) AS 'Reserved Space (mb)'
        , CAST((used_page_count * 8.0)/1024.0 AS DECIMAL(10, 2)) AS 'Used Space (mb)'
        , [Rows] as 'Rows'
    FROM xCTE x
    LEFT JOIN sys.dm_db_partition_stats s ON x.PartitionId = s.partition_id
    ORDER BY 'Reserved Space (mb)' DESC

The script returns a small number of internal tables and any number of service queues. I’ve used a UNION to merge the data into one recordset as I prefer it that way and fits in with my monitoring, but you could quite easily break the above into two seperate queries, one each for the internal tables and service queues.

Excessive rows/space allocated in the internal tables could indicate a problem with conversations not being ended properly or queues going offline because of a poison message for example. If you are seeing excessive rows then the next step is to start digging a bit deeper using the sys.conversation_endpoints and sys.transmission_queue catalog views.

Enjoy!