主页 / dba / 问题 / 246992
Accepted
Erik Darling
Asked: 2019-09-05 10:40:19 +0800 CST

哪些成本因素会影响优化器选择不同类型的线轴？

  • 772

线轴

在 SQL Server 中有几种假脱机。我感兴趣的两个是Table Spool s 和Index spools，在修改查询之外。

只读查询，特别是在嵌套循环连接的内侧，可以使用表或索引假脱机来潜在地减少 I/O 并提高查询性能。这些线轴可以是Eager或Lazy。就像你和我一样。

我的问题是：

  • 哪些因素会影响表与索引假脱机的选择
  • 在 Eager Spools 和 Lazy Spools 之间做出选择的因素有哪些
sql-server execution-plan

1 个回答

  1. Best Answer

    这有点宽泛，但我想我理解真正的问题并会相应地回答。不过，我只想谈谈表与索引假脱机。我认为将其视为表和索引假脱机之间的选择是不正确的。如您所知，可以在单个子树中获得索引假脱机、表假脱机或同时获得索引假脱机和表假脱机。我认为在以下条件下您获得索引假脱机通常是正确的：

    1. 查询优化器有理由将连接转换为应用
    2. 查询优化器实际执行对应用程序的转换
    3. 查询优化器使用规则添加索引假脱机（至少索引假脱机必须可以安全使用）
    4. 选择带有索引假脱机的计划

    您可以通过简单的演示看到其中的大部分内容。首先创建一对堆：

    DROP TABLE IF EXISTS dbo.X_10000_VARCHAR_901;
CREATE TABLE dbo.X_10000_VARCHAR_901 (ID VARCHAR(901) NOT NULL);

INSERT INTO dbo.X_10000_VARCHAR_901 WITH (TABLOCK)
SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;


DROP TABLE IF EXISTS dbo.X_10000_VARCHAR_800;
CREATE TABLE dbo.X_10000_VARCHAR_800 (ID VARCHAR(800) NOT NULL);

INSERT INTO dbo.X_10000_VARCHAR_800 WITH (TABLOCK)
SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

    对于第一个查询，没有什么可寻找的：

    SELECT *
FROM dbo.X_10000_VARCHAR_901 a
CROSS JOIN dbo.X_10000_VARCHAR_901 b
OPTION (MAXDOP 1);

    因此，优化器没有理由将连接转换为应用。由于成本原因，您最终会得到一个表线轴。所以这个查询没有通过第一次测试。

    在此处输入图像描述

    对于下一个查询，可以公平地期望优化器有理由考虑应用：

    SELECT *
FROM dbo.X_10000_VARCHAR_901 a
INNER JOIN dbo.X_10000_VARCHAR_901 b ON a.ID = b.ID 
OPTION (LOOP JOIN, MAXDOP 1);

    但这并不意味着：

    在此处输入图像描述

    此查询未通过第二次测试。完整的解释在这里。引用最相关的部分：

    优化器不会考虑动态构建索引来启用应用程序；相反，事件的顺序通常是相反的：转换为应用，因为存在一个好的索引。

    我可以重写查询以鼓励优化器考虑应用：

    SELECT *
FROM dbo.X_10000_VARCHAR_901 a
INNER JOIN dbo.X_10000_VARCHAR_901 b ON a.ID >= b.ID AND a.ID <= b.ID
OPTION (MAXDOP 1);

    但是仍然没有索引假脱机：

    在此处输入图像描述

    此查询未通过第三次测试。在 SQL Server 2014 中，索引键长度限制为 900 字节。这在 SQL Server 2016 中得到了扩展，但仅适用于非聚集索引。假脱机的索引是聚集索引，因此限制保持在 900 字节。在任何情况下，都不能应用索引假脱机规则，因为它可能会在查询执行期间导致错误。

    将数据类型长度减少到 800 最终提供了一个带有索引假脱机的计划：

    在此处输入图像描述

    毫不奇怪，索引假脱机计划的成本比没有假脱机的计划便宜得多：89.7603 个单位对 598.832 个单位。您可以看到与未记录的QUERYRULEOFF BuildSpool查询提示的区别：

    在此处输入图像描述

    这不是一个完整的答案，但希望它是您正在寻找的一些东西。

    • 12

相关问题