含临时表的存储过程运行时长短波动问题求助

问题背景 #

我们的存储过程依赖临时表运行，耗时波动明显：通常执行需10-15分钟，但偶尔会超过30分钟。对比正常与慢执行场景的执行计划文件后，定位到某段INSERT代码块是性能瓶颈。执行EXPLAIN PLAN FOR SQL PLAN CACHE ENTRY <plan_id>确认，慢执行时的表连接顺序与正常场景存在差异。

问题代码块如下：

INSERT INTO #TMP_DATI_SALDI_LORDI_BASE (
                 "COD_SCENARIO","COD_PERIODO","COD_CONTO","COD_DEST1","COD_DEST2","COD_DEST3","COD_DEST4","COD_DEST5"
                ,"IMPORTO","COD_VALUTA","IMPORTO_VALUTA_ORIGINARIA","COD_VALUTA_ORIGINARIA","NOTE"
            )
            (   SELECT
                     SCEN_P.SCENARIO
                    ,SCEN_P.PERIOD
                    ,ACCOUT_ADJ.ATTRIBUTO1 AS "COD_CONTO"
                    ,DATAS_rev.COD_DEST1
                    ,DATAS_rev.COD_DEST2
                    ,DATAS_rev.COD_DEST3
                    ,__typed_NString__($1, 50)
                    ,'RPT_NON'
                    ,SUM(
                            CASE WHEN INFO.INCOT = 'FOB' THEN
                                CASE ACCOUT_rev.ATTRIBUTO1 WHEN 'CalcInsurance' THEN
                                    0
                                ELSE
                                    DATAS_rev.IMPORTO
                                END
                            ELSE
                                DATAS_rev.IMPORTO
                            END
                            * (DATAS_ADJ.IMPORTO - DATAS.IMPORTO)
                        )
                    ,DATAS_rev.COD_VALUTA
                    ,SUM(
                            CASE WHEN INFO.INCOT = 'FOB' THEN
                                CASE ACCOUT_rev.ATTRIBUTO1 WHEN 'CalcInsurance' THEN
                                    0
                                ELSE
                                    DATAS_rev.IMPORTO_VALUTA_ORIGINARIA
                                END
                            ELSE
                                DATAS_rev.IMPORTO_VALUTA_ORIGINARIA
                            END
                            * (DATAS_ADJ.IMPORTO_VALUTA_ORIGINARIA - DATAS.IMPORTO_VALUTA_ORIGINARIA)
                        )
                    ,DATAS_rev.COD_VALUTA_ORIGINARIA
                    ,'CPM_SP_CACL_FY_E3 Parts Option ADJ'
                 FROM #TMP_TAGERT_SCEN_P SCEN_P
                 
                INNER JOIN #TMP_DATI_SALDI_LORDI_BASE DATAS_rev
                   ON DATAS_rev.COD_SCENARIO        = SCEN_P.SCENARIO
                  AND DATAS_rev.COD_PERIODO         = SCEN_P.PERIOD
                  AND LEFT(DATAS_rev.COD_DEST3, 1)  = 'O'
                  
                INNER JOIN CONTO ACCOUT_rev
                   ON ACCOUT_rev.COD_CONTO          = DATAS_rev.COD_CONTO
                  AND ACCOUT_rev.ATTRIBUTO1 IN ('CalcFOB','CalcInsurance')   --FOB,Insurance(Ocean freight is Nothing by Option)
                  
                INNER JOIN #DSL DATAS
                   ON DATAS.COD_SCENARIO            = 'LAUNCH'
                  AND DATAS.COD_PERIODO             = 12
                  AND DATAS.COD_DEST1               = 'NC'
                  AND DATAS.COD_DEST2               = 'NC'
                  AND DATAS.COD_DEST3               = 'F001'
                  AND DATAS.COD_DEST4               = DATAS_rev.COD_DEST4
                  AND DATAS.COD_DEST5               = 'INP'
                  
                INNER JOIN CONTO ACCOUT
                   ON ACCOUT.COD_CONTO              = DATAS.COD_CONTO
                  AND ACCOUT.ATTRIBUTO2             = 'E3'
                  
                INNER JOIN CONTO ACCOUT_ADJ
                   ON ACCOUT_ADJ.ATTRIBUTO3         = DATAS.COD_CONTO
                  AND ACCOUT_ADJ.ATTRIBUTO2         = 'HE3'
                  
                  
                INNER JOIN #DSL DATAS_ADJ
                   ON LEFT(DATAS_ADJ.COD_SCENARIO,4) = LEFT(SCEN_P.SCENARIO,4)
                  AND DATAS_ADJ.COD_PERIODO         = 12
                  AND DATAS_ADJ.COD_DEST1           = DATAS.COD_DEST1
                  AND DATAS_ADJ.COD_DEST2           = DATAS.COD_DEST2
                  AND DATAS_ADJ.COD_DEST3           = DATAS.COD_DEST3
                  AND DATAS_ADJ.COD_DEST4           = DATAS.COD_DEST4
                  AND DATAS_ADJ.COD_DEST5           = DATAS.COD_DEST5
                  AND DATAS_ADJ.COD_CONTO           = ACCOUT_ADJ.COD_CONTO

                 LEFT OUTER JOIN #TMP_KDPWT_INCOTERMS INFO
                   ON INFO.P_CODE                   = DATAS.COD_DEST4
                   
                   
                GROUP BY
                     SCEN_P.SCENARIO,SCEN_P.PERIOD,ACCOUT_ADJ.ATTRIBUTO1,DATAS_rev.COD_DEST1,DATAS_rev.COD_DEST2
                    ,DATAS_rev.COD_DEST3, DATAS.COD_DEST4,DATAS_rev.COD_VALUTA,DATAS_rev.COD_VALUTA_ORIGINARIA,INFO.INCOT
            )

问题分析 #

SQL优化器会基于表的统计信息选择连接顺序，但临时表的统计信息易出现偏差：比如数据量波动大、统计信息未及时更新，都会导致优化器做出低效的连接顺序选择，进而引发性能骤降。

解决方案 #

1. 更新临时表统计信息 #

在临时表加载完数据后，强制更新统计信息，帮助优化器获取准确的数据分布：

UPDATE STATISTICS #TMP_TAGERT_SCEN_P;
UPDATE STATISTICS #TMP_DATI_SALDI_LORDI_BASE;
UPDATE STATISTICS #DSL;
UPDATE STATISTICS #TMP_KDPWT_INCOTERMS;

2. 使用连接提示固定执行顺序 #

通过FORCE ORDER查询提示，强制优化器按照SQL中表的书写顺序执行连接，避免优化器选择低效顺序：
修改INSERT语句的SELECT部分，添加提示：

SELECT /*+ FORCE ORDER */
     SCEN_P.SCENARIO
    ,SCEN_P.PERIOD
    -- 剩余SELECT内容保持不变

3. 创建计划指南绑定最优执行计划 #

如果FORCE ORDER无法满足需求，可以直接绑定正常执行时的最优计划：

1. 导出正常执行场景的执行计划XML（可通过sys.dm_exec_query_plan或SSMS导出实际执行计划）
2. 创建计划指南：

EXEC sp_create_plan_guide 
    @name = N'Guide_Insert_TMP_DATI_SALDI_LORDI_BASE',
    @stmt = N'INSERT INTO #TMP_DATI_SALDI_LORDI_BASE (...) (...)', -- 替换为完整的INSERT语句文本
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = NULL,
    @hints = N'OPTION (USE PLAN N''<执行计划XML内容>'')';

4. 优化临时表索引 #

为临时表添加针对性索引，减少连接时的数据扫描范围：
比如为#TMP_DATI_SALDI_LORDI_BASE创建复合索引：

CREATE NONCLUSTERED INDEX IX_TMP_DATI_SALDI_LORDI_BASE_SCEN_PERIOD_DEST3 
ON #TMP_DATI_SALDI_LORDI_BASE (COD_SCENARIO, COD_PERIODO, COD_DEST3);

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