sys.dm_exec_procedure_stats计数异常？求排查及存储过程追踪方案

背景与问题 #

我需要退役一个无文档的过时数据库，已处理完表的外部依赖，正着手清理存储过程与函数。原本打算通过sys.dm_exec_procedure_stats视图识别正在使用的对象并修改调用方，但遇到了矛盾情况：

针对名为usp_ADMIN_Show_DateFormats的存储过程（用于返回日期时间转换常量示例），我用以下查询发现它每日被调用数千次：

SELECT    DB_NAME()                     as DBName
        , oo.[Name]                     as ProcedureName    
        , SCHEMA_NAME(oo.schema_id)     as SchemaName       
        , sm.is_recompiled              as isRecompiled     
        , sp.modify_date                as Modify_date      
        , st.cached_time                as cached_time      
        , st.last_execution_time        as Last_exec_Time   
        , st.execution_count            as execution_ct     
        , st.total_elapsed_time         as TotExecTime      
FROM sys.sql_modules sm
        LEFT JOIN sys.objects oo
            on oo.object_id = sm.object_id
        LEFT JOIN Sys.procedures sp
            on sp.object_id = sm.object_id
        LEFT JOIN sys.dm_exec_procedure_stats st
            on st.object_id = sm.object_id
WHERE NOT oo.type IN ('tr','v','fn', 'tf', 'if')

但运行跟踪未发现任何调用活动。为验证，我在该存储过程开头添加了日志插入代码，将每次执行记录到日志表：

INSERT INTO IT_SERVICE.dbo.DBA_ProcTrace
  ( ProcName, HostName, ProgramName, nt_domain, nt_userName, net_address, loginName, ProcSpid)
SELECT Object_name(@@PROCID), Hostname, program_name, nt_domain, nt_username, net_address, loginame, spid FROM Master.dbo.sysprocesses where spid = @@SPID

执行查询后得到第一次结果：

DBName  ProcedureName               SchemaName  isRecompiled    Modify_date             cached_time             Last_exec_Time          execution_ct    TotExecTime
@dbn    usp_ADMIN_Show_DateFormats  dbo         0               2023-08-23 09:50:28.420 2023-08-23 09:49:14.357 2023-08-23 10:17:43.797 168             172036

半小时后再次查询：

DBName  ProcedureName               SchemaName  isRecompiled    Modify_date             cached_time             Last_exec_Time          execution_ct    TotExecTime
DW_TBEI usp_ADMIN_Show_DateFormats  dbo         0               2023-08-23 09:50:28.420 2023-08-23 09:49:14.357 2023-08-23 10:41:32.130 333             280012

我预期日志表会新增165条记录（333-168），但实际一条都没有，且后续始终保持此模式：执行计数持续增长，但无日志记录。

问题根源与修正 #

核心错误 #

初始查询未关联sys.dm_exec_procedure_stats的database_id字段。由于不同数据库的对象ID可能重复，查询返回的是其他数据库中同名存储过程的执行统计数据，而非当前库的目标存储过程。这就是为什么当前库的存储过程加了日志却无记录，但计数仍在增长。

修正后的查询 #

结合建议，添加st.database_id = DB_ID(DB_NAME())的关联条件，确保只获取当前数据库的统计数据：

SELECT    DB_NAME()                     as DBName
        , oo.[Name]                     as ProcedureName    
        , SCHEMA_NAME(oo.schema_id)     as SchemaName       
        , sm.is_recompiled              as isRecompiled     
        , sp.modify_date                as Modify_date      
        , st.cached_time                as cached_time      
        , st.last_execution_time        as Last_exec_Time   
        , st.execution_count            as execution_ct     
        , st.total_elapsed_time         as TotExecTime  
FROM sys.sql_modules sm
        LEFT JOIN sys.objects oo
            on oo.object_id = sm.object_id
        LEFT JOIN Sys.procedures sp
            on sp.object_id = sm.object_id
        LEFT JOIN sys.dm_exec_procedure_stats st
            on st.object_id = sm.object_id
                and st.database_id = DB_ID(DB_NAME())
WHERE NOT oo.type IN ('tr','v','fn', 'tf', 'if')
        AND NOT cached_time IS NULL

低侵入性的存储过程使用追踪方法 #

为最小化对系统的影响，同时捕获存储过程的使用及调用方信息，推荐以下方案：

• 扩展事件（Extended Events）：SQL Server官方推荐的轻量级追踪工具，性能开销极低。可创建追踪rpc_completed（针对存储过程调用）或sql_statement_completed事件，通过筛选目标存储过程的object_id或名称，捕获调用方的客户端应用名、主机名、登录名等关键信息，无需修改任何存储过程。
• 依赖关系查询：使用sys.dm_sql_referencing_entities视图查找数据库内依赖目标存储过程的对象，但此方法无法捕获外部应用的调用。
• 轻量日志（可选）：若必须修改存储过程，优先使用异步日志方式（如写入内存表后批量同步），避免同步插入日志对性能的影响；仅在测试环境启用日志，生产环境改用扩展事件。

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