How to filter extended event session by sp execution time (seconds)?

All we need is an easy explanation of the problem, so here it is.

Trying to view all executions of a named stored procedure where execution time exceeds a given number of seconds. How do you filter on execution time (seconds)? This is what I have cobbled together so far

CREATE EVENT SESSION [Slow SP Executions]
ON SERVER
    ADD EVENT sqlserver.module_end
    (SET collect_statement = (1)
     ACTION
     (
         sqlserver.host,                       
         sqlserver.database_name,  
         sqlserver.client_app_name, 
         sqlserver.session_server_principal_name, 
         sqlserver.username,                      
         sqlserver.sql_text,
         sqlserver.tsql_stack
     )
     WHERE (
               [object_type] = 'P ' 
               AND [sqlserver].[database_name] = N'MyDB'
               AND [object_name] = N'MySproc'              
           )
    )
    



How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

This should work, duration filter is in Microseconds.

CREATE EVENT SESSION [captureProcDuration] ON SERVER 
ADD EVENT sqlserver.module_end(
    ACTION(
        sqlserver.client_app_name,
        sqlserver.client_hostname,
        sqlserver.database_name,
        sqlserver.server_principal_name,
        sqlserver.sql_text,
        sqlserver.tsql_stack)
    WHERE (
        [object_name] = N'testProcDuration'
        AND [package0].[greater_than_uint64]([duration], (5000)))
    )
ADD TARGET package0.event_file(
    SET filename = N'captureProcDuration', max_file_size = (256)
)
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

Proc that my test worked:

CREATE OR ALTER PROC testProcDuration
AS
BEGIN
    WAITFOR DELAY '00:00:07'
END

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply