SQL Server UPDATE操作导致子查询中Index Seek执行速度异常降低问题

原始查询（Query1） #

SELECT
    stage.IDContratto,
    SUM(stageReg.Costo) AS Costo
FROM STAGING.TabContrattiRedditivita AS stage
INNER JOIN STAGING.TabCommesse AS stageCom ON stage.CodiceContratto = stageCom.CodiceContrattoCommessa
INNER JOIN STAGING.TabRegistrazioneOreRisorse AS stageReg
    ON stageCom.CodiceCommessa = stageReg.CodiceCommessaCalcolato
    AND stageReg.DataRegistrazione BETWEEN stage.StartDate AND stage.EndDate
WHERE stageCom.SeMotivoNonFatturabilePerditaCommessa = 1
GROUP BY stage.IDContratto

涉及表数据量：

• TabContrattiRedditivita：1.6万行
• TabCommesse：4.9万行
• TabRegistrazioneOreRisorse：680万行
  Query1返回结果：1200行，单独执行耗时约3分钟，符合预期。

异常查询（Query2） #

将Query1作为子查询更新TabContrattiRedditivita时，耗时飙升到16分钟以上：

UPDATE STAGING.TabContrattiRedditivita
SET
    ActualCostoCommesseNonFatturanti += costi.Costo,
    TotaleCostoCommesseNonFatturanti += costi.Costo
FROM STAGING.TabContrattiRedditivita AS stage
INNER JOIN (Query1) AS costi ON stage.IDContratto = costi.IDContratto

排查确认更新写入本身耗时不足1秒，耗时集中在TabRegistrazioneOreRisorse的Index Seek操作上。

特殊验证现象 #

将三个表数据复制到临时表并创建相同索引后，临时表版本的Query2耗时仅3分10秒，性能恢复正常。其他尝试的CTE、先存临时表再更新等方案均无效果，Query2执行计划存在ExcessiveGrant告警。 #

1. Halloween保护机制触发：SQL Server为了避免更新操作修改读取数据源导致同一行被重复处理（即Halloween效应），在更新永久表的执行计划中自动插入了Eager Spool假脱机操作，需要将海量索引数据临时落盘处理，导致Index Seek操作耗时大幅上升。而临时表为会话私有对象，优化器判断无需触发Halloween保护，因此没有额外的假脱机开销，性能正常。
2. ExcessiveGrant内存授予异常：执行计划中该告警表示查询优化器估计的内存需求远高于实际使用量，过量的内存分配会挤占数据缓存空间，导致Index Seek操作需要频繁读取磁盘，进一步放大性能损耗。
3. 索引键顺序不合理：现有非聚集索引IX_CostiCommessa将DataRegistrazione作为前导列，而查询中是先等值匹配CodiceCommessaCalcolato再范围匹配DataRegistrazione，索引匹配效率本身存在优化空间，在触发保护机制后性能问题被放大。

1. 调整索引结构（长效方案）：将索引键顺序调换，优先匹配等值条件列，可大幅降低Index Seek的开销：

CREATE NONCLUSTERED INDEX [IX_CostiCommessa_Optimized]
ON [STAGING].[TabRegistrazioneOreRisorse] (
    [CodiceCommessaCalcolato] ASC,
    [DataRegistrazione] ASC
)
INCLUDE ( [Costo] )
WITH (FILLFACTOR = 100, ONLINE = ON);
-- 验证性能提升后可删除原索引
DROP INDEX IF EXISTS [IX_CostiCommessa] ON [STAGING].[TabRegistrazioneOreRisorse];

2. 禁用不必要的Halloween保护：确认当前更新逻辑不会出现同一行重复更新的场景后，可通过查询提示禁用Eager Spool保护：

UPDATE STAGING.TabContrattiRedditivita
SET
    ActualCostoCommesseNonFatturanti += costi.Costo,
    TotaleCostoCommesseNonFatturanti += costi.Costo
FROM STAGING.TabContrattiRedditivita AS stage
INNER JOIN (Query1) AS costi ON stage.IDContratto = costi.IDContratto
OPTION (QUERYTRACEON 8690);

3. 修正内存授予异常：添加内存限制提示避免内存浪费：

UPDATE STAGING.TabContrattiRedditivita
SET
    ActualCostoCommesseNonFatturanti += costi.Costo,
    TotaleCostoCommesseNonFatturanti += costi.Costo
FROM STAGING.TabContrattiRedditivita AS stage
INNER JOIN (Query1) AS costi ON stage.IDContratto = costi.IDContratto
OPTION (MAX_GRANT_PERCENT = 1);

以上方案可单独验证或组合使用。 #

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