Service Broker Enhancements in SQL Server 2016

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 (@rusanu) discussed the problems that fragmentation at high volume can cause for queues in this post:

Dealing with Large Queues

There, he revealed that you could actually use DBCC REINDEX against the internal table, but you had to determine the internal table name, and connect via the DAC. Not exactly convenient.

Now, almost six years later, if you believe you are experiencing fragmentation problems due to high load, you can force index REORGANIZE or REBUILD operations against the queue's internal table by referencing the queue directly:

ALTER QUEUE dbo.myQueue REORGANIZE;
-- or
ALTER QUEUE dbo.myQueue REBUILD;

How do you know how much fragmentation you have in a queue? 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 the queue to another filegroup; this operation will rebuild the internal queue table and all of its indexes on the new filegroup:

ALTER QUEUE dbo.myQueue MOVE TO [MY_FILEGROUP];

These new capabilities should allow for greater scalability of Service Broker solutions.

Service Broker Enhancements in SQL Server 2016

1 thought on “Service Broker Enhancements in SQL Server 2016”