Causality Tracking in Extended Events

If you go through all the stuff I’ve written about Extended Events, you’ll find that I use causality tracking quite a bit. However, I’ve never just talked about what causality tracking is and why I use it so frequently. Let’s fix that issue now.

Causality Tracking

Causality tracking is quite simple to understand. It’s property that you set for a given session. A session, of course, is defined by one or more events and a target. You can define things about a session, like it’s name, when you define the session itself. Turning on, or enabling, causality tracking is just a matter of defining that the session will have causality tracking. It looks like this in the GUI:

It looks like this in the T-SQL code:

CREATE EVENT SESSION QueryBehavior
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'QueryBehavior')
WITH
(
    TRACK_CAUSALITY = ON
);

So, then what happens? It’s simple. A given task, let’s say, for example, an INSERT query, will be given a GUID. Then, all the events associated with that task, from the compile, to the query start, query finish, waits, recompiles, etc., all get associated with that GUID. They also receive a sequence number.

Therefore, through the use of causality tracking, you can see all the behavior associated with a given task and the order in which it occurred. On a test system, with no load and no activity, this isn’t that big a deal. On a real system under load, whether we’re talking, dev, test or production, where you’re going to see tons of simultaneous tasks occurring. Causality tracking enables you to isolate everything and group by task. You can pluck one set of behavior out of the mess.

That’s a giant win.

I’d like to point out right here, for those still on #teamprofiler, you can’t do this in Profiler. You can’t do anything even approaching this in Profiler. This is next level behavior, unique to Extended Events.

Causality Tracking in Action

Let’s assume that I have the session define above running. Let’s take a simple example query and look at the output:

BEGIN TRAN;

UPDATE Person.Address
SET City = 'Fornebu';

SELECT a.AddressID,
       a.AddressLine1,
       a.AddressLine2
FROM Person.Address AS a
WHERE a.City = N'Fornebu'; -- nvarchar(30)

ROLLBACK TRAN;

Obviously, all the data in the address table will get changed. This means that the statistics will need to be updated and we’ll see a recompile run. Here’s the output in the Extended Events Data window:

The second column is the GUID and the third column is the sequence. So you can see all the statement start and stop, the statistics used and built and updated, the recompile event itself, and the causes for everything. All grouped into a single view through the attach_activity_id.guid value.

Conclusion

I love causality tracking. If you look through my posts on Extended Events, I show a bunch of places where it’s useful. Let’s be clear though, this adds overhead. You’re going to want to be judicious in it’s use. However, think of all the things you can discover and document within SQL Server.

This is just one of many reasons why I strongly urge people to move away from Profiler. I get it. You’re comfortable. However, you’re sacrificing knowledge, understanding, and enhanced behavior for comfort. That seems a poor choice to me. Come on over to the dark side at #teamexevents. We have cookies.

---

I have a standing offer to show anyone who asks at any event you find me at how to better use Extended Events. However, that might be a quick walk through. If you want to sit down and really learn how to use Extended Events, Query Store, Management Studio and Execution Plans, then I’ve got an all-day training session I’ll be putting on in Orlando this spring at DevIntersection:

DevIntersection: April 7-9

On the other hand, if you want to talk DevOps, I have an all day class at Bits:

SQLBits: April 1