如何查询SQL Server表中显式事务与自动提交事务的占比？

实时活跃事务占比查询 #

通过SQL Server的动态管理视图（DMVs）可快速获取当前活跃事务的类型分布：

SELECT
    CASE 
        WHEN is_autocommit = 1 THEN '自动提交事务'
        ELSE '显式事务'
    END AS 事务类型,
    COUNT(*) AS 事务数量,
    CAST(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER () AS DECIMAL(5,2)) AS 占比(%)
FROM (
    SELECT 
        s.session_id,
        CASE 
            WHEN t.transaction_type = 2 THEN 1  -- transaction_type=2对应自动提交事务
            ELSE 0 
        END AS is_autocommit
    FROM sys.dm_tran_active_transactions t
    JOIN sys.dm_exec_sessions s ON t.transaction_id = s.transaction_id
    WHERE t.transaction_state IN (1, 3)  -- 仅统计活跃的事务（未提交/正在提交）
) AS transaction_stats
GROUP BY is_autocommit
ORDER BY 事务类型;

说明 #

• sys.dm_tran_active_transactions 记录当前实例中的所有活跃事务，transaction_type=2 代表自动提交事务，其他类型（如1为显式用户事务）归为显式事务。
• 关联sys.dm_exec_sessions确保只统计有效会话的事务，过滤掉已完成的事务状态。

长期事务占比统计（适合整改进度监控） #

若需统计一段时间内的事务分布，推荐使用扩展事件捕获事务提交事件，避免实时查询的局限性：

1. 创建并启动扩展事件会话 #

-- 创建扩展事件会话，捕获事务提交事件及类型
CREATE EVENT SESSION [TransactionTracking] ON SERVER 
ADD EVENT sqlserver.transaction_committed(
    ACTION(sqlserver.session_id, sqlserver.transaction_id, sqlserver.transaction_type))
ADD TARGET package0.event_file(SET filename=N'C:\SQL_XE\TransactionTracking.xel')  -- 自定义存储路径
WITH (STARTUP_STATE=ON);

-- 启动会话
ALTER EVENT SESSION [TransactionTracking] ON SERVER STATE = START;

2. 查询捕获的事件数据统计占比 #

SELECT
    CASE 
        WHEN event_data.value('(data[@name="transaction_type"]/value)[1]', 'INT') = 2 THEN '自动提交事务'
        ELSE '显式事务'
    END AS 事务类型,
    COUNT(*) AS 事务数量,
    CAST(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER () AS DECIMAL(5,2)) AS 占比(%)
FROM (
    SELECT CAST(event_data AS XML) AS event_data
    FROM sys.fn_xe_file_target_read_file('C:\SQL_XE\TransactionTracking*.xel', NULL, NULL, NULL)
) AS xe_data
GROUP BY event_data.value('(data[@name="transaction_type"]/value)[1]', 'INT')
ORDER BY 事务类型;

说明 #

• 扩展事件性能开销极低，适合长期运行以收集全量事务数据，能准确反映整改后的整体事务分布。
• 需提前创建存储路径（如C:\SQL_XE\）并确保SQL Server服务账号有读写权限。

注意事项 #

• 查询DMVs和扩展事件需要VIEW SERVER STATE权限。
• 实时查询仅反映当前瞬间的事务状态，无法体现整体趋势；扩展事件是监控整改进度的更可靠方式。
• 定期清理扩展事件的目标文件，避免磁盘空间耗尽。

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