Aaron Bertrand

Service Broker Enhancements in SQL Server 2016

Free eBook on Mastering Query Tuning with SentryOne Plan Explorer
SentryOne Newsletters

The SQLPerformance.com bi-weekly newsletter keeps you up to speed on the most recent blog posts and forum discussions in the SQL Server community.

eNews is a bi-monthly newsletter with fun information about SentryOne, tips to help improve your productivity, and much more.

Subscribe

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.