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.
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.