必须用TOP (n) + WHILE循环 + 显式COMMIT，且WHERE条件需走索引；否则易锁表、拖慢主库、触发日志暴涨。因单次大删导致事务日志填满、锁升级至表级、执行计划退化为全表扫描，而分批删可控制锁粒度、分段写日志、及时释放资源。
直接上结论：SQL Server 2022 分批删除大表数据，必须用 TOP (n) + WHILE 循环 + 显式 COMMIT，且 WHERE 条件要能走索引；靠 OFFSET 或全表扫描式 DELETE 会锁表、拖慢主库、触发日志暴涨。
为什么不能直接 DELETE FROM table WHERE ...
SQL Server 中一次性删几十万行以上，会立刻暴露三个硬伤：
- 事务日志快速填满——尤其在
FULL恢复模式下，每行删除都记完整日志，log_reuse_wait_desc可能卡在LOG_BACKUP - 锁升级（Lock Escalation）：默认 5000 行就从行锁升到表锁，阻塞所有读写
- 执行计划退化：没索引的
WHERE条件会触发全表扫描，CPU 和 I/O 爆涨
哪怕表只有 200 万行，没分批就跑，DBA 很可能在 SSMS 里直接 KILL 掉会话。
正确写法：TOP + 循环 + 索引驱动游标
核心是每次只删固定行数，并用已删数据的最大主键（或时间字段）作为下一批起点，避免重复扫描和 OFFSET 性能坍塌。
示例（删 logs 表中 2022 年前的数据）：
DECLARE @batch_size INT = 5000; DECLARE @rows_affected INT = 1; <p>WHILE @rows_affected > 0 BEGIN BEGIN TRANSACTION;</p><pre class='brush:php;toolbar:false;'>DELETE TOP (@batch_size) FROM logs WHERE created_at < '2022-01-01' AND id <= (SELECT MAX(id) FROM logs WHERE created_at < '2022-01-01'); SET @rows_affected = @@ROWCOUNT; COMMIT TRANSACTION; -- 防止连续高负载，可选毫秒级暂停 -- WAITFOR DELAY '00:00:00.1';
END;
关键点：
-
id必须有索引（聚集或非聚集），否则子查询SELECT MAX(id)...会全表扫 - 删完立刻
COMMIT，释放日志空间和锁资源；不加事务控制，整个 WHILE 就包在一个长事务里 - 别用
OFFSET—— SQL Server 不支持DELETE ... ORDER BY ... OFFSET，强行模拟效率极低
更稳方案：用 CTE + OUTPUT 避免二次扫描
当删除条件复杂（比如多字段组合判断）、或需精确控制删除顺序时，CTE + OUTPUT 是更可靠的选择，它一次定位、一次删除、一次取回 ID，不依赖外部变量。
DECLARE @batch_size INT = 5000;
DECLARE @last_id BIGINT = 0;
DECLARE @rows_affected INT = 1;
<p>WHILE @rows_affected > 0
BEGIN
BEGIN TRANSACTION;</p><pre class='brush:php;toolbar:false;'>WITH batch AS (
SELECT TOP (@batch_size) id
FROM logs
WHERE created_at < '2022-01-01'
AND id > @last_id
ORDER BY id
)
DELETE l
OUTPUT DELETED.id INTO #temp_ids
FROM logs l
INNER JOIN batch b ON l.id = b.id;
SELECT @rows_affected = COUNT(*) FROM #temp_ids;
SELECT @last_id = MAX(id) FROM #temp_ids;
TRUNCATE TABLE #temp_ids;
COMMIT TRANSACTION;END;
注意：
- 必须提前建好临时表
#temp_ids（结构为id BIGINT），否则OUTPUT INTO报错 -
ORDER BY id保证顺序可控，配合id > @last_id实现游标推进 - 该方式比纯
TOP多一次 JOIN，但避免了子查询重复评估，对高并发环境更友好
容易被忽略的坑
真正线上出问题的，往往不是语法写错，而是这些细节：
-
created_at字段没索引？那上面所有WHERE都是全表扫描——先补CREATE NONCLUSTERED INDEX IX_logs_created_at ON logs(created_at) INCLUDE (id); - 没设
LOCK_TIMEOUT，某次删除卡住，整个循环挂死——建议开头加SET LOCK_TIMEOUT 5000; - 误删正在被其他进程读取的热数据？加
READPAST提示（如FROM logs WITH (READPAST)）跳过被锁行 - 没监控日志增长——执行前查
DBCC SQLPERF(LOGSPACE)，过程中盯sys.dm_db_log_stats
最后提醒一句：分批逻辑再稳，也得在业务低峰期跑，且必须提前备份目标表或开启数据库快照。删掉的数据，不会自己长回来。
