Why can I not capture dynamic SQL called with EXEC using the sqlserver.sql_batch_completed Extended Event?

Question

It is my understanding that dynamic SQL is part of a batch. Yet, when I listed for sqlserver.sql_batch_completed as follows

CREATE EVENT SESSION [CaptureBatch] ON SERVER 
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.sql_text)
    WHERE ([sqlserver].[database_id]>(4))) 
ADD TARGET package0.event_file(SET filename=N'CaptureBatch',max_rollover_files=(0))
WITH (STARTUP_STATE=ON);

GO

ALTER EVENT SESSION [CaptureBatch] ON SERVER 
STATE = START;

I cannot see any calls to EXEC that also call EXEC. For example, the final line here does not show in my Extended Event's log.

CREATE PROCEDURE [TestThis]
AS
BEGIN
    SELECT 'Test';
END

GO

EXEC (N'EXEC TestThis');

Why is this? Are these kinds of EXEC not considered a batch or am I missing something?

I cannot seem to find any extensive documentation on this event, so forgive me if I have missed some.

Zikato · Accepted Answer · 2024-11-21 08:18:39Z

You're probably doing something wrong (like executing in a different database than you're filtering on). But to answer the question:

A batch is a set (one or more) of commands that a client sends to SQL Server.
GO is a batch separator setting of the SSMS client (it's not a TSQL command).

In your example, you're sending two batches. One is the create proc and the other dynamically executes the proc.

Repro

Create this XE for tracking

/* Clean up previous version */
IF EXISTS (SELECT 1 FROM sys.server_event_sessions ses WHERE
           ses.name = 'BatchStatementModuleSp'
          )
BEGIN
    DROP EVENT SESSION BatchStatementModuleSp ON SERVER;
END;
GO

CREATE EVENT SESSION [BatchStatementModuleSp] ON SERVER 
ADD EVENT sqlserver.module_end(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.sql_text,sqlserver.tsql_stack, sqlserver.database_id)
    WHERE ([sqlserver].[client_app_name]=N'SingleSessionTracking')),
ADD EVENT sqlserver.module_start(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.sql_text,sqlserver.tsql_stack, sqlserver.database_id)
    WHERE ([sqlserver].[client_app_name]=N'SingleSessionTracking')),
ADD EVENT sqlserver.sp_statement_completed(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.sql_text,sqlserver.tsql_stack, sqlserver.database_id)
    WHERE ([sqlserver].[client_app_name]=N'SingleSessionTracking')),
ADD EVENT sqlserver.sp_statement_starting(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.sql_text,sqlserver.tsql_stack, sqlserver.database_id)
    WHERE ([sqlserver].[client_app_name]=N'SingleSessionTracking')),
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.sql_text,sqlserver.tsql_stack, sqlserver.database_id)
    WHERE ([sqlserver].[client_app_name]=N'SingleSessionTracking')),
ADD EVENT sqlserver.sql_batch_starting(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.sql_text,sqlserver.tsql_stack, sqlserver.database_id)
    WHERE ([sqlserver].[client_app_name]=N'SingleSessionTracking')),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.sql_text,sqlserver.tsql_stack, sqlserver.database_id)
    WHERE ([sqlserver].[client_app_name]=N'SingleSessionTracking')),
ADD EVENT sqlserver.sql_statement_starting(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.sql_text,sqlserver.tsql_stack, sqlserver.database_id)
    WHERE ([sqlserver].[client_app_name]=N'SingleSessionTracking'))
WITH (TRACK_CAUSALITY = ON);
GO

IF NOT EXISTS (SELECT 1 FROM sys.dm_xe_sessions dxs WHERE dxs.name = 'BatchStatementModuleSp') 
BEGIN
    ALTER EVENT SESSION [BatchStatementModuleSp] ON SERVER STATE = START
END

Open a new connection and add an additional connection property like this

Application Name=SingleSessionTracking;

Run your script in the session from step 2.

CREATE OR ALTER PROCEDURE [TestThis]
AS
BEGIN
    SELECT 'Test';
END

GO

EXEC (N'EXEC TestThis');

And you'll see something like this

And here's an edited version that showcases the relations and nesting

Stack Exchange Network

Why can I not capture dynamic SQL called with EXEC using the sqlserver.sql_batch_completed Extended Event?

1 Answer 1

Repro

Hot Network Questions

Why can I not capture dynamic SQL called with EXEC using the sqlserver.sql_batch_completed Extended Event?

1 Answer 1

Repro

Related

Hot Network Questions