1

In SQL Server Extended Events, is there any way of linking a "starting" event, such as rpc_starting, with a "completed" event, such as rpc_completed? For example, is there anything like a correlation ID which would uniquely identify a call to SQL Server?

I'd like to query the recorded events, to get the start time of a call, from rpc_starting, and its duration, from rpc_completed. So far I haven't found any way to uniquely link the start and end of the same call.

I thought the request_id action might work but it seems to come out as 0 for the events I've looked at.

2 Answers 2

6

Causality tracking can do that for you, below is an example:

CREATE EVENT SESSION TestCausality
ON SERVER
    ADD EVENT sqlserver.auto_stats,
    ADD EVENT sqlserver.sql_batch_completed,
    ADD EVENT sqlserver.sql_batch_starting,
    ADD EVENT sqlserver.sql_statement_completed,
    ADD EVENT sqlserver.sql_statement_recompile,
    ADD EVENT sqlserver.sql_statement_starting
    ADD TARGET package0.event_file
    (SET filename = N'TestCausality')
WITH
(
    TRACK_CAUSALITY = ON
);

After the event is created I run a simple TSQL just as an example:

SELECT TOP 10 * FROM dbo.CommandLog

Then in the GUI of your extended event view you can add "Attach_Activity_Id.GUID", with this you can link your statements to each other. Looks like this: Causality SQL Server

1
  • I've had a play with this. It's not perfect, since multiple statements can have the same attach_activity_Id.guid. However, I think it's good enough to be able to find the completed event for a given starting event.
    – Simon Elms
    Commented Dec 20, 2023 at 9:51
1

You could get the start time by subtracting the duration from the timestamp so you don't need to collect the rpc_starting events. Other than that, see Yannick's suggestion to use TRACK_CAUSALITY=ON.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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