如何通过Query Store表统计存储过程的每日总执行次数

1. 多语句存储过程统计重复的核心原因：Query Store以单条独立SQL语句为粒度记录执行信息，存储过程每执行1次，内部的N条语句都会各自生成1条执行记录。你直接对rs.count_executions求和，相当于把所有语句的执行次数累加，得到的结果是实际存储过程执行次数的N倍，自然会出现重复统计问题。
2. 查询仅返回单一QueryID的常见原因：
   • Query Store捕获策略默认是自动模式，会自动过滤执行开销低、耗时短的语句，仅记录达到开销阈值的语句，因此你只能看到符合捕获条件的少数/单条语句的记录。
   • 存储过程近期触发了重编译，或Query Store的旧数据已被清理，仅保留了最新编译后少数语句的执行记录。
   • 你查询的时间范围内，存储过程的调用都走到了同一分支逻辑，其他分支的语句未被触发执行，自然没有对应QueryID的记录。
3. 你原有代码存在笔误：TotalLogicalReads的计算误用了avg_cpu_time参与运算，得到的逻辑读数值完全错误。

方案1：短期/实时统计（基于过程缓存） #

该方案直接统计存储过程级别的执行次数，无重复统计问题，结果100%准确。仅缺点是数据仅保存在过程缓存中，实例重启、存储过程重编译、缓存回收都会丢失数据，仅能统计缓存有效期内的执行记录。

SELECT 
    RunDate = CONVERT(VARCHAR(10), last_execution_time, 121) + ' ' + LEFT(DATENAME(WEEKDAY, last_execution_time), 1),
    TotalExecutions = SUM(execution_count),
    TotalCPU = SUM(total_worker_time/1000), -- 单位转换为毫秒
    TotalLogicalReads = SUM(total_logical_reads)
FROM sys.dm_exec_procedure_stats ps
JOIN sys.objects o ON ps.object_id = o.object_id
WHERE 
    o.name = 'API_SPName'
    AND o.schema_id = SCHEMA_ID('dbo') -- 建议指定schema，避免重名存储过程统计错误
GROUP BY CONVERT(VARCHAR(10), last_execution_time, 121) + ' ' + LEFT(DATENAME(WEEKDAY, last_execution_time), 1)
ORDER BY RunDate DESC

方案2：长期历史统计（基于Query Store） #

该方案依赖Query Store的持久化存储，可统计历史全量数据。使用前需先将Query Store的捕获策略调整为全部，避免语句被过滤：

ALTER DATABASE 你的数据库名称 
SET QUERY_STORE (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = ALL);

再通过以下语句统计，逻辑是取每日所有QueryID的最高执行次数（存储过程每执行1次，所有被执行的语句执行次数都会+1，最高值即为存储过程实际执行次数）：

WITH DailyQueryExec AS (
    SELECT 
        RunDate = CONVERT(VARCHAR(10), rsi.start_time, 121) + ' ' + LEFT(DATENAME(WEEKDAY, rsi.start_time), 1),
        q.query_id,
        DailyExec = SUM(rs.count_executions)
    FROM sys.query_store_runtime_stats rs
    JOIN sys.query_store_runtime_stats_interval rsi ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
    JOIN sys.query_store_plan p ON rs.plan_id = p.plan_id
    JOIN sys.query_store_query q ON p.query_id = q.query_id
    WHERE 
        OBJECT_NAME(q.object_id) = 'API_SPName'
        AND q.object_id IS NOT NULL
    GROUP BY CONVERT(VARCHAR(10), rsi.start_time, 121) + ' ' + LEFT(DATENAME(WEEKDAY, rsi.start_time), 1), q.query_id
)
SELECT 
    RunDate,
    TotalExecutions = MAX(DailyExec),
    TotalQueries = COUNT(DISTINCT query_id)
FROM DailyQueryExec
GROUP BY RunDate
ORDER BY RunDate DESC

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