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



  • 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'              
               )
        )
    



  • 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
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2