(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

During one of my last Online Trainings about Extended Events, someone asked me about the Service Broker Target that Extended Events offers. To be honest, I had really no idea about this. So I had to check that target out, because in combination with Service Broker this would be an awesome functionality.

As you can see from the following picture, it is quite easy to configure the Service Broker Target for Extended Events.

You just have to pass in the following configuration options:

• The database_id where the Initiator Service is defined
• initiator_service
• message_type
• service_contract
• target_service
• target_service_broker_guid (optional)

If you configure the Service Broker Target, you will get the following T-SQL code for the creation of your Extended Event Session.

CREATE EVENT SESSION TrackSortWarnings ON SERVER 
ADD EVENT sqlserver.sort_warning
ADD TARGET sqlserver.service_broker
(
SET database_id = 12,
initiator_service = 'MyCustomInitiatorService',
message_type = 'MyCustomMessageType',
service_contract = 'MyCustomServiceContract',
target_service = 'MyCustomTargetService'
)
GO

Everything seems perfect so far, but in reality the Service Broker Target doesn’t really work. I have tried different approaches, but the outcome was always the same: no XEvents were delivered into the Service Broker queue, and sys.transmission_queue also doesn’t show any transient messages.

After googling for some time, I came across the following Connect Item from January 2018, where someone also complains that the Service Broker Target doesn’t really work. For me it seems that Microsoft has slipped here the meta data about the Service Broker Target into the various DMVs, but the real functionality isn’t yet implemented – unfortunately.

To be fair, I also have to say that the Service Broker Target is not really documented in any place in Books Online. So if it’s not documented, it doesn’t has to work or to be supported. But maybe we will get in a subsequent SQL Server update a fully functional Service Broker Target for Extended Events. Who knows?

If the Service Broker Target will ever work with Extended Events, it would be also nice if the Initiator Service, the Message Type, and the Contract would be already built-in. To make things easy I only want to define a Target Service, and everything else should be provided out of the box by SQL Server…

Thanks for your time,

-Klaus