SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Service Broker Target for Extended Events

(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.

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

Klaus Aschenbrenner

Klaus Aschenbrenner provides independent SQL Server Consulting Services across Europe and the US. Klaus works with the .NET Framework and especially with the SQL Server 2005/2008 from the very beginnings. In the years 2004 - 2005 Klaus was entitled with the MVP award from Microsoft for his tremendous support in the .NET Community. Klaus has also written the book Pro SQL Server 2008 Service Broker which was published by Apress in the Summer of 2008. Further information about Klaus you can find on his homepage at http://www.SQLpassion.at. He also twitters at http://twitter.com/Aschenbrenner.

Comments

Leave a comment on the original post [www.sqlpassion.at, opens in a new window]

Loading comments...