Daniel Hutmacher's questions

我有一个报表（大约 10 亿行）和一个很小的维度表：

CREATE TABLE dbo.Sales_unpartitioned (
    BusinessUnit    int NOT NULL,
    [Date]          date NOT NULL,
    SKU             varchar(8) NOT NULL,
    Quantity        numeric(10, 2) NOT NULL,
    Amount          numeric(10, 2) NOT NULL,
    CONSTRAINT PK_Sales_unpartitioned PRIMARY KEY CLUSTERED (BusinessUnit, [Date], SKU)
);

--- Demo data:
INSERT INTO dbo.Sales_unpartitioned
SELECT severity AS BusinessUnit,
       DATEADD(day, message_id, '2000-01-01') AS [Date],
       LEFT([text], 3) AS SKU,
       1000.*RAND(CHECKSUM(NEWID())) AS Quantity,
       10000.*RAND(CHECKSUM(NEWID())) AS Amount
FROM sys.messages
WHERE [language_id]=1033;

--- Artificially inflate statistics of demo data:
UPDATE STATISTICS dbo.Sales_unpartitioned WITH ROWCOUNT=1000000000;

--- Dimension table:
CREATE TABLE dbo.BusinessUnits (
    BusinessUnit    int NOT NULL,
    SalesManager    nvarchar(250) NULL,
    PRIMARY KEY CLUSTERED (BusinessUnit)
);

INSERT INTO dbo.BusinessUnits (BusinessUnit)
SELECT DISTINCT BusinessUnit FROM dbo.Sales;

...我在其中添加了应用程序用于 OLTP 样式报告的报告视图。

CREATE OR ALTER VIEW dbo.SalesReport_unpartitioned
AS

SELECT bu.BusinessUnit,
       s.[Date],
       s.SKU,
       s.Quantity,
       s.Amount
FROM dbo.BusinessUnits AS bu
CROSS APPLY (
    --- Regular sales
    SELECT t.BusinessUnit, t.[Date], t.SKU, t.Quantity, t.Amount
    FROM dbo.Sales_unpartitioned AS t
    WHERE t.BusinessUnit=bu.BusinessUnit
      AND t.SKU LIKE 'T%'

    UNION ALL

    --- This is a special reporting entry. We only
    --- want to see today's row. In case of duplicates,
    --- get the row with the first "SKU".
    SELECT TOP (1) s.BusinessUnit, s.[Date], s.SKU, s.Quantity, s.Amount
    FROM dbo.Sales_unpartitioned AS s
    WHERE s.BusinessUnit=bu.BusinessUnit
      AND s.[Date]=CAST(SYSDATETIME() AS date)
      AND s.SKU LIKE 'S%'
    ORDER BY s.BusinessUnit, s.[Date], s.SKU
) AS s

这个想法是，用户应用程序将使用 SELECT 查询来查询此视图，该查询过滤一系列日期和一个或多个业务单位。为此，我选择了一种CROSS APPLY模式，以便查询可以“循环”每个业务单元，查找日期范围，并在 SKU 上应用残差过滤器。

示例应用查询：

DECLARE @from date='2021-01-01', @to date='2021-12-31';

SELECT *
FROM dbo.SalesReport_unpartitioned
WHERE BusinessUnit=16
  AND [Date] BETWEEN @from AND @to
ORDER BY BusinessUnit, [Date], SKU;

我希望查询计划看起来像这样： Desired plan

但是，计划结果如下： 实际计划

我希望 SQL Server 在 Date 列上执行“谓词下推”，允许 Clustered Index Seek 查找单个 BusinessUnit 和日期范围，然后在 SKU 上应用剩余谓词。这适用于“s”分支中的 Seek（带有 的分支TOP）——可能是因为它在查询中有一个硬编码的 Date 谓词——但不适用于“t”分支。

但是，在“t”分支上，SQL Server 只寻找具有 SKU 上的剩余谓词的特定 BusinessUnit，有效地检索所有日期。只有在计划结束时，它才会应用过滤日期列的过滤运算符。

在一个大表中，这会带来非常显着的性能损失——当您只需要一周的时间时，您最终可能会从磁盘读取 20 年的数据。

我尝试过的事情

解决方法：

• 将视图转换为具有过滤“s”和“t”查询的@fromDate 和@toDate 参数的内联表值函数将根据需要启用Seek on (BusinessUnit, Date)，但需要重写应用程序代码。
• 将(from to )UNION ALL移出将启用谓词下推。它在 BusinessUnit 表上又进行了一次搜索，这是完全可以接受的。CROSS APPLYCROSS APPLY (UNION)CROSS APPLY() UNION CROSS APPLY()

修复了 Seek，但改变了结果：

• 令人惊讶的是，删除“s”查询的TOP (1)andORDER BY会使谓词下推对“t”起作用，但会从“s”返回太多行。
• UNION ALL通过删除“s”或“t”查询来消除将启用谓词下推，但会产生不正确的结果。

无变化或不可行：

• 替换TOP (1)为ROW_NUMBER()模式不会改变 Seek。
• 将 the 更改CROSS APPLY为强制INNER LOOP JOIN修复了“t”上的 Seek，但实际上将“s”更改为 Scan，这甚至更糟。
• 添加跟踪标志 8780 以允许优化器在计划上工作更长时间不会改变任何事情。该计划已经完全优化，没有提前终止。

一个常见的线程似乎是更改/简化“s”查询（删除TOP, ORDER BY）解决了“t”查询的问题，这对我来说感觉违反直觉。

我在寻找什么

我试图了解这是否是优化器的缺点，是否是故意成本/优化机制的结果，或者我是否只是忽略了某些东西。

我有两个具有相同名称、类型和索引键列的表。其中一个具有唯一的聚集索引，另一个具有非唯一的.

测试设置

设置脚本，包括一些真实的统计数据：

DROP TABLE IF EXISTS #left;
DROP TABLE IF EXISTS #right;

CREATE TABLE #left (
    a       char(4) NOT NULL,
    b       char(2) NOT NULL,
    c       varchar(13) NOT NULL,
    d       bit NOT NULL,
    e       char(4) NOT NULL,
    f       char(25) NULL,
    g       char(25) NOT NULL,
    h       char(25) NULL
    --- and a few other columns
);

CREATE UNIQUE CLUSTERED INDEX IX ON #left (a, b, c, d, e, f, g, h)

UPDATE STATISTICS #left WITH ROWCOUNT=63800000, PAGECOUNT=186000;

CREATE TABLE #right (
    a       char(4) NOT NULL,
    b       char(2) NOT NULL,
    c       varchar(13) NOT NULL,
    d       bit NOT NULL,
    e       char(4) NOT NULL,
    f       char(25) NULL,
    g       char(25) NOT NULL,
    h       char(25) NULL
    --- and a few other columns
);

CREATE CLUSTERED INDEX IX ON #right (a, b, c, d, e, f, g, h)

UPDATE STATISTICS #right WITH ROWCOUNT=55700000, PAGECOUNT=128000;

复制品

当我在它们的集群键上加入这两个表时，我期望一个一对多的 MERGE 连接，如下所示：

SELECT *
FROM #left AS l
LEFT JOIN #right AS r ON
    l.a=r.a AND
    l.b=r.b AND
    l.c=r.c AND
    l.d=r.d AND
    l.e=r.e AND
    l.f=r.f AND
    l.g=r.g AND
    l.h=r.h
WHERE l.a='2018';

这是我想要的查询计划：

（不要介意警告，它们与虚假统计有关。）

但是，如果我更改连接中列的顺序，如下所示：

SELECT *
FROM #left AS l
LEFT JOIN #right AS r ON
    l.c=r.c AND     -- used to be third
    l.a=r.a AND     -- used to be first
    l.b=r.b AND     -- used to be second
    l.d=r.d AND
    l.e=r.e AND
    l.f=r.f AND
    l.g=r.g AND
    l.h=r.h
WHERE l.a='2018';

... 有时候是这样的：

Sort 运算符似乎根据声明的连接顺序对流进行排序，即c, a, b, d, e, f, g, h，它向我的查询计划添加了阻塞操作。

我看过的东西

• 我尝试将列更改为NOT NULL，结果相同。
• 原始表是用 创建的ANSI_PADDING OFF，但创建它ANSI_PADDING ON不会影响这个计划。
• 我尝试了一个INNER JOIN而不是LEFT JOIN，没有变化。
• 我在 2014 SP2 Enterprise 上发现了它，在 2017 Developer（当前 CU）上创建了一个复制品。
• 删除前导索引列上的 WHERE 子句确实会产生好的计划，但它会影响结果.. :)

最后，我们进入问题

• 这是故意的吗？
• 我可以在不更改查询的情况下消除排序吗（这是供应商代码，所以我真的不想......）。我可以更改表和索引。

出于查询调优和测试目的，您可以通过运行手动将行数和页数分配给表的索引统计信息UPDATE STATISTICS。但是你如何重新计算/重置统计信息到表的实​​际内容？

--- Create a table..
CREATE TABLE dbo.StatTest (
    i      int NOT NULL,
    CONSTRAINT PK_StatTest PRIMARY KEY CLUSTERED (i)
);
GO

--- .. and give it a thousand-or-so rows:
DECLARE @i int=1;
INSERT INTO dbo.StatTest (i) VALUES (@i);

WHILE (@i<1000) BEGIN;
    INSERT INTO dbo.StatTest (i) SELECT @i+i FROM dbo.StatTest;
    SET @i=@i*2;
END;

一个虚拟查询：

SELECT i%100, COUNT(*) FROM dbo.StatTest GROUP BY i%100;

... 将返回以下查询计划（索引扫描中的行估计为 1024 行）。

运行UPDATE STATISTICS命令..

UPDATE STATISTICS dbo.StatTest WITH ROWCOUNT=10000000;

...计划看起来像这样，现在估计有 1000 万行：

如何在不使用的情况下将行数重置为表的实际内容WITH ROWCOUNT？

我试过WITH FULLSCAN,WITH RESAMPLE和WITH SAMPLE n ROWS，但统计行数仍然是 1000 万行。插入一行甚至删除所有行都不会更新统计信息，因为更改太小了。

我正在写一篇关于排名和聚合窗口函数的博客文章，特别是 Segment 和 Sequence Project 迭代器。我理解它的方式是 Segment 标识流中构成组的结束/开始的行，因此以下查询：

SELECT ROW_NUMBER() OVER (PARTITION BY someGroup ORDER BY someOrder)

将使用 Segment 来判断一行何时属于不同于前一行的不同组。然后，Sequence Project 迭代器根据 Segment 迭代器的输出进行实际的行号计算。

但是使用该逻辑的以下查询不必包含 Segment，因为没有分区表达式。

SELECT ROW_NUMBER() OVER (ORDER BY someGroup, someOrder)

但是，当我尝试这个假设时，这两个查询都使用了 Segment 运算符。唯一不同的是，第二个查询不需要GroupBy对 Segment 进行 a。这不是首先消除了对 Segment 的需求吗？

例子

CREATE TABLE dbo.someTable (
    someGroup   int NOT NULL,
    someOrder   int NOT NULL,
    someValue   numeric(8, 2) NOT NULL,
    PRIMARY KEY CLUSTERED (someGroup, someOrder)
);

--- Query 1:
SELECT ROW_NUMBER() OVER (PARTITION BY someGroup ORDER BY someOrder)
FROM dbo.someTable;

--- Query 2:
SELECT ROW_NUMBER() OVER (ORDER BY someGroup, someOrder)
FROM dbo.someTable;