SQL Server 2017百万行数据表批量更新执行过慢问题咨询

问题解答 #

1. 简单恢复模式下是否仍需要分批提交事务？ #

仍然需要，核心原因如下：

• 简单恢复模式只是取消了事务日志的备份链保留逻辑，不会省略事务日志的生成。单事务全表更新150万行时，会持有大范围排他锁数个小时，完全阻塞其他业务对该表的读写，同时会导致事务日志短时间内暴涨。如果中途执行失败，回滚时间会远长于执行时间，甚至可能因为磁盘日志空间不足直接执行失败。
• 分批提交可以控制单次锁持有时长，避免长时间阻塞业务，同时控制日志文件的增长幅度，中途出错仅需要回滚当前批次，无需从头重新执行。

2. 能否直接执行无显式事务的全表更新语句？ #

极度不推荐。单条UPDATE语句本身就是隐式事务，执行逻辑和你包了一层显式事务完全一致，要么全成功要么全失败，同样会遇到锁持有时间长、回滚风险高、日志暴涨的问题，生产环境几乎不会对大表执行全量单事务更新。

优化后脚本提速的核心原理 #

你最初的脚本没有加过滤条件，每次循环都会全表扫描所有行，哪怕已经更新完成的行也会重复执行replace计算，循环多少次就全表扫描多少次，绝大多数操作都是无用功，执行效率自然极低。
新增过滤条件后，每次仅扫描、更新符合条件的未修改行，已经更新完成的行不会再被重复处理，无用操作被完全砍掉，所以执行速度大幅提升。

更优方案建议 #

• 优化过滤条件适配索引：如果SendPath字段存在索引，将left(SendPath,31) = '\\Server1\myFolder\<'改为SendPath like '\\Server1\myFolder\%'，前缀匹配的LIKE语法可以走索引范围扫描，比针对字段计算的left函数执行效率高得多。
  注意：SET ROWCOUNT是SQL Server即将废弃的语法，官方推荐使用TOP语法实现分批操作，改写后的标准分批脚本如下：

declare @LastCount int
set @LastCount = 1
while (@LastCount > 0)
begin
    begin tran
        update top (50000) myTbl 
        set SendPath = replace(SendPath,'\\Server1\myFolder\','\\Server2\myFolder') ,
            ReceivePath = replace(ReceivePath,'\\Server1\myFolder\','\\Server2\myFolder') 
        where SendPath like '\\Server1\myFolder\%'

        set @LastCount = @@ROWCOUNT
    commit tran
    -- 可选：如果是业务热表，加1秒延迟避免IO打满影响正常业务
    -- WAITFOR DELAY '00:00:01'
end

• 如果表存在自增主键/有序聚类索引，可改用主键范围分批，执行效率比TOP更稳定：

declare @id int = 0, @maxId int
select @maxId = max(id) from myTbl
while @id < @maxId
begin
    begin tran
        update myTbl 
        set SendPath = replace(SendPath,'\\Server1\myFolder\','\\Server2\myFolder') ,
            ReceivePath = replace(ReceivePath,'\\Server1\myFolder\','\\Server2\myFolder') 
        where id between @id and @id + 50000
        and SendPath like '\\Server1\myFolder\%'

        set @id = @id + 50000
    commit tran
end

• 如果是离线更新、更新期间无业务读写，可临时禁用表上的非聚集索引，更新完成后再重建，能进一步大幅提升更新速度。

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