如何持久化SQL Server系统表记录查询存储过程执行历史

sys.dm_exec_procedure_stats返回的是内存中计划缓存对应的存储过程执行统计数据，本身不做任何持久化存储，只要对应存储过程的执行计划从缓存中移除，相关记录就会从视图中消失，不存在任何系统配置可以让这个视图长期保留历史数据。
你间隔30秒查询就返回空结果，和SQL Server 2019 Express的特性直接相关：

• SQL Server Express版本实例最大可用内存仅1410MB，当存在内存压力时，计划缓存是优先被清理的内存区域
• Express版本默认对用户数据库开启AUTO_CLOSE选项，当数据库的最后一个用户连接断开后，数据库会自动关闭，关联的所有缓存执行计划会被全部清空，30秒的空闲间隔完全足够触发这个流程
• 存储过程重编译、实例重启、手动执行缓存清理命令也会清空这个视图的记录

要拿到跨天的稳定历史记录，必须自行搭建数据采集持久化流程，没有现成的系统开关可以直接开启：

1. 先关闭目标数据库的AUTO_CLOSE配置，减少无意义的缓存清空：

ALTER DATABASE [你的业务数据库名] SET AUTO_CLOSE OFF;

2. 在业务库中创建专用的历史记录存储表：

USE [你的业务数据库名];
GO
CREATE TABLE dbo.ProcedureExecHistory
(
    ObjectID INT NOT NULL,
    ProcName SYSNAME NOT NULL,
    LastExecTime DATETIME2(3) NOT NULL,
    CollectTime DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT PK_ProcExecHistory PRIMARY KEY CLUSTERED (ObjectID, LastExecTime)
);

3. 编写数据采集脚本，定期把当前DMV中的新增记录写入历史表。注意SQL Server Express没有自带SQL Server代理服务，你需要通过Windows任务计划程序调用sqlcmd，定时（建议间隔10-15分钟）执行以下脚本：

-- 合并新的执行记录
MERGE dbo.ProcedureExecHistory AS target
USING (
    SELECT
        ps.object_id,
        o.name AS ProcName,
        ps.last_execution_time
    FROM sys.dm_exec_procedure_stats ps
    INNER JOIN sys.objects o
        ON ps.object_id = o.object_id
    WHERE o.type = 'P' -- 仅筛选用户存储过程，排除系统对象
        AND ps.database_id = DB_ID('你的业务数据库名')
) AS source
ON target.ObjectID = source.object_id
    AND target.LastExecTime = source.last_execution_time
WHEN NOT MATCHED THEN
    INSERT (ObjectID, ProcName, LastExecTime)
    VALUES (source.object_id, source.ProcName, source.last_execution_time);

-- 自动清理3天以上的旧数据，避免表体积无限增长
DELETE FROM dbo.ProcedureExecHistory
WHERE LastExecTime < DATEADD(DAY, -3, SYSUTCDATETIME());

4. 后续查询近3天执行过的存储过程，直接查询自建的历史表即可，结果不会因为缓存清空丢失：

SELECT
    ProcName,
    MAX(LastExecTime) AS LatestExecTime
FROM dbo.ProcedureExecHistory
WHERE LastExecTime >= DATEADD(DAY, -3, SYSUTCDATETIME())
GROUP BY ProcName
ORDER BY LatestExecTime DESC;

注意：不要尝试通过调整服务器内存、缓存相关配置让sys.dm_exec_procedure_stats直接保留3天数据，这个DMV的设计定位就是实时暴露当前缓存中的统计信息，没有任何机制可以保证执行计划在缓存中留存3天不被淘汰，自行定时采集持久化是唯一可靠的实现方式。

内容的提问来源于stack exchange，提问作者user1647160