SQL Server：如何统计每小时事务数及生成每小时事务SSRS日报

阿华AIGC实验室

2026-5-15

解决SQL Server每小时事务统计与查询信息收集的数据源方案

针对你要做的两件事——统计每小时事务数、获取用户数据库每小时的查询信息来做SSRS每日报表，我从原生系统数据源的角度给你梳理可行方案，重点讲数据来源而非事后过滤的逻辑：

一、每小时事务数量统计的数据源

1. 性能计数器（`sys.dm_os_performance_counters`）

SQL Server内置的性能计数器是最直接的事务统计数据源，其中Transactions/sec（数据库级别）或Batch Requests/sec（宽泛的事务相关指标）可以反映实时事务吞吐量。要获取每小时累计值，你需要定期捕获计数器快照，计算时间窗口内的差值：

核心查询示例（获取目标数据库的事务计数器）：
```
SELECT 
    object_name, 
    counter_name, 
    cntr_value 
FROM sys.dm_os_performance_counters 
WHERE 
    object_name LIKE '%Databases%' 
    AND counter_name = 'Transactions/sec' 
    AND instance_name = DB_NAME(); -- 替换为你的用户数据库名
```
建议创建自定义表（比如dbo.HourlyTransactionStats），用SQL Server Agent作业每小时执行一次快照插入，再通过相邻快照的cntr_value差值计算该小时的事务总数。

2. 系统扩展事件（`system_health`会话）

SQL Server默认开启的system_health扩展事件会话会自动捕获关键事务事件（比如tran_start、tran_commit、tran_abort）。你可以直接查询该会话的事件文件，提取事务事件并按小时聚合：

查询事件文件的基础语句：

SELECT 
    DATEADD(HOUR, DATEDIFF(HOUR, 0, event_time), 0) AS hour_start,
    COUNT(*) AS transaction_count
FROM 
    sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL) x
WHERE 
    x.event_data.value('(event/@name)[1]', 'varchar(100)') IN ('tran_start', 'tran_commit')
GROUP BY 
    DATEADD(HOUR, DATEDIFF(HOUR, 0, event_time), 0)
ORDER BY 
    hour_start;

注意：system_health的文件会自动滚动，适合短期查询；若要长期留存数据，建议将事件导出到自定义表。

二、用户数据库每小时执行的所有查询信息数据源

1. 查询存储（Query Store）

SQL Server 2016及以上版本的Query Store是专为监控查询性能设计的内置功能，它会自动收集所有执行过的查询的详细信息（包括执行时间、资源消耗、执行计划等），并支持按时间窗口聚合，是生成报表最便捷的数据源：

按小时查询用户数据库执行查询的示例：

USE YourUserDatabase; -- 替换为目标用户数据库
SELECT 
    DATEADD(HOUR, DATEDIFF(HOUR, 0, rs.start_time), 0) AS hour_start,
    qt.query_sql_text,
    COUNT(rs.execution_type_desc) AS execution_count,
    SUM(rs.total_worker_time) AS total_cpu_time
FROM 
    sys.query_store_query_text qt
JOIN 
    sys.query_store_query q ON qt.query_text_id = q.query_text_id
JOIN 
    sys.query_store_plan p ON q.query_id = p.query_id
JOIN 
    sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
GROUP BY 
    DATEADD(HOUR, DATEDIFF(HOUR, 0, rs.start_time), 0),
    qt.query_sql_text
ORDER BY 
    hour_start, execution_count DESC;

你可以直接基于Query Store的视图创建SSRS数据集，无需额外的手动收集逻辑。

2. 自定义扩展事件会话

如果你的SQL Server版本低于2016，或者需要更细粒度的查询信息（比如捕获客户端应用名称、登录名），可以创建自定义扩展事件会话，捕获sql_statement_completed或batch_completed事件：

创建会话的示例语句：

CREATE EVENT SESSION [HourlyQueryCapture] ON SERVER 
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.database_name, sqlserver.sql_text, sqlserver.client_app_name, sqlserver.login_name)
    WHERE database_name = N'YourUserDatabase') -- 过滤目标数据库
ADD TARGET package0.event_file(SET filename=N'HourlyQueryCapture.xel')
WITH (STARTUP_STATE=ON); -- 服务重启后自动启动

之后可以通过sys.fn_xe_file_target_read_file读取事件文件，按小时聚合查询信息并导入到报表专用表中。

3. 动态管理视图（`sys.dm_exec_query_stats`）

这个DMV存储了当前缓存中查询的累积执行统计，但数据会在SQL Server重启或计划缓存清理时丢失，适合临时获取最近的查询信息，不适合长期报表：

按小时聚合的示例：

SELECT 
    DATEADD(HOUR, DATEDIFF(HOUR, 0, qs.last_execution_time), 0) AS hour_start,
    SUBSTRING(qt.text, qs.statement_start_offset/2 + 1, 
        (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2 
        ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS query_text,
    COUNT(*) AS execution_count
FROM 
    sys.dm_exec_query_stats qs
CROSS APPLY 
    sys.dm_exec_sql_text(qs.sql_handle) qt
WHERE 
    qt.dbid = DB_ID('YourUserDatabase') -- 过滤目标数据库
GROUP BY 
    DATEADD(HOUR, DATEDIFF(HOUR, 0, qs.last_execution_time), 0),
    SUBSTRING(qt.text, qs.statement_start_offset/2 + 1, 
        (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2 
        ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)
ORDER BY 
    hour_start;