In advance of this week's PASS Summit, and surely dozens and dozens of announcements around SQL Server 2016, I thought I would share a tidbit of a feature that's been hidden in the CTPs for some time now, but that Microsoft hasn't had a chance to publicize: Additional maintenance operations available for Service Broker queues.
Remus Rusanu discussed the problems that fragmentation at high volume can cause for queues in this post:
There, he revealed that you could actually use DBCC REINDEXagainst the internal table, but you had to determine the internal table name, and connect via the DAC. Not exactly convenient.
Now, in SQL Server 2016, if you experience fragmentationproblems due to a high load, you can force index REORGANIZEor REBUILDoperations on queue internal tables by referencing the queue directly:ALTER QUEUE dbo.myQueue REORGANIZE;-- orALTER QUEUE dbo.myQueue REBUILD;
How do you know you have fragmentation in your queues, and how much? Well, queues have been added as a permissible object to pass to sys.dm_db_index_physical_stats, too:SELECT * FROM sys.dm_db_index_physical_stats ( DB_ID(), OBJECT_ID(N'dbo.QueryNotificationErrorsQueue'), -1, 0, 'SAMPLED' );
And you get similar output to the same interrogation of a regular table.
Additionally, you can move queue toanother filegroup (i.e. change the filegroup that was specified when queue was created). This operation will rebuild internal queue table and all indexes on new filegroup:ALTER QUEUE dbo.myQueue MOVE TO [MY_FILEGROUP];
These new capabilities should allow for greater scalability of Service Broker solutions.