SQL Server 2019 Standard中INSERT/UPDATE操作偶发性大幅延迟的根因排查求助

阿华AIGC实验室

2026-4-30

这确实是那种最让人头疼的偶发性性能问题——没固定规律、开发环境复现不了，但一旦出现就影响业务稳定性。结合你已经排除的那些可能性，我给你一套逐步落地的排查方案，重点放在捕获卡顿瞬间的现场数据和验证你怀疑的IO/内部操作这两个核心方向：

一、先捕获卡顿时刻的SQL Server现场数据

偶发问题的核心是「抓现场」，事后分析很难找到根因，所以必须在卡顿发生时拿到实时状态：

卡顿发生时立即执行以下查询（可以写个轻量工具绑定快捷键，或者用SSMS快速查询窗口）：

-- 查看当前所有正在执行的请求，重点关注等待类型、阻塞会话
SELECT 
    r.session_id,
    r.status,
    r.command,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    r.cpu_time,
    r.logical_reads,
    r.writes,
    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 executing_sql
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE r.session_id <> @@SPID; -- 排除当前查询会话

重点盯这几个字段：

wait_type：如果是PAGEIOLATCH_*、WRITELOG、IO_COMPLETION这类IO相关的等待，直接指向IO瓶颈；
blocking_session_id：虽然你排除了死锁和长事务，但要确认是否存在隐性阻塞（比如某个会话持有锁但未触发死锁检测）；
writes：如果卡顿语句的writes数值异常高，可能触发了批量写入或日志强制刷新。

用扩展事件长期监控慢DML操作
扩展事件是轻量级监控工具，不会影响数据库性能，适合长期蹲守偶发问题。可以创建一个专门捕获耗时超过2秒的INSERT/UPDATE的事件会话：

CREATE EVENT SESSION [SlowDML] ON SERVER 
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
    WHERE ([duration]>(2000000) AND ([statement] LIKE '%INSERT%' OR [statement] LIKE '%UPDATE%')))
ADD TARGET package0.event_file(SET filename=N'SlowDML.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=ON);

开启后，卡顿发生时会自动记录到.xel文件，事后用SSMS就能打开分析，能拿到语句详情、执行计划、等待类型等关键信息。

二、验证IO瓶颈的具体方法

你已经排除了CPU、内存问题，现在重点排查磁盘IO：

用性能监视器长期监控磁盘指标
打开Windows的Performance Monitor（perfmon），添加以下计数器，持续观察：
- PhysicalDisk -> % Disk Time：如果持续超过80%，说明磁盘处于高负载状态；
- PhysicalDisk -> Avg. Disk Sec/Read、Avg. Disk Sec/Write：正常应低于20ms，若超过50ms，说明IO响应速度过慢；
- SQL Server:Databases -> Log Flush Wait Time：如果这个值持续偏高，说明日志写入卡顿，大概率是日志文件所在磁盘IO不足，或者日志文件配置不合理（比如自动增长是按百分比，导致频繁扩容）；
- SQL Server:Buffer Manager -> Page life expectancy：如果这个值突然暴跌，可能是内存不足导致频繁换页，不过你已经排除内存问题，可以用来交叉验证。

检查SQL Server文件的IO延迟
用以下查询查看数据文件和日志文件的平均IO延迟：

SELECT 
    DB_NAME(vfs.database_id) AS DatabaseName,
    mf.name AS FileName,
    mf.type_desc AS FileType,
    ROUND(vfs.io_stall_read_ms/CAST(vfs.num_of_reads AS FLOAT),2) AS AvgReadStallMs,
    ROUND(vfs.io_stall_write_ms/CAST(vfs.num_of_writes AS FLOAT),2) AS AvgWriteStallMs
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;

如果某个文件的平均延迟超过50ms，说明该文件所在磁盘存在IO瓶颈。

检查文件配置是否合理
- 确认数据文件和日志文件的自动增长设置：如果是按百分比增长，每次扩容都会引发卡顿，建议改成固定大小（比如每次增长1GB），并提前预分配足够空间；
- 关闭数据库的Auto Close和Auto Shrink选项：这两个功能会导致数据库频繁关闭、收缩，引发不必要的IO开销。

三、排查SQL Server内部的滞后操作

你怀疑的缓存刷新、统计更新等操作，虽然一般不会导致秒级卡顿，但可以针对性排查：

检查统计信息更新时间
如果卡顿刚好触发了统计信息自动更新，可能会导致执行计划重新编译，耗时增加。用以下查询查看最近24小时的统计信息更新：
```
SELECT 
    OBJECT_NAME(s.object_id) AS TableName,
    s.name AS StatName,
    s.last_updated,
    s.rows_sampled
FROM sys.stats s
JOIN sys.tables t ON s.object_id = t.object_id
WHERE s.last_updated > DATEADD(hour, -24, GETDATE());
```
如果卡顿时间和统计更新时间吻合，建议手动更新统计信息，并调整统计更新的阈值。

检查执行计划重新编译情况
偶发的执行计划重新编译也可能导致卡顿，用以下查询查看有多次重新编译的计划：

SELECT 
    cp.objtype,
    cp.usecounts,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS sql_text,
    qs.plan_generation_num
FROM sys.dm_exec_cached_plans cp
JOIN sys.dm_exec_query_stats qs ON cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE qs.plan_generation_num > 1;

如果某个INSERT/UPDATE语句的plan_generation_num很高，说明频繁重新编译，可以考虑添加OPTION (RECOMPILE)提示，或者优化语句的参数化逻辑。

四、虚拟机层面的验证

虽然IT部门声称虚拟机无问题，但还是要确认宿主机层面的资源抢占：