3

In SQL Server, when function sys.fn_get_audit_file() returns info from audit files, it includes column additional_information. Per that column's documentation, it contains "one level of TSQL stack" in XML format.

Example:

SELECT * FROM sys.fn_get_audit_file('\\myAuditPath\myAuditFile.sqlaudit', DEFAULT, DEFAULT)

returns additional_information values like this:

<tsql_stack><frame nest_level = '1' database_name = 'myDBa' schema_name = 'mySchema' object_name = 'thisObject'/></tsql_stack>
<tsql_stack><frame nest_level = '1' database_name = 'myDBa' schema_name = 'mySchema' object_name = 'someObject'/></tsql_stack>
<tsql_stack><frame nest_level = '3' database_name = 'myDBb' schema_name = 'mySchema' object_name = 'thatObject'/></tsql_stack>

My question: What is a frame's nest_level?

The documentation says only that "Frame nest_level indicates the current nesting level of the frame."

That frame nest_level contains values 1,2,3,4,...

It doesn't seem to be the nest_level that's returned by sys.dm_exec_requests. (That view's nest_level includes values -1 and 0, which don't seem to be relevant to the above frame nest_level.)

And it doesn't seem to be related to window functions. (Searching on the word "frame" temporarily led me down that path.) The above nest_level is seen even on audit rows that have no window function.

Can you point me to any info about that frame nest_level, or info about TSQL stacks?

By searching "tsql_stack" (definitely with the underscore) I found a couple of results about TSQL stacks. But still looking for details about that nest_level if anyone can help. Thanks again.

0

1 Answer 1

5

The nest level is indeed the level of the call stack. In other words, it represents how many EXEC statements have been nested. A "frame" means a single call to a procedure (it technically includes its parameters as well). The frames together make up the call stack, and unwind in LIFO order as calls to procedures end.

This is the same value as sys.dm_exec_requests shows. It's just that you can get 0 if no procedure is running, ie you are doing an ad-hoc batch. I'm not sure what -1 represents, but it seems to only be present on system requests.

An interesting way of showing this is using a recursive procedure that displays the current nesting level.

CREATE PROC p
  @maxnest int
AS

SELECT CONCAT('Before ', @maxnest) callNumber, nest_level
FROM sys.dm_exec_requests
WHERE session_id = @@SPID;

IF @maxnest > 0
BEGIN
    DECLARE @nextnest int = @maxnest - 1;
    EXEC dbo.p @nextnest;
END;

SELECT CONCAT('After ', @maxnest) callNumber, nest_level
FROM sys.dm_exec_requests
WHERE session_id = @@SPID;

What this does is show the current level, then decrement the parameter until 0 and recurse on itself. It will call itself the number of times to the value of @maxnest.

Executing the bare query not in a procedure gets us 0

SELECT nest_level
FROM sys.dm_exec_requests
WHERE session_id = @@SPID;

But recursing with a value of 2 gets us:

callNumber nest_level
Before 2 1
Before 1 2
Before 0 3
After 0 3
After 1 2
After 2 1

db<>fiddle

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.