Aaron Bertrand

Service Broker Enhancements in SQL Server 2016

SentryOne eBooks

In these books, you will find useful, hand-picked articles that will help give insight into some of your most vexing performance problems. These articles were written by several of the SQL Server industry’s leading experts, including Paul White, Paul Randal, Jonathan Kehayias, Erin Stellato, Glenn Berry, Aaron Bertrand, and Joe Sack.

Free Download

Featured Author

Jonathan Kehayias is a Principal Consultant with SQLskills and the youngest MCM ever.

Jonathan’s Posts

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:

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.