We use Service Broker heavily for asynchronous background processing. Additionally we want to use it for extended event processing.
Is it possible to use Service Broker listener as target for an extended event session?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
There looks to be a
service_broker target in SQL Server 2017 but I can't find any documentation on it. This target is visible with the latest SSMS GUI and this query:
SELECT * FROM sys.dm_xe_objects WHERE object_type='target' AND name = N'service_broker';
Without documentation, I experimented using the script below but could not capture any events and saw no SB activity. The execution_count in sys.dm_xe_session_targets increments so it seems the plumbing is there. It could be an issue with my script or the target might not be fully implemented by SQL Server.
I'll see if I can find more info about the service broker target and report back here.
/* DROP EVENT SESSION [service_broker_target_test] ON SERVER DROP SERVICE InitiatorService; DROP SERVICE TargetService; DROP CONTRACT ExtendedEventMessageContract; DROP MESSAGE TYPE ExtendedEventMessageType; DROP QUEUE InitiatorQueue; DROP QUEUE TargetQueue; */ CREATE QUEUE InitiatorQueue; CREATE QUEUE TargetQueue; CREATE MESSAGE TYPE ExtendedEventMessageType VALIDATION = NONE; CREATE CONTRACT ExtendedEventMessageContract ( ExtendedEventMessageType SENT BY ANY ); CREATE SERVICE InitiatorService ON QUEUE dbo.InitiatorQueue (ExtendedEventMessageContract); CREATE SERVICE TargetService ON QUEUE dbo.TargetQueue (ExtendedEventMessageContract); GO CREATE EVENT SESSION service_broker_target_test ON SERVER ADD EVENT sqlserver.rpc_completed(SET collect_statement=(1)) ADD TARGET sqlserver.service_broker(SET database_id=(14) ,initiator_service=N'InitiatorService' ,message_type=N'ExtendedEventMessageType' ,service_contract=N'ExtendedEventMessageContract' ,target_service=N'TargetService') WITH ( MAX_MEMORY=4096 KB ,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS ,MAX_DISPATCH_LATENCY=30 SECONDS ,MAX_EVENT_SIZE=0 KB ,MEMORY_PARTITION_MODE=NONE ,TRACK_CAUSALITY=OFF, STARTUP_STATE=OFF); GO ALTER EVENT SESSION service_broker_target_test ON SERVER STATE = START; GO SELECT * FROM sys.conversation_endpoints; SELECT * FROM dbo.InitiatorQueue; SELECT * FROM dbo.TargetQueue; SELECT * FROM sys.dm_xe_session_targets WHERE target_name = 'service_broker'; GO