Just another WordPress.com site
We have an audit application which database uses service broker to receive the audit messages. The system handling a large number of audit messages per second from each device and as time grows more device were registered to application and in the meantime the message transfer rate to broker also grown exponentially.
Dealing with an application with high volume of message, the service broker queue can grow a very large. Reading or deleting messages from the queues also showing a large number, so obviously there could be high number of fragmentations can present to these queues table which may slow down the read and delete operation to the queue.
We also have noticed that sometimes messages receive processing hit a threshold from where it could not recover. The queue processing rate slow down below the incoming rate, as a result queue start growing.
Queues are hidden tables and there is neither ALTER QUEUE … REBUILD nor ALTER QUEUE … REORGANIZE statement can be execute to these hidden tables. But if we login as a DAC to the SQL server then we will able to view these hidden tables .Once we are connected by DAC the following query returns the name of the internal table that backs each Service Broker queue in the database.
Great!!! with DAC connection we can reindex Service Broker queue internal table.
Here is the scripts we use in our environment to rebuild the queues internal tables. We rebuild only the queues has more than 200 rows. But this number can be change depending on the environment.