如何持久化 SQL Server 系统表记录？

一尘不染

如何持久化 SQL Server 系统表记录？

sql

我正在运行一个返回存储过程的最后执行时间的查询：

SELECT 
    o.name, 
    ps.last_execution_time 
FROM   
    sys.dm_exec_procedure_stats ps 
INNER JOIN 
    sys.objects o ON ps.object_id = o.object_id 
ORDER BY 
    ps.last_execution_time DESC

我得到了正确的结果，但如果我在大约 30 秒后再次运行查询，我不会得到任何结果。

是否需要设置或添加设置或命令以保留结果？

我的目标是找出过去 3 天内运行的存储过程。我正在针对 SQL Server 2019 Express 运行此查询。

阅读 78

2022-07-21

共1个答案

一尘不染

我会为此建议扩展活动。一、会话定义：

CREATE EVENT SESSION [ProcExecutions] ON SERVER
    ADD EVENT sqlserver.module_end
    ADD TARGET package0.event_file(
        SET filename = N'ProcExecutions',
        max_file_size = 10,
        max_rollover_files = 5
    )
    WITH (
        MAX_MEMORY = 4096 KB,
        EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS,
        MAX_DISPATCH_LATENCY = 30 SECONDS,
        MAX_EVENT_SIZE = 0 KB,
        MEMORY_PARTITION_MODE = NONE,
        TRACK_CAUSALITY = OFF,
        STARTUP_STATE = ON
    )
GO
ALTER EVENT SESSION [ProcExecutions] ON SERVER
    STATE = START;

您可能想要修改会话定义以满足您的需要。例子是：

按特定用户过滤（例如您的应用程序的登录）
只获取一个样本（例如“一百分之一的处决”）
获取其他数据（例如“什么用户调用了 proc？”、“整个语句（包括参数）是什么？”等）

以下是读取数据的方法：

IF object_id('tempdb.dbo.#events') IS NOT NULL
    DROP TABLE #events;

select cast(event_data as xml) as [event]
into #events
from sys.fn_xe_file_target_read_file('ProcExecutions*.xel', null, null, null);

WITH XEData AS (
    SELECT
        [event].value('(event/@timestamp)[1]', 'datetime2') AS [timestamp],
        db_name([event].value('(event/data[@name="source_database_id"])[1]', 'int')) AS [database],
        [event].value('(event/data[@name="object_name"])[1]', 'sysname') AS [object_name],
        [event].query('.') AS [event]
    from #events
)
SELECT * 
FROM XEData
ORDER BY [timestamp];

同样，这是非常基本的（仅返回时间戳、数据库和过程名称）。当我着手处理新事件会话的数据时，我将使用该event列作为 XML 外观的参考，这样我就可以编写适当的 xpath 表达式来提取我需要的数据。

2022-07-21