Monitoring Stored Procedure Performance in SQL Server with Extended Events

Keywords: SQL Server, stored procedure, performance monitoring, extended events, event session

 

Introduction:

In SQL Server, stored procedures are commonly used to improve performance and security. However, they can also impact database performance if they are poorly designed or executed. As a database administrator, it's important to monitor stored procedure performance to ensure that they are running efficiently. In this article, we'll show you how to use extended events to monitor stored procedure performance in SQL Server.

 

What are Extended Events?

Extended Events are a feature in SQL Server that allow you to collect information about events that occur in the database engine. This can include things like query execution, deadlock detection, and performance metrics. Extended Events use a lightweight architecture that minimizes the impact on server performance.

Step-by-Step Guide:

To monitor stored procedure performance using Extended Events, follow these steps:

Create a folder in SQL Server to store the event data. Make sure that you have permission to write data to this folder.

CREATE EVENT SESSION [SP_Runtime] ON SERVER

ADD EVENT sqlserver.sp_statement_completed(

    ACTION(sqlserver.sql_text,sqlserver.tsql_stack,sqlserver.username)

    WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'% YourStoredProcedureName %')))    -- can modify

ADD TARGET package0.event_file(SET filename=N'C:\test\SP_Runtime.xel')

WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)

GO

Alter the event session to start capturing data.

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

Execute the stored procedure that you want to monitor.

EXEC YourStoredProcedureName;

 

Once you've finished monitoring, stop the event session to prevent it from impacting server performance.

ALTER EVENT SESSION [SP_Runtime] ON SERVER STATE = STOP;

 

Query the log data from the file that you created in step 1 to see the results of the monitoring.

SELECT

    CAST(event_data AS XML).value('(event/@timestamp)[1]', 'DATETIME2') AS [Timestamp],

    CAST(event_data AS XML).value('(event/action[@name="sql_text"]/value)[1]', 'VARCHAR(MAX)') AS [SQL Text],

    CAST(event_data AS XML).value('(event/action[@name="tsql_stack"]/value)[1]', 'VARCHAR(MAX)') AS [TSQL Stack],

    CAST(event_data AS XML).value('(event/action[@name="username"]/value)[1]', 'VARCHAR(50)') AS [Username],

    CAST(event_data AS XML).value('(event/data[@name="duration"]/value)[1]', 'BIGINT') / 1000.0 AS [Duration (ms)]

FROM sys.fn_xe_file_target_read_file('C:\test\SP_Runtime*.xel', null, null, null) AS XE

 

Conclusion:

Monitoring stored procedure performance in SQL Server is an important task for database administrators. Extended Events provide a lightweight and efficient way to collect information about stored procedure execution. By following the steps outlined in this article, you can easily set up an event session to monitor stored procedure performance and identify potential performance bottlenecks.

 

Tags: SQL Server, stored procedure, performance monitoring, extended events, event session

Optimized Content:

When it comes to monitoring stored procedure performance in SQL