主页 / dba / 问题 / 312112
Accepted
user2368632
Asked: 2022-05-14 17:09:13 +0800 CST

如何按 sp 执行时间（秒）过滤扩展事件会话？

  • 772

尝试查看执行时间超过给定秒数的命名存储过程的所有执行。你如何过滤执行时间（秒）？这是我到目前为止拼凑起来的

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'              
           )
    )
sql-server sql-server-2019

1 个回答

  1. Best Answer

    这应该有效，持续时间过滤器以微秒为单位。

    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

    我的测试有效的过程：

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

相关问题