主页 / dba / 问题 / 189020
Accepted
Alex Friedman
Asked: 2017-10-23 00:42:02 +0800 CST

由于临时表，统计信息更新后执行计划错误

  • 772

存储过程查询有时会在其中一个表的统计信息更新后得到一个糟糕的计划，但之后可以立即重新编译为好的计划。相同的编译参数。

问题似乎来自在 SP 中创建然后加入的小型临时表。糟糕的计划在临时表上警告连接列没有统计信息。是什么赋予了？

SQL Server 2016 SP1 CU4，具有 2014 兼容级别

糟糕的计划：

糟糕的计划截图

好计划：

好计划截图

存储过程

USE AppDB
GO
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [MySchema].[MySP]
    @MyId VARCHAR(50),
    @Months INT
AS
BEGIN

    SET NOCOUNT ON

    SELECT * 
    INTO #MyTemp
    FROM AppDB.MySchema.View_Feeder vf WITH (NOLOCK)
    WHERE vf.MyId = @MyId AND vf.Status IS NOT NULL

    SELECT wd.Col1
         , vp.Col2
         , vp.Col3 
    FROM AppDB.MySchema.View_VP vp WITH (FORCESEEK)
    INNER JOIN #MyTemp wd ON wd.Col1 = vp.Col1
    WHERE vp.Col3 > DATEADD(MONTH, @Months * -1, GETDATE())

END

内部视图

USE AppDB
GO
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE VIEW [MySchema].[View_VP]
AS

    SELECT pp.Col1,
           pd.Col2 AS Col2, 
           MAX(pp.Col4) AS Col3
    FROM P_DB..LargeTable pp WITH (NOLOCK)
    INNER JOIN P_DB..SmallTable pd WITH (NOLOCK) ON pp.P_Id = pd.P_Id
    WHERE pp.[Status] IN (3, 4)
    GROUP BY pp.Col1, pd.Col2

计划

编辑好的计划和坏的计划。

附加信息

当时FORCESEEK添加了提示以尝试处理同样的问题并稳定计划。无论如何，不​​管有没有它，我真的很想了解这里发生了什么。

我无法随意重现该问题，因此很难说用SELECT INTO显式表替换 是否会有所作为。但是，我相信它的行为方式应该相同。

SELECT
    database_id, 
    is_auto_create_stats_on, 
    is_auto_update_stats_on, 
    is_auto_update_stats_async_on
FROM sys.databases
WHERE
    database_id IN (2, <relevant user databases>)

返回：

  database_id   is_auto_create_stats_on   is_auto_update_stats_on   is_auto_update_stats_async_on  
 ------------- ------------------------- ------------------------- ------------------------------- 
  2             1                         1                         0                              
  7             1                         1                         1                              
  37            1                         1                         1

很明显，这种搜索很糟糕，但问题是为什么它一开始就没有做好搜索。

查询没有返回 1m 行，估计是错误的。输出可能会有细微的变化，但行数总是很低（最多可能数百）。

即使是返回相对多行的那些也会生成由 theId而不是由 the搜索的计划status（如您所见，这不是选择性的）。无论编译什么值，我似乎都无法重现状态寻求计划。我什至尝试waitfor delay在临时表的创建和第二个查询之间添加一个，并在第二个会话中更新统计信息/重新编译，也没有任何效果。

sql-server optimization

1 个回答

  1. Best Answer

    糟糕的计划在临时表上警告连接列没有统计信息。是什么赋予了？

    这可能有一个更深奥的原因，但更可能是一个简单的统计创建失败。例如，当任务无法获得所需的内存资源时，或者统计创建受到限制（并发编译过多）时，可能会发生这种情况。请参阅Microsoft SQL Server 2008 中查询优化器使用的 Microsoft 白皮书统计信息。您可以通过查看自动统计分析器或扩展事件以及大约同时的其他事件来进一步调试。

    也就是说，需要更多的信息和调查才能将计划选择的责任归咎于丢失的临时表统计信息。即使没有详细的统计信息，优化器仍然可以看到临时表的总基数，这似乎是这里的一个重要因素。

    ...但可以立即重新编译为好的计划。相同的编译参数。

    @Months参数可能相同，但临时表中的行数（来自未知视图）View_Feeder不同，并且提供的计划不显示 的值@MyId。

    从可用信息来看：“好”计划（仅估计，不提供性能数据）基于包含4 行的临时表。“坏计划”基于一个有114 行的临时表。当然，缺少密度和直方图信息可能无济于事，但很容易看出优化器如何为 4 行和 114 行选择不同的计划，尽管这些计划的密度和分布未知。

    如果对不依赖于临时表的计划运算符的估计大大偏离，这是一个强烈的信号，表明当前的主表统计信息不能代表基础数据。问题中缺乏信息使得这无法评估。

    然而，可以看到优化器被要求在次优选项之间进行选择。所提出的这两个计划都不是一个“明显不错”的选择，因为两者都涉及查找（缺少“覆盖”索引）和后期过滤（见下文）。特别是查找具有与之相关的高成本，这敏感地取决于基数估计。

    使用视图会限制优化器和提示选项：

    • 视图包含一个GROUP BY防止谓词vp.Col3 > DATEADD(MONTH, @Months * -1, GETDATE())被下推的，即使转换在这种非常特殊的情况下是有效的。
      • 将视图内联到查询将提供一种更早过滤日期/时间列的自然方法（尽管问题没有说明重构查询是否是一种选择）。
    • 不可能在视图上提示索引，而FORCESEEK只是要求优化器找到任何索引搜索计划（不一定使用您喜欢的索引）。删除视图同样会删除此限制。

    允许谓词下推也应该在大表上打开索引机会。例如：

    CREATE INDEX give_me_a_good_name
ON dbo.LargeTable (Col1, [Status], Col4) 
INCLUDE (P_Id);

    ...为重写的查询提供了良好的访问路径：

    DECLARE @Date datetime = DATEADD(MONTH, @Months * -1, GETDATE());

SELECT
    MT.Col1,
    ST.Col2,
    MAX(LT.Col4)
FROM #MyTemp AS MT
JOIN dbo.LargeTable AS LT
    ON LT.Col1 = MT.Col1
JOIN dbo.SmallTable AS ST
    ON ST.P_id = LT.P_Id
WHERE
    LT.[Status] IN (3, 4)
    AND LT.Col4 > @Date
GROUP BY
    MT.Col1,
    ST.Col2
OPTION (RECOMPILE);

    示例计划

    另一个考虑因素是临时表和统计信息缓存的影响，如我的文章存储过程中的临时表和解释的临时表缓存中所述。如果一个好的计划取决于临时对象的当前UPDATE STATISTICS #MyTemp;内容，那么在主查询之前显式地添加OPTION (RECOMPILE)到主查询中可能是一个很好的解决方案。

    或者，如果一个特定的计划形状对于此查询始终是最佳的，那么您有许多可用的选项，包括各种提示、计划指南和查询存储计划强制。您可能会发现使用表变量而不是临时表是更好的选择，因为它有利于低基数情况，并且不提供（或依赖）统计信息。

    总而言之，在担心临时表上偶尔丢失统计信息的原因之前，应该进行一些一般性的改进：

    • 确保统计数据对优化器具有代表性和有用
    • 检查一系列参数值的实际值与估计值
    • 通过改进现有索引为查询提供良好的数据访问路径
    • 如果可能，移除视图；或考虑使用日期/时间参数的显式谓词的“参数化视图”（内联表值函数）。
    • 确保自动统计创建不会受到不必要的限制
    • 为任务使用正确类型的临时对象（表与变量）
    • 考虑RECOMPILE计划选择是否对参数值非常敏感
    • 添加UPDATE STATISTICS，RECOMPILE如果缓存的统计数据有问题
    • 考虑一个带有主键的临时表，而不是SELECT INTO它是否为优化器提供有用的信息
    • 检查架构以确保优化器拥有尽可能多的信息（例如外键、其他约束）
    • 根据您对数据的了解，考虑过滤索引/统计信息的适用性
    • 不要NOLOCK为了提高性能而添加提示

    复制品

    以下是根据提供的编辑执行计划中可用的有限信息构建的：

    DROP VIEW IF EXISTS dbo.View_VP;
DROP TABLE IF EXISTS dbo.SmallTable, dbo.LargeTable, #MyTemp;
GO
CREATE TABLE LargeTable (P_Id integer NOT NULL, Status integer NOT NULL, Col1 integer NOT NULL, Col4 datetime NOT NULL);
CREATE TABLE SmallTable (P_id integer NOT NULL, Col2 integer NOT NULL)
CREATE TABLE #MyTemp (Col1 integer NOT NULL);
GO
CREATE VIEW dbo.View_VP 
AS
    SELECT
        pp.Col1,
        pd.Col2 AS Col2,
        MAX(pp.Col4) AS Col3
    FROM LargeTable pp
    JOIN SmallTable pd
        ON pd.P_id = pp.P_Id
    WHERE 
        pp.[Status] IN (3, 4)
    GROUP BY 
        pp.Col1, pd.Col2;
GO
CREATE UNIQUE CLUSTERED INDEX PK_SmallTable ON dbo.SmallTable (P_id)
CREATE CLUSTERED INDEX ix_P_id ON dbo.LargeTable (P_Id)
CREATE INDEX ix_Col1 ON dbo.LargeTable (Col1)
CREATE INDEX ix_Status ON dbo.LargeTable ([Status])
GO
UPDATE STATISTICS dbo.LargeTable WITH ROWCOUNT = 32268200, PAGECOUNT = 322682;
UPDATE STATISTICS dbo.SmallTable WITH ROWCOUNT = 6349, PAGECOUNT = 63;
UPDATE STATISTICS #MyTemp WITH ROWCOUNT = 4;

    查询是：

    DECLARE @Months integer = 6;

SELECT wd.Col1
         , vp.Col2
         , vp.Col3 
    FROM dbo.View_VP vp WITH (FORCESEEK)
    INNER JOIN #MyTemp wd ON wd.Col1 = vp.Col1
    WHERE vp.Col3 > DATEADD(MONTH, @Months * -1, GETDATE())

    如果没有关于基表的真实统计信息，这有利于接近“坏计划”示例的计划（使用ix_Status）：

    演示计划

    这表明关于 的选择性的信息Col1是优化器选择的一个重要因素。

    • 12

相关问题