SQL Server特定时段磁盘I/O延迟问题排查求助

咱们一步步来定位这个每天凌晨4:30准时触发的高磁盘I/O问题，结合你提到的存储和开发团队均确认自身无异常的情况，重点从SQL Server内部、系统层面以及实时监控三个方向入手：

1. 先查SQL Server代理的定时作业 #

这是最常见的凌晨任务触发源，比如备份、索引重建、数据清理等作业都可能导致高I/O。用以下SQL查询筛选出4:30左右启动的作业：

SELECT 
    j.name AS 作业名称,
    s.name AS 调度名称,
    CONVERT(VARCHAR(8), s.active_start_time) AS 启动时间(HHMMSS),
    j.description AS 作业描述
FROM 
    msdb.dbo.sysjobs j
JOIN 
    msdb.dbo.sysjobschedules js ON j.job_id = js.job_id
JOIN 
    msdb.dbo.sysschedules s ON js.schedule_id = s.schedule_id
WHERE 
    s.active_start_time BETWEEN 43000 AND 44000 -- 筛选4:30-4:40启动的调度
ORDER BY 
    s.active_start_time;

同时检查这些作业的历史执行记录，确认是否在问题时段运行：

SELECT 
    j.name AS 作业名称,
    CONVERT(VARCHAR(8), h.run_date) AS 执行日期(YYYYMMDD),
    CONVERT(VARCHAR(8), h.run_time) AS 执行时间(HHMMSS),
    h.run_duration AS 执行时长(秒),
    h.message AS 执行信息
FROM 
    msdb.dbo.sysjobhistory h
JOIN 
    msdb.dbo.sysjobs j ON h.job_id = j.job_id
WHERE 
    h.run_date = CONVERT(VARCHAR(8), DATEADD(DAY, -1, GETDATE()), 112) -- 前一天的日期
    AND h.run_time BETWEEN 43000 AND 93000 -- 覆盖整个问题时段
ORDER BY 
    h.run_time;

2. 排查维护计划 #

很多团队会用维护计划来统一管理数据库的例行维护任务，这些任务往往也是高I/O大户。你可以在SSMS的【管理】->【维护计划】里直观查看，也可以用SQL查询定位：

SELECT 
    mp.name AS 维护计划名称,
    s.name AS 调度名称,
    CONVERT(VARCHAR(8), s.active_start_time) AS 启动时间(HHMMSS)
FROM 
    msdb.dbo.sysmaintplan_plans mp
JOIN 
    msdb.dbo.sysmaintplan_subplans sp ON mp.id = sp.plan_id
JOIN 
    msdb.dbo.sysschedules s ON sp.schedule_id = s.schedule_id
WHERE 
    s.active_start_time BETWEEN 43000 AND 44000;

3. 检查Windows系统层面的定时任务 #

别忽略系统级别的任务，比如Windows任务计划里的脚本（调用sqlcmd、bcp批量导入导出）、第三方备份工具的快照任务，这些可能不会在SQL Server内部留下记录。

• 打开【任务计划程序】，筛选触发时间为凌晨4:30左右的任务，重点关注那些涉及SQL Server操作的任务；
• 询问运维团队是否有服务器级别的备份、磁盘巡检等任务在该时段执行。

4. 实时监控问题时段的I/O来源 #

等下次4:30问题出现时，立刻运行以下查询，找出当前最消耗磁盘I/O的会话和SQL语句：

SELECT 
    DB_NAME(r.database_id) AS 数据库名称,
    r.session_id AS 会话ID,
    r.command AS 执行命令,
    r.reads AS 物理读取次数,
    r.writes AS 物理写入次数,
    t.text AS 执行的SQL语句
FROM 
    sys.dm_exec_requests r
CROSS APPLY 
    sys.dm_exec_sql_text(r.sql_handle) t
WHERE 
    r.reads > 0 OR r.writes > 0
ORDER BY 
    r.reads + r.writes DESC;

同时查看哪个数据库文件的I/O延迟最高，确认是否是特定库的任务导致：

SELECT 
    DB_NAME(vfs.database_id) AS 数据库名称,
    mf.name AS 文件名称,
    mf.type_desc AS 文件类型(数据/日志),
    ROUND(vfs.io_stall_read_ms / (vfs.num_of_reads + 1), 2) AS 平均读取延迟(ms),
    ROUND(vfs.io_stall_write_ms / (vfs.num_of_writes + 1), 2) AS 平均写入延迟(ms),
    vfs.num_of_reads AS 总读取次数,
    vfs.num_of_writes AS 总写入次数
FROM 
    sys.dm_io_virtual_file_stats(NULL, NULL) vfs
JOIN 
    sys.master_files mf ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
ORDER BY 
    平均读取延迟(ms) + 平均写入延迟(ms) DESC;

5. 验证存储层面的I/O情况 #

虽然存储团队说LUN无故障，但可以自己收集数据验证：

• 用Windows性能计数器（PerfMon）采集PhysicalDisk\Avg. Disk Sec/Read、PhysicalDisk\Avg. Disk Sec/Write，同时对比SQL Server的SQL Server:Buffer Manager\Page reads/sec、SQL Server:Buffer Manager\Page writes/sec，看是否是SQL的I/O请求量激增导致了存储延迟，还是存储本身的瓶颈（比如同一LUN上的其他服务器也在该时段产生高I/O）；
• 检查SQL Server日志，看是否有数据库文件自动增长的记录（搜索“Autogrow of file”），自动增长操作会瞬间消耗大量磁盘I/O。

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