主页 / dba / 问题 / 187158
Accepted
George K
Asked: 2017-09-29 05:15:24 +0800 CST

SQL Server 实际计划。不正确的行估计

  • 772

在练习调整 StackOverflow 数据库的查询时，我遇到了以下问题：

SELECT TOP 50 Id AS [User Link],
(
    SELECT COUNT(*)
    FROM Posts
    WHERE PostTypeId = 1 AND 
          LastEditorUserId = Users.Id AND 
          OwnerUserId != Users.Id
) AS QuestionEdits,
(
    SELECT COUNT(*)
    FROM Posts
    WHERE PostTypeId = 2 AND 
          LastEditorUserId = Users.Id AND 
          OwnerUserId != Users.Id
) AS AnswerEdits,
(
    SELECT COUNT(*)
    FROM Posts
    WHERE LastEditorUserId = Users.Id AND 
          OwnerUserId != Users.Id
) AS TotalEdits
FROM Users
ORDER BY TotalEdits DESC;

计划就在这里。

执行统计：

(50 row(s) affected)
Table 'Posts'. Scan count 43217677, logical reads 172988050, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Users'. Scan count 5, logical reads 12692, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 701344 ms,  elapsed time = 192167 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

我在这里有三个问题：

  1. 运算符“持续扫描”指的是什么？
  2. 为什么行估计在以下块中可能不正确：

一个。

在此处输入图像描述

b. 在此处输入图像描述

  1. 怎样做才能使估计更准确？要考虑提高查询性能的索引/技术是什么？

任何提示将不胜感激。

sql-server performance

3 个回答

  1. Best Answer

    这个问题很难回答，因为查询与提供的计划不匹配，而且用于生成计划的查询有点无意义：

    SELECT TOP 50 Id AS [User Link],
(
 SELECT top 1 1
 FROM Posts
 WHERE PostTypeId = 1 AND 
    LastEditorUserId = Users.Id AND 
    OwnerUserId != Users.Id
) AS QuestionEdits,
(
 SELECT top 1 1
 FROM Posts
 WHERE PostTypeId = 2 AND 
    LastEditorUserId = Users.Id AND 
    OwnerUserId != Users.Id
) AS AnswerEdits,
(
 SELECT top 1 1
 FROM Posts
 WHERE LastEditorUserId = Users.Id AND 
    OwnerUserId != Users.Id
) AS TotalEdits
FROM Users
ORDER BY TotalEdits DESC

    尽管如此：

    1. 运算符“持续扫描”指的是什么？

    常量扫描从常量值的内存表中读取。它可能有一行或多行，以及零个或多个列。是的，Constant Scan 可能有零列；它通常由优化器简单地作为（空）行的来源引入，后面的运算符（通常是计算标量）可以向其中添加列。

    在很大程度上，Constant Scans 通常是正确运行查询所必需的纯架构工件，或者作为优化的一部分。

    当 Constant Scans 和 Compute Scalars 位于 Merge Interval 之下时，这是一个明确的迹象，表明这些元素是在优化后引入的，用于计算多个逻辑间隔并将其折叠为适合在该范围内驱动 Index Seek 的单个间隔。

    在这种特殊情况下，Constant Scans 表示扩展OwnerUserId <> Users.Id为：

    • OwnerUserId < Users.Id;OR
    • OwnerUserId > Users.Id

    合并间隔子树的优化后重写由此x < y OR x > y谓词触发。机器测试重叠的范围，并将生成的“合并间隔”折叠为可与索引查找一起使用的起点和终点。

    以下最小示例显示了 SQL Server 以这种方式重写谓词，以便可以生成索引查找：

    CREATE TABLE #U (u integer NOT NULL);
CREATE TABLE #P (u integer NOT NULL PRIMARY KEY);

INSERT #U VALUES (1);
INSERT #P VALUES (0), (2);

SELECT 
    U.u,
    (
        SELECT TOP (1) 1
        FROM #P AS P
            WITH (FORCESEEK)
        WHERE P.u <> U.u
    )
FROM #U AS U;

DROP TABLE #U, #P

    演示计划

    由此产生的 Clustered Index Seek 具有以下属性：

    求属性

    最终结果是向前扫描索引直到终点（不包括目标值），然后在目标值之后重新开始扫描到索引的末尾。[Expr1011]并且[Expr1012]是计算出的排除范围的起点和终点。

    您可以在我的文章Dynamic Seeks and Hidden Implicit Conversions中阅读更多相关信息。

    1. 为什么行估计在以下块中可能不正确

    SSMS 显示每次迭代的嵌套循环连接内侧的估计值，而实际行数是所有迭代的总数。这让很多人感到困惑，并且是一个有问题的设计决策的结果。

    由 Microsoft 于 2009 年 10 月 14 日下午 2:47 发布
    您所指的估计值和实际值之间的差异并不是优化器中的真正错误；我们在展示计划中呈现信息的方式是一个有问题的设计决定。该表位于循环连接的内侧，在那个位置，我们报告的实际行数和预期行数并不意味着同一件事：实际行数包括所有执行，而估计行数是每次执行。

    您可以通过在Sentry One Plan Explorer中查看计划来获得更自然的比较。它为您进行每次迭代计算，因此您可以更直接地比较估计值和实际值。例如，这是在 Plan Explorer 中查看的 Merge Interval 子树之一的视图：

    合并区间子树

    1. 怎样做才能使估计更准确？要考虑提高查询性能的索引/技术是什么？

    showplan display 的差异解释了第一个问题。大卫的回答解决了重写查询以获得更好的性能（和有意义的结果）的问题。

    • 4

  2. 对于持续扫描，这里有一篇文章可能有助于解释执行计划中的持续扫描。

    估计的行数只是一个估计值。查询优化器尝试使用过去的执行来猜测任何给定操作将返回多少行。如果您正在使用诸如恢复之类的东西来练习查询调优，那么很可能在您执行恢复操作之后您没有执行任何数据库维护。统计信息有助于确定诸如估计行之类的事情，而索引维护将有助于智能地查询数据并为这些统计信息提供更好的信息。

    如果更新统计信息不起作用，可能是因为对数据库执行的查询不多。运行该查询几次将为其提供一些更好的信息，但如果执行时间过长，则需要考虑其他选项来帮助优化器。

    为了使该查询执行得更好，您只需查看已返回的 I/O 统计信息中的工作表即可。IO Statistics 是了解您真正对数据进行了多少处理的绝佳选择。此处显示的工作表意味着优化器正在保存数据集，然后对其进行多次审查。通过将此处显示的三个子查询压缩到一个语句中，可以大大减少这种情况，如下所示。

    SELECT
    SUM(CASE
        WHEN PostType = 1
        THEN 1 ELSE 0 END) AS QuestionEdits,
    SUM(CASE
        WHEN PostTypeId = 2
        THEN 1 ELSE 0 END) AS AnswerEdits,
    COUNT(*) AS TotalEdits
FROM Posts P
INNER JOIN Users U ON
    U.Id = P.LastEditorUserId
    AND U.Id <> P.OwnerUserId
ORDER BY 3 DESC

    请注意，我没有测试上面的内容，因为我没有架构或数据库，所以请对它持保留态度（它甚至可能无法编译）。

    • 3

  3. 根据 David 的说法，新查询（稍作修改）实际上获得了更好的性能 - 基数和其他一切似乎都很好。大卫，非常感谢。这是优化的查询：

    SELECT TOP 50 U.Id AS [User Link],
              SUM(CASE
                      WHEN PostTypeId = 1
                      THEN 1
                      ELSE 0
                  END) AS QuestionEdits,
              SUM(CASE
                      WHEN PostTypeId = 2
                      THEN 1
                      ELSE 0
                  END) AS AnswerEdits,
              COUNT(*) AS TotalEdits
FROM Users AS U
     INNER JOIN Posts AS P ON U.Id = P.LastEditorUserId
                              AND U.Id <> P.OwnerUserId
GROUP BY U.Id
ORDER BY TotalEdits DESC;

    执行统计：

    SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 10 ms, elapsed time = 10 ms.

(50 row(s) affected)
Table 'Posts'. Scan count 1, logical reads 98579, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Users'. Scan count 1, logical reads 112295, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 8750 ms,  elapsed time = 8888 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
    • 0

相关问题