Rajendra Gupta
View event sequence

SQL Server Extended Events Causality Tracking

October 14, 2021 by

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.

SQL Server Extended Events

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

pre-configured SQL Server Extended Event sessions

To create a new session, right-click on Sessions and choose New Session.

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.

turn on the causality tracking in SQL Server Extended Event

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

individual extended event

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.

equivalent T-SQL of the configured XEvent

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

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

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.

customize the view

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

View event sequence

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.

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

Workload with GO separator

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

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.

  • 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

Nested stored procedure

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.

Disable and Enable Causality tracking

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.

Similarly, use the following script to enable if the causality tracking is disabled.

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.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
Monitoring, Performance counters

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is 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 his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

628 Views