Cause and Effect with Causality Tracking

For this month’s T-SQL Tuesday, I discussed my changing thoughts on Extended Events as time has passed. Staying on the topic of Extended Events, let’s take a look at causality tracking and how it can be beneficial when tracking SQL Server activity.

What is Causality Tracking?

Causality is defined as the relationship between cause and effect so it’s safe to say that “causality tracking” is an appropriate name.

When you have causality tracking turned on for an Extended Events session, you can follow along with how one event in SQL can lead to various other events. By following the initial attach_activity_id.guid that gets assigned, you can follow statements, waits, statistic changes, and more. It’s a great way to filter the flury of events occuring on your server and narrow your focus.

Example of Causality Tracking

Let’s look at a short example that shows the differences between causality tracking turned on or turned off.

We’ll keep it simple. First, here’s our Extended Events session with causality tracking off. We’ll specifically note to turn it off:

CREATE EVENT SESSION [TEST_CT_OFF] ON SERVER ADD EVENT sqlserver.existing_connection
	,ADD EVENT sqlserver.LOGIN
	,ADD EVENT sqlserver.logout
	,ADD EVENT sqlserver.module_end
	,ADD EVENT sqlserver.module_start
	,ADD EVENT sqlserver.rpc_starting
	,ADD EVENT sqlserver.sp_statement_starting
	,ADD EVENT sqlserver.sql_batch_starting
	WITH (TRACK_CAUSALITY = OFF)
GO

We’ll also add a similar session with causality tracking on:

CREATE EVENT SESSION [TEST_CT_ON] ON SERVER ADD EVENT sqlserver.existing_connection
	,ADD EVENT sqlserver.LOGIN
	,ADD EVENT sqlserver.logout
	,ADD EVENT sqlserver.module_end
	,ADD EVENT sqlserver.module_start
	,ADD EVENT sqlserver.rpc_starting
	,ADD EVENT sqlserver.sp_statement_starting
	,ADD EVENT sqlserver.sql_batch_starting
	WITH (TRACK_CAUSALITY = ON)
GO

If we have both of these sessions running, watch the live data, and run a stored procedure, we’ll see the following information captured on our “off” session:

And we’ll see the following for our “on” session:

Adding attach_activity_id Columns

We can take advantage of the additional attach_activity_id columns to track our events. You can see them in the Details below the events. We’ll add two of these columns to make it clear how events are related:

We can track the attach_activity_id.guid and see the sequence by following the attach_activity_id.seq:

There you go. If you’re running some Extended Events sessions, check causality tracking and take advantage of its benefits.

Thanks for reading!