1

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?

2

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
4
  • Thanks a lot! The Broker works well, but the extended event session doesn't seems to send events to the service broker target. – Christian Gräfe Jan 30 '18 at 13:59
  • @ChristianGräfe, right, it seems some artifacts of the feature exist but it is obviously not documented for a reason. I think it would be best to ignore it for now and hope to see the target functional in a upcoming CU or version. – Dan Guzman Jan 31 '18 at 1:39
  • Thanks a lot fpr your investigation, I will file a bug on connect! :-) – Christian Gräfe Jan 31 '18 at 7:29
  • 1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.