1

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



1 Answer 1

4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.