Explanation of Extended Events xml

Question

I queried an Extended Events session and returned the XML. I am confused on what frame level 1, 2, 3 mean. As well as line, offsetStart and offsetEnd? I am however able to convert the sql handle to the sp name.

The level seems to be in reverse order of how they are called. So the object listed in level 3, calls the object in level 2, which calls the object in level 1.

Answer 1

As the name of the "action" indicates, you are looking at the execution stack of a nested series of calls in a single Session. As you noted, one object calls another which calls another. As each successive module is executed, the calling module is put on hold and pushed down the "stack", with the current / active module being on top (level 1). When the top level module completes, if there are any calling modules in the stack (level > 1) then they all move up by one and what was at level 2 is now at level 1 and is active again, continuing where it left off when it called the sub-module.

Level 1 is what you see in sys.dm_exec_requests and other DMVs. You cannot see Levels above 1 (at least as far as I have ever seen).

What exists at each level is the query batch (one or more T-SQL statements encapsulated in either a module -- Stored Procedure, Triggers, not sure if Multi-statement Table-Valued Functions and Scalar UDFs would get their own level -- or submitted as ad hoc / Dynamic SQL). The handle (i.e. "sql_handle") attribute refers to the query batch (all statements within the batch). The line attribute should be the starting line number of the current executing statement within the batch. The offsetStart and offsetEnd attributes should be the starting and ending byte positions of the current executing statement within the batch. Because the offsets are expressed as byte positions, you need to divide by 2 in order to get the character positions (if you want to do something like SUBSTRING the batch to extract that particular statement). I am not sure if a value of -1 ever shows up here for offsetEnd, but it does show up in sys.dm_exec_requests and indicates "end of the batch".