SQL Server 2016快照隔离引发INSERT延迟飙升问题排查

针对你描述的场景（单写入端持续插入、独立读取端偶尔批量查询，无更新/删除操作，查询无需返回新插入行），当ALLOW_SNAPSHOT_ISOLATION和READ_COMMITTED_SNAPSHOT均开启时，读取端以READ COMMITTED隔离级查询触发插入延迟暴涨，而NOLOCK无此现象，核心原因是快照隔离引入了以下额外开销：

1. 版本存储的强制写入与维护开销 #

开启快照隔离后，SQL Server会为所有数据修改操作（包括插入）生成版本记录，并写入tempdb的版本存储区。插入操作原本仅需完成数据页写入+事务日志写入，现在额外增加了：

• 版本记录的序列化与写入I/O（针对tempdb）
• 版本链的维护逻辑（追踪数据行的历史版本）
  当读取端执行READ COMMITTED查询时（因READ_COMMITTED_SNAPSHOT开启，查询会读取版本存储的快照数据），版本存储的访问压力会传导至写入端：若tempdb的I/O性能不足、或版本存储的清理线程（Lazy Writer）跟不上生成速度，插入操作会因等待版本存储资源而延迟飙升。

2. 内存闩锁（Latch）的竞争加剧 #

快照隔离下，版本存储的读写操作会增加tempdb及用户数据库数据页的闩锁竞争：

• 插入操作生成版本记录时，需获取版本存储页的闩锁
• 读取端扫描大量行并关联版本存储时，会持续占用数据页的闩锁
  这种内存级的同步竞争，会让持续插入的线程频繁进入等待状态，直接拉高插入延迟。

3. 事务日志的额外负载 #

版本记录的生成会增加事务日志的写入量：每个插入操作的版本信息都要追加到日志中，额外占用日志驱动器的I/O带宽和存储空间。若日志驱动器存在瓶颈、或日志备份/截断不及时，日志写入延迟会直接拖累插入操作的执行速度。

关于READPAST表现与快照一致的原因 #

READPAST表提示仅会跳过被排他锁（X锁）锁定的行，但在READ_COMMITTED_SNAPSHOT开启的前提下，READ COMMITTED隔离级的READPAST查询依然会依赖版本存储读取快照数据，因此同样会触发上述版本存储与闩锁竞争的开销，导致插入延迟飙升。

针对性解决方案 #

既然业务允许脏读，最直接的优化方式是在读取端查询中使用NOLOCK表提示：完全绕过版本存储的所有开销，消除插入与读取之间的资源竞争。若需保留快照隔离的其他特性，可尝试：

• 优化tempdb配置：创建与CPU核心数匹配的多个数据文件，放置于高速存储介质，降低版本存储的I/O瓶颈
• 限制读取端查询的扫描范围：避免全表扫描，减少版本存储的访问量
• 关闭READ_COMMITTED_SNAPSHOT（仅保留ALLOW_SNAPSHOT_ISOLATION），让默认READ COMMITTED隔离级回归锁机制，但此方式可能导致读取端阻塞写入，需结合业务场景评估。

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