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.

Answer 1

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

1. 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

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

Application Name=SingleSessionTracking;

enter image description here

3. 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

enter image description here

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

enter image description here