SQL Server 管理系列（第七期）：故障排查实战——从报警到解决的完整流程

SQL Server 管理系列（第七期）：故障排查实战——从报警到解决的完整流程

前六期我们系统学习了安装配置、安全加固、备份恢复、高可用、自动化运维和性能监控。这一期，作为管理系列的收官之作，我们将把这些知识融会贯通，通过真实故障场景，走完故障排查的完整流程。从收到报警到定位根因，从应急处理到永久修复，再到事后复盘——让你面对生产故障时不再慌乱。

一、故障排查方法论

1.1 故障响应五步法

┌─────────────────────────────────────────────────────────────┐
│                    故障响应流程                              │
│                                                             │
│  第1步：收到报警 ──→ 确认影响范围                            │
│        │                                                    │
│        ▼                                                    │
│  第2步：快速止血 ──→ 应急处理（Kill会话、重启服务等）         │
│        │                                                    │
│        ▼                                                    │
│  第3步：定位根因 ──→ 分析日志、DMV、执行计划                  │
│        │                                                    │
│        ▼                                                    │
│  第4步：永久修复 ──→ 代码变更、配置调整、架构优化             │
│        │                                                    │
│        ▼                                                    │
│  第5步：事后复盘 ──→ 故障报告、改进措施、监控增强             │
└─────────────────────────────────────────────────────────────┘

1.2 DBA 应急工具箱

工具	用途	命令
SP_WHO2	查看当前会话	SP_WHO2 或 SP_WHO
SP_WHOISACTIVE	高级会话分析（需安装）	EXEC dbo.sp_WhoIsActive
DBCC INPUTBUFFER	查看会话最后执行的SQL	DBCC INPUTBUFFER(SPID)
KILL	终止会话	KILL SPID
ALTER DATABASE	设置数据库状态	ALTER DATABASE ... SET SINGLE_USER
DBCC CHECKDB	数据库完整性检查	DBCC CHECKDB(DBName)
DBCC SQLPERF	日志空间使用	DBCC SQLPERF(LOGSPACE)

二、故障场景一：CPU 飙升到 100%

2.1 症状识别

报警信息：
- CPU 使用率持续 95%+
- 用户反馈查询超时
- 新增查询大量出现

2.2 应急处理（5分钟内）

-- 1. 立即查看当前高 CPU 的会话
SELECT 
    r.session_id,
    s.login_name,
    r.cpu_time,
    r.total_elapsed_time,
    r.status,
    r.command,
    SUBSTRING(st.text, (r.statement_start_offset/2)+1,
        ((CASE r.statement_end_offset 
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE r.statement_end_offset
        END - r.statement_start_offset)/2) + 1) AS query_text
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE r.status NOT IN ('background', 'sleeping')
  AND r.cpu_time > 0
ORDER BY r.cpu_time DESC;

-- 2. 找到 CPU 消耗最高的查询（历史累计）
SELECT TOP 5
    qs.total_worker_time / 1000000 AS total_cpu_sec,
    qs.execution_count,
    qs.total_worker_time / qs.execution_count / 1000000 AS avg_cpu_sec,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 500) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_worker_time DESC;

-- 3. 应急：终止异常会话（确认后执行）
KILL <session_id>;

2.3 根因定位

-- 检查是否有大量并行查询
SELECT 
    session_id,
    scheduler_id,
    status,
    command,
    wait_type,
    wait_time
FROM sys.dm_exec_requests
WHERE command LIKE '%SELECT%'
  AND session_id IN (
      SELECT session_id FROM sys.dm_os_tasks 
      GROUP BY session_id HAVING COUNT(*) > 8
  );

-- 检查参数嗅探问题
SELECT 
    qs.execution_count,
    qs.total_worker_time / qs.execution_count / 1000000 AS avg_cpu_sec,
    qs.total_elapsed_time / qs.execution_count / 1000000 AS avg_elapsed_sec,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 500) AS query_text,
    qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qs.execution_count > 10
  AND qs.total_worker_time / qs.execution_count > 1000000  -- 平均超过1秒CPU
ORDER BY qs.total_worker_time / qs.execution_count DESC;

2.4 解决方案

原因	解决方案
缺少索引	创建覆盖索引
统计信息过期	UPDATE STATISTICS
参数嗅探	使用 OPTION (RECOMPILE) 或本地变量
并行度过高	调整 MAXDOP
循环/游标	改为集合操作

-- 针对参数嗅探的快速修复
EXEC sp_recompile 'YourStoredProcedure';
-- 或
ALTER DATABASE YourDB SET PARAMETERIZATION FORCED;

三、故障场景二：数据库阻塞（应用卡死）

3.1 症状识别

报警信息：
- 大量超时错误
- 应用程序无响应
- 用户反馈"转圈"

3.2 应急处理

-- 1. 查看阻塞链
SELECT 
    blocked.session_id AS blocked_session,
    blocking.session_id AS blocking_session,
    blocked.wait_time / 1000 AS wait_sec,
    blocked.wait_type,
    blocking.last_request_start_time,
    blocking.last_request_end_time,
    (SELECT TEXT FROM sys.dm_exec_sql_text(blocked.sql_handle)) AS blocked_sql,
    (SELECT TEXT FROM sys.dm_exec_sql_text(blocking.sql_handle)) AS blocking_sql
FROM sys.dm_exec_requests blocked
JOIN sys.dm_exec_requests blocking 
    ON blocked.blocking_session_id = blocking.session_id
WHERE blocked.blocking_session_id > 0;

-- 2. 查找阻塞源头（最顶层的阻塞会话）
SELECT 
    session_id,
    blocking_session_id,
    wait_time,
    wait_type,
    last_wait_type,
    text
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE session_id IN (
    SELECT blocking_session_id 
    FROM sys.dm_exec_requests 
    WHERE blocking_session_id > 0
    EXCEPT 
    SELECT session_id 
    FROM sys.dm_exec_requests 
    WHERE blocking_session_id > 0
);

-- 3. 查看锁详细信息
SELECT 
    request_session_id,
    resource_type,
    resource_database_id,
    resource_description,
    request_mode,
    request_status,
    request_owner_type
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID()
ORDER BY request_session_id, resource_type;

-- 4. 应急：终止阻塞源头（确认后执行）
KILL <blocking_session_id>;

3.3 根因定位

-- 查找长期未提交的事务
SELECT 
    st.session_id,
    st.transaction_id,
    at.transaction_begin_time,
    DATEDIFF(MINUTE, at.transaction_begin_time, GETDATE()) AS duration_min,
    at.transaction_state,
    at.transaction_type,
    s.login_name,
    s.program_name,
    s.host_name
FROM sys.dm_tran_session_transactions st
JOIN sys.dm_tran_active_transactions at 
    ON st.transaction_id = at.transaction_id
JOIN sys.dm_exec_sessions s 
    ON st.session_id = s.session_id
WHERE at.transaction_begin_time < DATEADD(MINUTE, -5, GETDATE())
ORDER BY at.transaction_begin_time;

-- 查找锁升级（行锁→表锁）
SELECT 
    object_name(p.object_id) AS TableName,
    p.index_id,
    lock_count = COUNT(*)
FROM sys.dm_tran_locks l
JOIN sys.partitions p ON l.resource_associated_entity_id = p.hobt_id
WHERE l.resource_type = 'OBJECT'
GROUP BY p.object_id, p.index_id
ORDER BY lock_count DESC;

3.4 解决方案

原因	解决方案
应用程序未提交事务	修复代码，添加超时机制
事务过长	拆分事务，减少锁定范围
缺失索引	添加索引，减少锁定的行数
锁升级	调整锁升级阈值，或优化查询
死锁	统一资源访问顺序，应用重试

-- 设置锁超时（应用连接字符串）
-- 或在查询中设置
SET LOCK_TIMEOUT 30000;  -- 30秒后自动超时

-- 启用 RCSI 减少读写阻塞
ALTER DATABASE YourDB SET READ_COMMITTED_SNAPSHOT ON;

四、故障场景三：磁盘空间爆满

4.1 症状识别

报警信息：
- 磁盘剩余空间 < 10%
- INSERT/UPDATE 失败（日志满）
- 备份失败

4.2 应急处理

-- 1. 查看磁盘空间
EXEC xp_fixeddrives;

-- 2. 查看数据库文件大小
SELECT 
    DB_NAME(database_id) AS DBName,
    name AS FileName,
    type_desc,
    size/128.0 AS CurrentSizeMB,
    size/128.0 - FILEPROPERTY(name, 'SpaceUsed')/128.0 AS FreeMB,
    growth/128.0 AS GrowthMB,
    is_percent_growth
FROM sys.master_files
WHERE database_id = DB_ID()
ORDER BY CurrentSizeMB DESC;

-- 3. 查看日志空间使用
DBCC SQLPERF(LOGSPACE);

-- 4. 查看最大表占用的空间
SELECT TOP 10
    OBJECT_NAME(p.object_id) AS TableName,
    SUM(a.total_pages) * 8 / 1024 AS TotalMB,
    SUM(a.used_pages) * 8 / 1024 AS UsedMB,
    SUM(a.data_pages) * 8 / 1024 AS DataMB
FROM sys.partitions p
JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE p.object_id > 100
GROUP BY p.object_id
ORDER BY TotalMB DESC;

4.3 根因定位

-- 查找日志满的原因
-- 原因1：数据库在 FULL 恢复模式但从未备份日志
SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE name = DB_NAME();
-- log_reuse_wait_desc = 'LOG_BACKUP' 表示需要备份日志

-- 原因2：长期未提交的事务阻止日志截断
SELECT 
    st.session_id,
    at.transaction_begin_time,
    DATEDIFF(MINUTE, at.transaction_begin_time, GETDATE()) AS duration_min,
    at.transaction_state,
    s.login_name,
    s.program_name
FROM sys.dm_tran_session_transactions st
JOIN sys.dm_tran_active_transactions at ON st.transaction_id = at.transaction_id
JOIN sys.dm_exec_sessions s ON st.session_id = s.session_id
WHERE at.transaction_state = 2  -- 活动事务
ORDER BY at.transaction_begin_time;

-- 原因3：数据文件增长设置不当（百分比增长）
SELECT 
    name,
    type_desc,
    growth,
    is_percent_growth,
    CASE WHEN is_percent_growth = 1 THEN '⚠️ 危险：百分比增长' ELSE '✅ 正常' END AS Status
FROM sys.database_files;

4.4 解决方案

-- 应急：清理空间
-- 1. 备份日志（如果是 FULL 模式）
BACKUP LOG YourDB TO DISK = 'G:\Backup\YourDB_Log.trn';

-- 2. 或切换到 SIMPLE 模式（开发/测试环境）
ALTER DATABASE YourDB SET RECOVERY SIMPLE;

-- 3. 收缩日志文件
DBCC SHRINKFILE (YourDB_Log, 1024);  -- 收缩到 1GB

-- 4. 收缩数据文件（谨慎！会导致碎片）
DBCC SHRINKFILE (YourDB_Data, 51200);  -- 收缩到 50GB

-- 5. 删除旧备份文件
-- 使用 PowerShell 或 CmdExec

-- 永久修复
-- 1. 修改文件增长为固定值
ALTER DATABASE YourDB MODIFY FILE 
(NAME = YourDB_Data, FILEGROWTH = 1024MB);

-- 2. 建立定期日志备份作业（每小时）

-- 3. 清理历史数据
DELETE FROM LogTable WHERE LogDate < DATEADD(month, -3, GETDATE());

-- 4. 使用分区表快速归档

五、故障场景四：Always On 同步延迟

5.1 症状识别

报警信息：
- 辅助副本数据延迟
- 只读查询返回过期数据
- 故障转移后数据丢失

5.2 应急处理

-- 1. 查看 AG 同步状态
SELECT 
    ag.name AS AGName,
    ar.replica_server_name,
    rs.role_desc,
    rs.synchronization_health_desc,
    rs.connected_state_desc,
    rs.last_connect_error_number,
    rs.last_connect_error_description
FROM sys.availability_groups ag
JOIN sys.dm_hadr_availability_replica_states rs 
    ON ag.group_id = rs.group_id
JOIN sys.availability_replicas ar 
    ON rs.replica_id = ar.replica_id;

-- 2. 查看数据库同步延迟
SELECT 
    DB_NAME(database_id) AS DBName,
    synchronization_state_desc,
    synchronization_health_desc,
    last_commit_time,
    DATEDIFF(SECOND, last_commit_time, GETDATE()) AS seconds_behind,
    log_send_queue_size,
    log_send_rate,
    redo_queue_size,
    redo_rate
FROM sys.dm_hadr_database_replica_states
WHERE is_primary_replica = 0;

-- 3. 查看发送和重做队列
SELECT 
    DB_NAME(database_id) AS DBName,
    log_send_queue_size / 1024 AS log_send_queue_MB,
    redo_queue_size / 1024 AS redo_queue_MB,
    log_send_rate / 1024 AS log_send_rate_KBps,
    redo_rate / 1024 AS redo_rate_KBps
FROM sys.dm_hadr_database_replica_states
WHERE is_primary_replica = 0;

5.3 根因定位

-- 检查网络延迟
SELECT 
    ar.replica_server_name,
    ar.endpoint_url,
    rs.log_send_queue_size,
    rs.log_send_rate
FROM sys.dm_hadr_availability_replica_states rs
JOIN sys.availability_replicas ar ON rs.replica_id = ar.replica_id
WHERE rs.role_desc = 'PRIMARY';

-- 检查辅助副本重做瓶颈
SELECT 
    session_id,
    command,
    status,
    wait_type,
    wait_time,
    blocking_session_id
FROM sys.dm_exec_requests
WHERE database_id = DB_ID('YourDB')
  AND command LIKE '%REDO%';

-- 检查网络带宽
-- 使用性能计数器：Network Interface\Bytes Total/sec

5.4 解决方案

原因	解决方案
网络带宽不足	压缩日志流、升级网络、异步模式
辅助副本 I/O 慢	将辅助副本放在更快的磁盘上
重做线程瓶颈	增加重做线程数（2016+）
大事务	拆分大事务为小批量
同步模式	评估是否可改为异步

-- 修改为异步提交（减轻网络压力）
ALTER AVAILABILITY GROUP [AG_YourDB]
MODIFY REPLICA ON N'SecondaryNode'
WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT);

-- 启用日志流压缩（SQL Server 2016+）
ALTER AVAILABILITY GROUP [AG_YourDB]
MODIFY REPLICA ON N'SecondaryNode'
WITH (COMPRESSION = ENABLED);

六、故障排查决策树

收到报警
    │
    ├─ CPU 飙升 (>80%)
    │   ├─ 查询高CPU会话 → 找到异常查询
    │   ├─ 检查执行计划 → 缺少索引/统计信息过期/参数嗅探
    │   └─ 应急：KILL会话 / 添加索引
    │
    ├─ 数据库阻塞
    │   ├─ 查找阻塞链 → 定位源头会话
    │   ├─ 检查事务 → 长事务/未提交事务
    │   ├─ 检查锁 → 锁升级/死锁
    │   └─ 应急：KILL源头 / 启用RCSI
    │
    ├─ 磁盘空间不足
    │   ├─ 查看各文件大小 → 数据文件/日志文件
    │   ├─ 检查日志 → 未备份日志/长事务阻止截断
    │   ├─ 检查数据增长 → 历史数据积累
    │   └─ 应急：备份日志/收缩文件/删除旧数据
    │
    ├─ AG 同步延迟
    │   ├─ 查看延迟秒数 → 发送队列/重做队列
    │   ├─ 检查网络 → 带宽/延迟
    │   ├─ 检查辅助副本 → I/O瓶颈
    │   └─ 应急：改为异步/压缩传输
    │
    └─ 数据库损坏
        ├─ DBCC CHECKDB → 确定损坏范围
        ├─ 检查备份 → 找到最近的完好备份
        └─ 应急：从备份恢复 / 尝试修复

七、应急预案模板

7.1 故障记录表

CREATE TABLE DBA_IncidentLog (
    IncidentID INT IDENTITY PRIMARY KEY,
    OccurrenceTime DATETIME DEFAULT GETDATE(),
    ResolvedTime DATETIME,
    Severity VARCHAR(20),  -- 'Critical', 'High', 'Medium', 'Low'
    Category VARCHAR(50),   -- 'CPU', 'Blocking', 'Disk', 'AG', 'Corruption'
    Symptom NVARCHAR(500),
    RootCause NVARCHAR(1000),
    Solution NVARCHAR(2000),
    ImpactDuration INT,  -- 分钟
    ImpactScope NVARCHAR(500),
    PreventiveAction NVARCHAR(1000),
    ReportedBy VARCHAR(100),
    ResolvedBy VARCHAR(100)
);

-- 插入故障记录
INSERT INTO DBA_IncidentLog (
    OccurrenceTime, Severity, Category, Symptom, 
    RootCause, Solution, ImpactDuration, PreventiveAction,
    ReportedBy, ResolvedBy
)
VALUES (
    '2024-01-15 14:23:00', 'Critical', 'Blocking',
    '订单系统大面积超时，用户无法下单',
    '应用程序开启事务后未提交，阻塞了所有订单插入',
    'KILL阻塞会话，修复应用程序事务逻辑',
    15,
    '添加应用程序连接池事务超时设置，监控长事务',
    'MonitoringSystem', 'DBA_OnCall'
);

7.2 快速响应脚本

-- 创建一键诊断存储过程
CREATE PROC dbo.sp_EmergencyDiagnosis
AS
BEGIN
    SET NOCOUNT ON;
    
    PRINT '========== 紧急诊断报告 ==========';
    PRINT '生成时间: ' + CONVERT(VARCHAR, GETDATE(), 120);
    PRINT '';
    
    -- 1. 高CPU查询
    PRINT '>>> Top 3 当前高CPU会话:';
    SELECT TOP 3
        session_id,
        cpu_time,
        total_elapsed_time,
        SUBSTRING(st.text, (r.statement_start_offset/2)+1, 200) AS query
    FROM sys.dm_exec_requests r
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
    WHERE r.status NOT IN ('background', 'sleeping')
    ORDER BY r.cpu_time DESC;
    
    -- 2. 阻塞链
    PRINT '>>> 阻塞链信息:';
    SELECT 
        blocked.session_id AS blocked,
        blocking.session_id AS blocking,
        blocked.wait_time/1000 AS wait_sec,
        SUBSTRING(blk.text, 1, 200) AS blocking_query
    FROM sys.dm_exec_requests blocked
    JOIN sys.dm_exec_requests blocking 
        ON blocked.blocking_session_id = blocking.session_id
    CROSS APPLY sys.dm_exec_sql_text(blocking.sql_handle) blk
    WHERE blocked.blocking_session_id > 0;
    
    -- 3. 磁盘空间
    PRINT '>>> 磁盘空间:';
    EXEC xp_fixeddrives;
    
    -- 4. 日志空间
    PRINT '>>> 日志空间使用:';
    DBCC SQLPERF(LOGSPACE);
    
    -- 5. 内存压力
    PRINT '>>> 内存压力指标:';
    SELECT 
        (SELECT cntr_value FROM sys.dm_os_performance_counters 
         WHERE counter_name = 'Page life expectancy') AS PLE,
        (SELECT cntr_value FROM sys.dm_os_performance_counters 
         WHERE counter_name = 'Lazy writes/sec') AS LazyWrites;
    
    -- 6. AG 状态（如果有）
    IF EXISTS (SELECT 1 FROM sys.availability_groups)
    BEGIN
        PRINT '>>> Always On 状态:';
        SELECT 
            ar.replica_server_name,
            rs.role_desc,
            rs.synchronization_health_desc,
            DATEDIFF(SECOND, drs.last_commit_time, GETDATE()) AS seconds_behind
        FROM sys.availability_groups ag
        JOIN sys.dm_hadr_availability_replica_states rs 
            ON ag.group_id = rs.group_id
        JOIN sys.availability_replicas ar 
            ON rs.replica_id = ar.replica_id
        LEFT JOIN sys.dm_hadr_database_replica_states drs 
            ON rs.replica_id = drs.replica_id;
    END
END

八、核心总结

故障类型	应急操作	根因定位	永久修复
CPU 飙升	KILL 异常会话	查询统计/执行计划	添加索引/参数嗅探
数据库阻塞	KILL 源头会话	锁/事务分析	优化事务/启用RCSI
磁盘爆满	备份日志/收缩	文件/日志分析	调整增长/定期清理
AG 延迟	改异步/压缩	网络/重做分析	优化网络/拆分事务

一句话记住本期内容（管理系列收官）：

故障排查五步法——先止血再定位，从现象到根因，应急方案保恢复，永久修复防复发，事后复盘建体系。

管理系列回顾

期数	主题	核心技能
1	安装配置	磁盘分离、内存配置、tempdb优化
2	安全加固	最小权限、动态脱敏、TDE
3	备份恢复	备份链、时间点恢复、恢复演练
4	高可用性	Always On AG、FCI、日志传送
5	自动化运维	SQL Agent、多服务器管理
6	性能监控	DMV、等待统计、性能计数器
7	故障排查	应急响应、根因分析、复盘改进

最终寄语

DBA 的核心价值不是"数据库不宕机"，而是"数据库宕机后能多快恢复"。

备份是你的最后防线，监控是你的早期预警，自动化是你的效率工具，而经验——来自每一次故障排查的复盘——是你最宝贵的财富。

---

📌 本文所有脚本适用于 SQL Server 2016-2022。建议在测试环境验证后应用到生产环境。

《SQL Server 管理系列》到此完结。感谢你的阅读，愿你的数据库永远稳定运行，即使出问题也能从容应对。