How to Improve Service Broker performance

In recent past, one of the reader emailed and asked me a question – How to Improve Service Broker performance? Let us first understand what is Service Broker? In simple words, service broker is a mechanism which enables SQL Server instances to talk to each other, send and receive messages, these messages can also be queued. This is very helpful in case one wants to send information to database(s) residing on another server. In case you want to read more about Service Broker basics in simple word my friend Pinal Dave has written a nice blog post here.

The question was how to improve Service Broker performance ? Service Broker stores the messages inside of some internal tables. You can use below query to get list of the internal tables and it’s index details using below script.

SELECT 
QueueName = SSQ.NAME, 
SchemaName = SS.NAME, 
NumberofRows = SP.ROWS,
InternalTableName = SIT.NAME, 
IndexId = SI.INDEX_ID,
IndexName = SI.NAME,
QueueId = SSQ.OBJECT_ID
FROM 
  SYS.PARTITIONS SP
  JOIN 
  SYS.INTERNAL_TABLES SIT
    ON SIT.OBJECT_ID = SP.OBJECT_ID
    INNER JOIN SYS.INDEXES SI	
    ON SIT.OBJECT_ID = SI.OBJECT_ID
  INNER JOIN SYS.SERVICE_QUEUES SSQ
    ON SIT.PARENT_OBJECT_ID = SSQ.OBJECT_ID
  INNER JOIN SYS.SCHEMAS SS
    ON SIT.SCHEMA_ID = SS.SCHEMA_ID

What one has to do is pickup the busiest Service Broker Queue from the list and then fire up the usual command that is used for Index Maintenance. Yes, that’s right.

DBCC DBREINDEX('sys.queue_message_someid')

Here is what one has to remember though, these are the internal table, and the REINDEX will not work with normal connection, so, use Dedicate Administrator Connection aka DAC and one will be able to do work on these internal tables. So this is the answer on How to Improve Service Broker performance.

Do let me know if you have heard or read otherwise, also, do let me know about the TSQL Script, if it works for you.

photo credit: jaroslavd Queue for ‘A Subtlety’ via photopin (license)