SQL Server 2019索引重建后作业变慢，SET ARITHABORT需反复调整

核心成因 #

这是执行计划缓存与会话SET选项不兼容结合索引重建触发统计信息更新导致的问题：

1. 索引重建默认会更新表的统计信息（除非指定NOSTATISTICS参数），统计信息变更会触发存储过程执行计划的重新编译。
2. SET ARITHABORT是SQL Server判断执行计划缓存键的关键会话选项之一——不同的ARITHABORT设置会被视为不同的会话环境，生成独立的执行计划缓存条目。
3. 第一次索引重建后，存储过程在ARITHABORT OFF的默认会话环境下生成了适配新统计信息的低效计划（比如选择错误索引、执行全表扫描），导致CPU飙升、耗时增加；添加SET ARITHABORT ON后，会话环境变更触发重新编译，生成适配当前数据分布的高效计划，性能恢复。
4. 下次索引重建后，统计信息再次更新，此时ARITHABORT ON环境下生成的新计划又因数据分布变化变得低效；注释该语句回到OFF环境，再次触发重新编译生成另一个高效计划，如此反复。
5. 多租户环境下不同客户库的数据分布差异（部分客户数据量波动大）会放大这个问题，导致通用执行计划无法适配所有租户场景。

解决办法 #

1. 强制存储过程每次执行重新编译 #

在调用存储过程时添加WITH RECOMPILE，确保每次执行都基于当前租户库的数据分布生成最优计划：

SET @dynSql = 'USE ' + @DatabaseName + ';';
SET @dynSql = @dynSql +  'EXEC Nightly_CustomerRewards_TransactionEntry_Update WITH RECOMPILE;';

或者直接修改存储过程定义，添加编译选项：

ALTER PROCEDURE Nightly_CustomerRewards_TransactionEntry_Update
WITH RECOMPILE
AS
-- 存储过程原有逻辑

2. 统一会话关键SET选项 #

在动态SQL中固定设置SQL Server推荐的稳定执行计划所需的会话选项，避免因环境差异导致计划缓存碎片化：

SET @dynSql = 'USE ' + @DatabaseName + ';';
SET @dynSql = @dynSql +  'SET ARITHABORT ON; SET ANSI_NULLS ON; SET ANSI_PADDING ON; SET ANSI_WARNINGS ON; SET CONCAT_NULL_YIELDS_NULL ON; SET QUOTED_IDENTIFIER ON;';
SET @dynSql = @dynSql +  'EXEC Nightly_CustomerRewards_TransactionEntry_Update;';

3. 优化统计信息更新策略 #

索引重建时默认的统计信息更新可能采样率不足，改为手动执行全量扫描更新统计信息，确保统计数据准确：

-- 在索引重建后，针对存储过程涉及的表执行
UPDATE STATISTICS dbo.YourTargetTable WITH FULLSCAN;

也可以修改索引重建语句，禁用自动统计信息更新，改为手动统一更新：

ALTER INDEX ALL ON dbo.YourTargetTable REBUILD WITH (NOSTATISTICS);

4. 启用数据库自动优化 #

SQL Server 2019的自动优化功能可以自动检测低效执行计划，并替换为历史上的最优计划：

ALTER DATABASE [YourManagementDB] SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);

（需确保数据库兼容级别为150及以上）

5. 拆分存储过程逻辑 #

如果存储过程处理的数据量较大，可拆分为分批次处理的逻辑，降低单次执行的负载，减少执行计划对数据分布的敏感度：

-- 示例：分批次更新数据
DECLARE @BatchSize INT = 1000;
DECLARE @LastId INT = 0;
WHILE EXISTS(SELECT 1 FROM dbo.TransactionEntry WHERE Id > @LastId)
BEGIN
    UPDATE TOP(@BatchSize) dbo.TransactionEntry
    SET RewardPoints = CalculatedPoints
    WHERE Id > @LastId;
    SET @LastId = SCOPE_IDENTITY();
END

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