This article explores the Causality tracking option in the SQL Server Extended Events session(XEvents) and its usefulness in troubleshooting performance issues.
Introduction to SQL Server Extended Events(XEvent)
The SQL Server Extended event (XEvent) is a lightweight tool to collect data for troubleshooting. You can use XEvent to collect information, view it graphically, and analyze it for detecting any performance issues or system bottlenecks. In addition, it can correlate data from the operating system and SQL Server processes. The extended events replace the SQL trace and SQL Server profiler.
The following terms help understand the extended events.
- Session States: It represents the different states in an extended event session.
- CREATE EVENT SESSION
- ALTER EVENT SESSION, STATE=START
- ALTER EVENT SESSION, STATE=STOP
- DROP EVENT SESSION
- Session content and characteristics: It represents the content of an XEvent session. For example, target events along with their correlation in one or more sessions.
Image Reference: Microsoft docs
The multiple XEvent session is independent of each other configuration. These sessions do not interface with any event or target being used in multiple sessions.
- Note: I would recommend you to explore these extended events features and configuration using the SQLShack articles
Configuration of SQL Server Extended Events(XEvent) session using a template
SQL Server includes few pre-defined templates similar to Profiler for you to use without selecting the specific events. Connect to SQL Server in SSMS and navigate to Management -> Extended events.
It gives a list of pre-configured XEvent sessions in the SQL Server instance:
- Alwayson_health
- system_health
- telemetry_xevents
To create a new session, right-click on Sessions and choose New Session.
In the New session window, we specify the following inputs:
- SQL Server Extended Event (XEvent) session name
- Template: These templates are similar to the SQL Server profiler templates. The template description gives brief information about the purpose of the specific template
For this example, I choose the Standard template from the drop-down. It captures all transact SQL batches and stored procedure for monitoring the general database activity.
- Schedule: We can configure the XEvent session to start at server startup and immediately after its configuration. If you want to watch live data on XEvent viewer, Put a tick on Watch live data on screen as it is captured
- Causality tracking: Usually, we skip configuring the Causality tracking feature in an XEvent session. This article will explore what Causality tracking is and how we can leverage this for monitoring database activities
To turn on the causality tracking, check on – Track how events are related to one another.
Click on the Events, and it populates the extended events based on the chosen templates. The following figure shows the events for the standard templates.
- Note: We will not go into detail about the individual extended event in this article. You can explore Microsoft docs or SQLShack articles for detailed information
Click on the scripts to generate equivalent T-SQL of the configured SQL Server Extended Event. As shown below, it includes the TRACK_CASUALITY=ON argument.
To make things simpler, I configure the “TestCasualityEvents” using the following T-SQL script. It includes the following XEvents:
- sp_statement_completed: This event indicates that a T-SQL statement within a stored procedure has completed
- sql_statement_completed: This event indicates that a T-SQL statement has completed
- It turns on the causality tracking using the argument TRACK_CAUSALITY=ON
- It stores the captured events data on an event file named TestCasualityEvents. As we have not specified any directory, it stores the event file in the instance LOG directory. Therefore, you can navigate to the LOG directory and view the XEvent file with the XEL extension
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE EVENT SESSION TestCasualityEvents ON SERVER ADD EVENT sqlserver.sp_statement_completed( WHERE ([sqlserver].[is_system]=(0))), ADD EVENT sqlserver.sql_statement_completed( WHERE ([sqlserver].[is_system]=(0))) ADD TARGET package0.event_file (SET filename = N'TestCasualityEvents') WITH ( TRACK_CAUSALITY = ON ); |
Once the XEvent is set up, start it and execute a SQL transaction that includes the Update, Select statements in a single transaction.
- Note: We have not added the go statement to separate the statements Update and Select
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Use AdventureWorks2017 Go BEGIN TRAN; UPDATE [AdventureWorks2017].[HumanResources].[Employee] SET [JobTitle] = 'Senior Database Developer'; where BusinessEntityID=1 SELECT [NationalIDNumber] ,[LoginID] ,[JobTitle] ,[BirthDate] ,[MaritalStatus] FROM [AdventureWorks2017].[HumanResources].[Employee] where BusinessEntityID=1 ROLLBACK TRAN; |
Since the above transaction includes multiple SQL statements, the causality tracking can help us determine their execution order. Expand the TestCasualityEvents XEvent and view event data.
By default, XEvent viewer does not show all columns. However, you can customize the view as per your requirement. Therefore, click on columns and choose the required columns as shown below.
It captures data for the XEvent sql_statement_completed. In the output, look at the following columns:
- attach_activity_id.guid: The values in this column are similar for all statements in a transaction. It represents guid that remains the same for all events in a transaction
-
attach_activity_id.seq: It gives the sequence of an event in a transaction. It is an incremental number
- Sequence #1: Internal SQL Server commands such as Select @@SPID and specified BEGIN TRAN, Use Adventureworks2017 statements
- Sequence #2: The update statement
- Sequence #3: The select statement
- Sequence #4: ROLLBACK TRAN statement
We have not separated multiple statements in the previous query using the Go statement. Therefore, let’s run the modified code below and view the XEvent session output.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
Use AdventureWorks2017 Go BEGIN TRAN; UPDATE [AdventureWorks2017].[HumanResources].[Employee] SET [JobTitle] = 'Senior Database Administrator' where BusinessEntityID=2; GO SELECT [NationalIDNumber] ,[LoginID] ,[JobTitle] ,[BirthDate] ,[MaritalStatus] FROM [AdventureWorks2017].[HumanResources].[Employee] where BusinessEntityID=2 ROLLBACK TRAN; |
In the above query, we use a Go batch separator between the Update and Select statements. Therefore, the extended event output does not have a similar GUID for both update and select T-SQL.
-
Batch #1
- Sequence #1: Internal SQL Server commands such as Select @@SPID and specified BEGIN TRAN, Use Adventureworks2017 statements
- Sequence #2: The update statement
-
Batch #2
- Sequence #1: The select statement
- Sequence #2: ROLLBACK TRAN statement
Causality tracking for the stored procedure tracking using SQL Server Extended Events
Stored procedures are commonly used in SQL Server for optimizing performances because SQL Server cache the execution plan and reuses it on the subsequent executions.
In the following T-SQL script, we create the following stored procedures:
- The Stored procedures TestSP1, TestSP2, TestSP3 includes select statements and filter records based on the parameter @BusinessEntityID
- A Stored procedure TestSPFinal internally calls the stored procedures TestSP1, TestSP2, TestSP3 and a select statement. This procedure is known as the nested stored procedures
- For the testing purpose, we execute the TestSPFinal and capture the extended event data with causality tracking
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 |
Create Procedure TestSP1 ( @BusinessEntityID int) AS BEGIN SELECT [NationalIDNumber] ,[LoginID] ,[JobTitle] ,[BirthDate] ,[MaritalStatus] FROM [AdventureWorks2017].[HumanResources].[Employee] where BusinessEntityID=@BusinessEntityID END Go Create Procedure TestSP2 ( @BusinessEntityID int) AS BEGIN SELECT [BusinessEntityID] ,[rowguid] ,[ModifiedDate] FROM [AdventureWorks2017].[Person].[BusinessEntity] where BusinessEntityID=@BusinessEntityID END Go Create Procedure TestSP3 ( @BusinessEntityID int) AS BEGIN Select [PersonType] ,[NameStyle] ,[Title] ,[FirstName] ,[MiddleName] ,[LastName] ,[Suffix] ,[EmailPromotion] ,[AdditionalContactInfo] ,[Demographics] ,[rowguid] ,[ModifiedDate] FROM [AdventureWorks2017].[Person].[Person] where BusinessEntityID=@BusinessEntityID END Go Create Procedure TestSPFinal ( @BusinessEntityID int) AS BEGIN Exec TestSP1 @BusinessEntityID Exec TestSP2 @BusinessEntityID Exec TestSP3 @BusinessEntityID SELECT[Name] ,[ProductNumber] ,[MakeFlag] ,[FinishedGoodsFlag] ,[Color] ,[SafetyStockLevel] ,[ReorderPoint] ,[StandardCost] ,[ListPrice] ,[Size] ,[SizeUnitMeasureCode] ,[WeightUnitMeasureCode] FROM [AdventureWorks2017].[Production].[Product] END GO |
Suppose user-reported performance issues with the TestSPFinal stored procedure. You checked the SP definition and found that it is a nested SP. The performance issue might be with any of the stored procedures nested in the definition. You need to evaluate each SP individually and verify the performance, execution plans. It might be a complex and time-consuming task. The causality tracking can help you to figure out which SP is taking more time in execution.
Execute the following procedure and view the extended event file data.
|
1 |
Exec TestSPFinal @BusinessEntityID=1 |
- All nested stored procedures are running in a single batch and transaction. Therefore, the GUID value is the same for all
- It gives you a sequence of stored procedure executions
- You can choose column Duration and analyze the results to view which SP is taking more time in the execution. Therefore, you can pick that SP and troubleshoot it further
Disable and Enable Causality tracking for existing SQL Server Extended Events(XEvent) sessions
You can easily disable and enable causality tracking for existing XEvent sessions. Open the XEvent session properties, and the causality tracking option is on the general page.
Currently, the option for causality tracking is greyed out because the XEvent session is running.
To change the state of causality tracking, stop the XEvent session, change the configuration, and Start it again. Alternatively, you can use the following T-SQL for disabling the causality tracking configuration n the [TestCasualityEvents] XEvent session.
|
1 2 3 4 5 6 7 |
ALTER EVENT SESSION [TestCasualityEvents] ON SERVER STATE = STOP; Go ALTER EVENT SESSION [TestCasualityEvents] ON SERVER WITH (TRACK_CAUSALITY=OFF) GO ALTER EVENT SESSION [TestCasualityEvents] ON SERVER STATE = START; Go |
Similarly, use the following script to enable if the causality tracking is disabled.
|
1 2 3 4 5 6 7 |
ALTER EVENT SESSION [TestCasualityEvents] ON SERVER STATE = STOP; Go ALTER EVENT SESSION [TestCasualityEvents] ON SERVER WITH (TRACK_CAUSALITY=ON) GO ALTER EVENT SESSION [TestCasualityEvents] ON SERVER STATE = START; Go |
Conclusion
The track causality option in the SQL Server Extended Events session helps troubleshoot the performance issues. It is essential to understand the order of events for a transaction in SQL Server. In addition, you can utilize it for tracking individual stored procedures or statement performance (duration) from the nested stored procedure, as we explored in this article. You can explore more on extended events from the Microsoft documentation.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023