Capturing Stored Procedure Executions with Extended Events in SQL Server

Today, let’s talk about how to capture the execution of stored procedures using Extended Events in SQL Server. This is a powerful feature that allows us to monitor and analyze various aspects of SQL Server’s behavior. We’ll go through the steps to set up an Extended Events session, run a sample stored procedure, and then query the captured data. Let’s dive in!

Setting Up the Extended Events Session

First, we’ll create an Extended Events session to capture the execution of our stored procedures. We’ll use the sqlserver.module_end event to capture when a module (stored procedure, function, etc.) completes its execution. We’ll also set up a file target to store the captured events.

-- Create the Extended Events session
CREATE EVENT SESSION [Capture_Stored_Procedures]
ON SERVER
ADD EVENT sqlserver.module_end
(SET collect_statement = (1))
ADD TARGET package0.event_file
(SET filename = N'd:\data\StoredProcedureCaptures.xel', max_file_size = 5, max_rollover_files = 2);
GO
-- Start the Extended Events session
ALTER EVENT SESSION [Capture_Stored_Procedures] ON SERVER STATE = START;
GO

In the above code, we:

1. Create a new Extended Events session named Capture_Stored_Procedures.
2. Add the sqlserver.module_end event to capture when modules end, with collect_statement set to 1 to capture the executed statement.
3. Add a file target to store the captured events in d:\data\StoredProcedureCaptures.xel.

Running the Sample Stored Procedure

Next, let’s run a sample stored procedure multiple times to generate some event data. We’ll create a simple stored procedure that takes a parameter and returns it as a message.

USE YourDatabaseName; -- Change to your database
GO
CREATE OR ALTER PROCEDURE SampleProcedure
@InputMessage NVARCHAR(100)
AS
BEGIN
SELECT @InputMessage AS Message;
END;
GO
-- Execute the stored procedure
EXEC SampleProcedure @InputMessage = 'Hello, World!';
GO 5

This script creates a stored procedure named SampleProcedure that takes a single parameter @InputMessage and returns it as a message. We then execute this stored procedure five times.

Querying the Captured Data

Finally, let’s query the captured event data to analyze the stored procedure executions. We’ll use the sys.fn_xe_file_target_read_file function to read the data from the file target.

-- Query the Extended Events data from the file
SELECT
event_data.value('(event/@name)[1]', 'nvarchar(50)') AS EventName,
event_data.value('(event/data[@name="statement"]/value)[1]', 'nvarchar(max)') AS Statement,
event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint') AS Duration,
event_data.value('(event/data[@name="row_count"]/value)[1]', 'int') AS [RowCount],
event_data.value('(event/data[@name="logical_reads"]/value)[1]', 'int') AS [LogicalReads],
event_data.value('(event/data[@name="writes"]/value)[1]', 'int') AS [Writes],
event_data.value('(event/data[@name="cpu_time"]/value)[1]', 'int') AS [CpuTime],
event_data.value('(event/@timestamp)[1]', 'datetime') AS [Timestamp]
FROM (
SELECT
CONVERT(XML, event_data) AS event_data
FROM sys.fn_xe_file_target_read_file('d:\data\StoredProcedureCaptures*.xel', NULL, NULL, NULL)
) AS data;
GO

This query extracts various pieces of information from the captured event data, including the event name, executed statement, duration, row count, logical reads, writes, CPU time, and timestamp.

Conclusion – Extended Events

Extended Event is a powerful feature in SQL Server that allows us to capture detailed information about stored procedure executions. By setting up an Extended Events session and querying the captured data, we can gain valuable insights into the performance and behavior of our stored procedures.

I hope you find this post helpful! If you have any questions or comments, please feel free to reach out.

You can subscribe to my SQL in Sixty Seconds YouTube Channel for more learning.

Reference: Pinal Dave (https://blog.sqlauthority.com)