Extended Events Capturing the T-SQL of Prepared Statements

I asked this question myself: Is there a way to use Extended Events to capture the T-SQL of a prepared statement?

Why would I be concerned with prepared statements? Wouldn’t sql_batch_completed and rpc_completed cover us? Well, no.

What happens when you use sp_prepare? What happens when you’re using an ORM tool that’s using prepared statements? You may see queries that look like this:

EXEC sp_execute 5, 48766;

What the heck code is that executing? Let’s find out.

sp_statement_completed

Here’s a set of sample code that I swiped from Microsoft (they don’t mind, but, full attribution like a good citizen, you’ll find it here):

DECLARE @P1 int;  
EXEC sp_prepare @P1 output,   
    N'@Param int',  
    N'SELECT *
FROM Sales.SalesOrderDetail AS sod
INNER JOIN Production.Product AS p ON sod.ProductID = p.ProductID
WHERE SalesOrderID = @Param
ORDER BY Style DESC;';  

-- Return handle for calling application
SELECT @P1;
GO

EXEC sp_execute @handle = 5, @PARAM = 49879;  
GO

EXEC sp_execute 5, 48766;
GO

EXEC dbo.AddressByCity @City = N'London' -- nvarchar(30)
GO

I also tossed in a stored procedure call just to be able to see the difference. I also prepped the following Extended Events session:

CREATE EVENT SESSION PreparedStatements
ON SERVER
ADD EVENT sqlserver.exec_prepared_sql
(ACTION
(
sqlserver.sql_text
)
WHERE (sqlserver.database_name = N'AdventureWorks')
),
ADD EVENT sqlserver.prepare_sql
(ACTION
(
sqlserver.sql_text
)
WHERE (sqlserver.database_name = N'AdventureWorks')
),
ADD EVENT sqlserver.rpc_completed
(ACTION
(
sqlserver.sql_text
)
WHERE (sqlserver.database_name = N'AdventureWorks')
),
ADD EVENT sqlserver.sp_statement_completed
(ACTION
(
sqlserver.sql_text
)
WHERE (sqlserver.database_name = N'AdventureWorks')
),
ADD EVENT sqlserver.sql_batch_completed
(ACTION
(
sqlserver.sql_text
)
WHERE (sqlserver.database_name = N'AdventureWorks')
),
ADD EVENT sqlserver.unprepare_sql
(ACTION
(
sqlserver.sql_text
)
WHERE (sqlserver.database_name = N'AdventureWorks')
)
WITH
(
TRACK_CAUSALITY = ON
);
GO

Normally, I wouldn’t mess around with adding an Action like sql_text. However, in this case, I wanted to be able to see it as a way to illustrate what’s happening. I’ve added a few events just to track the behaviors of prepared statements too: prepare_sql, exec_prepared_sql and unprepare_sql.

The Test

Running the test is easy. Start the Extended Event session. Run the code. It’s the results that are interesting:

You may have to click on that to make it bigger. However, you can see our target pretty easily. If, instead of the Extended Event session above, we were to add a filter to the sp_statement_completed based on “Type = ‘PREPARED'” we could easily capture the text of the prepared statements.

Conclusion

For many of us, this may not be an issue. For some of us dealing with lots of prepared statements from ORM tools, this could be a blessing. Either way, it’s a pretty simple addition to standard monitoring to easily pick up the text behind prepared statements.