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' ) )
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
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