集群列店的划分索引 - 基数估计错误

Question

这个让我困惑。我有一个尺寸表，其中约有3000万行。这是一个聚集的柱状店。另外，该表具有INT类型的主要密钥约束，其替代键。

在给定的日期范围内检索替代键的最小((的查询，看起来像这样：

SELECT
    MIN(DIM.OrderId)
FROM
    dbo.Dim_Order AS DIM
WHERE
    DIM.OrderDate >= CAST('2016-06-01' AS DATE)
    AND DIM.OrderDate < CAST('2016-07-01' AS DATE)
OPTION (MAXDOP 1);

这是输出：

表'dim_order'。扫描计数2，逻辑读取833，物理读数0， 读取读数为0，lob逻辑读取1702561，lob物理读取0， lob read-head读取0。

表'dim_order'。段读取304001，跳过0。

(受1行影响(

SQL Server执行时间：CPU时间= 2829毫秒，经过时间= 2876 ms。

优化器不是使用列存储，而是选择使用非簇的主键并通过嵌套环执行键查找。更糟糕的是，它严重低估了返回的行数。

奇怪的是，行估计似乎与日期范围的大小成反比。

╔════════════╦══════════════════════════╗
║ Date Range ║ Estimated Number of Rows ║
╠════════════╬══════════════════════════╣
║ 1 year     ║ 2.00311                  ║
║ 6 months   ║ 3.41584                  ║
║ 1 month    ║ 24.4459                  ║
║ 2 weeks    ║ 52.093                   ║
║ 1 week     ║ 99.9055                  ║
║ 3 days     ║ 217.632                  ║
║ 1 day      ║ 1088.16                  ║
╚════════════╩══════════════════════════╝

此版本带有索引提示，几乎立即运行：

SELECT
    MIN(DIM.OrderId)
FROM
    dbo.Dim_Order AS DIM WITH(INDEX=CCI_Dim_Order)
WHERE
    DIM.OrderDate >= CAST('2016-06-01' AS DATE)
    AND DIM.OrderDate < CAST('2016-07-01' AS DATE)
OPTION (MAXDOP 1);

表'dim_order'。扫描计数1，逻辑读取0，物理读数0， 读取读数为0，lob逻辑读取1004，lob物理读数0，lob 读取读数为0。

表'dim_order'。段读取2，跳过0。

(受1行影响(

SQL Server执行时间：CPU时间= 0 ms，经过时间= 1 ms。

我在以下版本上观察到了这种行为：

Microsoft SQL Server 2016(RTM(-13.0.1601.5(x64(

Microsoft SQL Server 2016(SP1 -CU5((KB4040714(-13.0.4451.0(x64(

下面的repro脚本将创建一个示例表，并用2年的2,000个客户每天订购2年的订单填充它。这在我们的桌子上算出1,462,000个样品订单，涵盖24个月，每个月约有60,000行。脚本底部的示例查询旨在证明行为。如您所见，由于某种原因，行估计值非常低，除非提示，否则优化器拒绝使用群集的柱面。

我感谢对此的任何意见或建议。这是示例脚本。

DROP TABLE IF EXISTS dbo.Dim_Order
CREATE TABLE dbo.Dim_Order
    (
    OrderId INT NOT NULL
    , CustomerId INT NOT NULL
    , OrderDate DATE NOT NULL
    , OrderTotal decimal(5,2) NOT NULL
    );
WITH CTE_DATE AS
(
SELECT CAST('2016-01-01' AS DATE) AS DateValue
UNION ALL
SELECT
       DATEADD(DAY, 1, D.DateValue)
FROM
       CTE_DATE AS D
WHERE
       D.DateValue < CAST('2017-12-31' AS DATE)
),
CTE_CUSTOMER AS
(
SELECT 1 AS CustomerId
UNION ALL
SELECT
       CustomerId + 1
FROM
       CTE_CUSTOMER AS D
WHERE
       D.CustomerId < 2000
)
, CTE_FINAL
AS
(
SELECT
    ROW_NUMBER() OVER (ORDER BY DateValue ASC, CustomerId ASC) AS OrderId
    , CustomerId
    , DateValue AS OrderDate
    , CAST(ROUND(RAND(CHECKSUM(NEWID()))*(100-1)+1, 2) AS DECIMAL(5,2)) AS OrderTotal
FROM
    CTE_DATE
    CROSS JOIN CTE_CUSTOMER
)
INSERT INTO
    dbo.Dim_Order
    (
    OrderId
    , CustomerId
    , OrderDate
    , OrderTotal
    )
SELECT
    ORD.OrderId
    , ORD.CustomerId
    , ORD.OrderDate
    , ORD.OrderTotal
FROM
    CTE_FINAL AS ORD
OPTION (MAXRECURSION 32767);
CREATE CLUSTERED COLUMNSTORE INDEX CCI_Dim_Order ON dbo.Dim_Order;
ALTER INDEX CCI_Dim_Order ON dbo.Dim_Order
    REORGANIZE
    WITH (COMPRESS_ALL_ROW_GROUPS = ON)
ALTER TABLE dbo.Dim_Order
    ADD CONSTRAINT PK_Dim_Order PRIMARY KEY NONCLUSTERED (OrderId ASC);
RETURN;
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT
    MIN(DIM.OrderId)
FROM
    dbo.Dim_Order AS DIM
WHERE
    DIM.OrderDate = CAST('2016-06-01' AS DATE)
    AND DIM.OrderDate < CAST('2016-07-01' AS DATE)
OPTION (MAXDOP 1);
SELECT
    MIN(DIM.OrderId)
FROM
    dbo.Dim_Order AS DIM WITH(INDEX=CCI_Dim_Order)
WHERE
    DIM.OrderDate >= CAST('2016-06-01' AS DATE)
    AND DIM.OrderDate < CAST('2016-07-01' AS DATE)
OPTION (MAXDOP 1);

Answer 1

这是一个典型的行目标基数估计问题。您可以添加USE HINT ('DISABLE_OPTIMIZER_ROWGOAL')来禁用行目标，并应该发现聚类的列存储现在更便宜并选择。

该计划在PK_Dim_Order上有有序扫描 - 因为它按照OrderId的顺序处理行，并且正在寻找MIN(DIM.OrderId)，它可以在发现第一个与OrderDate上的谓词匹配的第一个行 - 假设60,000行与匹配的行相匹配。月谓词将在整个指数中均匀散布。实际上，它们都与IDS 304001到364000处于连续的范围。

这种非相关性的假设也是为什么随着日期范围较大的估计行数量下降的原因。如果您将日期谓词的匹配行数增加一倍，并且它们确实通过索引均匀地散布了，那么您只需要读取一半的一半行，然后再击中一个匹配谓词并停止扫描。