Parsing an Extended Event Attach_Activity_ID into Component Parts with the String_Split Function
In SQL Server 2008, extended event sessions were introduced as more performant and extensive tool to replace SQL Profiler and server side traces. Within extended events you are able to add a field that is called attach_activity_id which generates a GUID for each remote procedure call and a sequence number for each event that is recorded in the XE Session related to that RPC call. This is very helpful if you have nested procedures and trigger code that is firing in response to a given RPC call. However, the attach_activity_id is a single field with both the GUID and the sequence number concatenated. As performance tuners and developers, we want to see all the events related to specific RPC call, the GUID, ordered by the sequence number. Thus parsing the GUID and the sequence number is important. The GUID components are variable length and the sequence number is variable length, so parsing is not straight forward.
Enter the STRING_SPLIT() table valued function that was introduced in SQL Server 2016. It parses a value into parts based on a character. This function can be used with the common table expressions, the ROW_NUMBER() function and the PIVOT operator to split the attach_activity_id into the GUID and the Sequence number.
Here is the full example. Below the example you’ll find an explanation and additional links for reference. Click picture to download full code example.
The first two queries are very straight forward – they create a table and populate the table with sample data. When you shred the extended event file one of the columns you’ll shred out is the attach_activity_id.
The first CTE, SplitActivity, does the STRING_SPLIT in the CROSS APPLY. The cross apply takes the attach_activity_id from each row in the table and passes it into the string split function. Since the column always has 6 values separated by the hypen, the string_split table valued function returns 6 rows for each row in the #XESession temporary table. The ROW_NUMBER() function gives us a column with a value 1 through 6.
The second CTE, PivotActivity, does the pivot which moves the 6 rows into one row for each attach_activity_id and 6 columns named 1 through 6. Since there are six rows for each value and they are always numbered 1 through 6 we can hard code the column names and do not have to do dynamic sql for the pivot.
The last CTE, ActivityParts, simply concatenates the columns to give you a GUID and a Sequence Number.
Here are some additional references that may help:
Using the STRING_SPLIT() Function - Microsoft
Using the ROW_NUMBER() Function – SQL Shack
Using the PIVOT operator – SQL Server Tutorial
Full Troubleshooting Series
Parsing an Extended Event Attach_Activity_ID into Component Parts with the String_Split Function
Using Extended Events to Performance Tune Sage 500 - Overview
IDENTIFYING THE SAGE 500 TASK NAME AS SEEN IN SQL SERVER
CREATE AN EXTENDED EVENTS SESSION FOR RECORDING ALL THE ACTIVITY UNDER A TASK
Running the XE Session and Brief View of Events
EXTRACT THE XE DATA FROM THE EVENT FILE AND LOAD IT INTO A SQL TABLE
