Sql-server – service broker target for an extended event session

extended-eventsservice-brokersql serversql-server-2016sql-server-2017

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?

Best Answer

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
Related Question