主页 / dba / 问题 / 226356
Accepted
Alsin
Asked: 2019-01-05 09:34:16 +0800 CST

对分区表查询的错误估计

  • 772

我想知道为什么 SQL Server 在这种简单的情况下会做出错误的估计。有一个场景。

CREATE PARTITION FUNCTION PF_Test (int) AS RANGE RIGHT 
FOR VALUES (20140801, 20140802, 20140803)

CREATE PARTITION SCHEME PS_Test AS PARTITION PF_Test ALL TO ([Primary])

CREATE TABLE A
(
  DateKey int not null,
  Type int not null,
  constraint PK_A primary key (DateKey, Type) on PS_Test(DateKey)
)

INSERT INTO A (DateKey, Type)
SELECT
  DateKey = N1.n  + 20140801,
  Type = N2.n + 1
FROM dbo.Numbers N1
  cross join dbo.Numbers N2
WHERE N1.n BETWEEN 0 AND 2
  and N2.n BETWEEN 0 AND 10000 - 1

UPDATE STATISTICS A (PK_A) WITH FULLSCAN, INCREMENTAL = ON

CREATE TABLE B
(
  DateKey int not null,
  SubType int not null,
  Type int not null,
  constraint PK_B primary key (DateKey, SubType) on PS_Test(DateKey)
)

INSERT INTO B (DateKey, SubType, Type)
SELECT
  DateKey,
  SubType = Type * 10000 + N.n,
  Type
FROM A
  cross join dbo.Numbers N
WHERE N.n BETWEEN 1 AND 10

UPDATE STATISTICS B (PK_B) WITH FULLSCAN, INCREMENTAL = ON

所以设置非常简单，统计信息就位，当我们查询一个表时，SQL Server 可以产生正确的估计。

select COUNT(*) from A where DateKey = 20140802
--10000
select COUNT(*) from B where DateKey = 20140802
--100000

但是在这个简单的选择中，估计值相差甚远，我看不出原因。

SELECT a.DateKey, a.Type
FROM A
  JOIN B
    ON b.DateKey = a.DateKey
    AND b.Type = a.Type
WHERE a.DateKey = 20140802

执行计划

Clustered Index Seek 之后估计是实际值的 57%！真实世界的查询更糟，估计是实际的 2%。

用于重现设置的 PS 编号表

DECLARE @UpperBound INT = 1000000;

;WITH cteN(Number) AS
(
  SELECT ROW_NUMBER() OVER (ORDER BY s1.[object_id]) - 1
  FROM sys.all_columns AS s1
  CROSS JOIN sys.all_columns AS s2
)
SELECT n = [Number] INTO dbo.Numbers
FROM cteN WHERE [Number] <= @UpperBound;

CREATE UNIQUE CLUSTERED INDEX CIX_Number ON dbo.Numbers(n)
WITH 
(
  FILLFACTOR = 100,      -- in the event server default has been changed
  DATA_COMPRESSION = ROW -- if Enterprise & table large enough to matter
);

PPS 相同场景但未分区完美运行。

sql-server partitioning

2 个回答

  1. Best Answer

    估计值（使用新的基数估计器）对于普通连接来说没问题，但当优化器考虑并置连接的选项时就不那么准确了。

    当连接两个以相同方式分区的表时，可以使用并置连接（也称为按分区连接）。这个想法是一次加入一个分区，使用由不断扫描（内存中的值表）提供的分区 ID 驱动的嵌套循环应用。

    定期加盟

    由于并置连接涉及嵌套循环应用，您可以通过指定OPTION (HASH JOIN)例如强制优化器避免这种情况：

    计划强制哈希连接

    该计划中的两个目标是：

    Seek Keys[1]: Prefix:
    PtnId1000, [dbo].[A].DateKey = Scalar Operator((3)), Scalar Operator((20140802))
Seek Keys[1]: Prefix:
    PtnId1003, [dbo].[B].DateKey = Scalar Operator((3)), Scalar Operator((20140802))

    优化器在这两种情况下都应用了静态分区消除，为两次查找和后续连接提供了准确的估计。

    同地连接

    当优化器考虑并置连接时（如问题所示），搜索是：

    并置加入计划

    Seek Keys[1]: Prefix:
    PtnId1000, [dbo].[A].DateKey = Scalar Operator([Expr1006]), Scalar Operator((20140802))
Seek Keys[1]: Prefix:
    PtnId1003, [dbo].[B].DateKey = Scalar Operator([Expr1006]), Scalar Operator((20140802))

    ... [Expr1006]Constant Scan 运算符返回的值在哪里。

    基数估计器现在看不到DateKey值和分区 ID 是相互依赖的，就像使用文字常量时那样。换句话说，对于估计器来说，内部的值[Expr1006]指定与 相同的分区并不明显DateKey = 20140802。

    因此，CE 选择（默认情况下）使用正常的指数退避方法来估计两个（显然独立的）谓词的选择性。

    这解释了为连接提供的减少的基数估计。此选项的较低表观成本（由于错误估计）意味着优化器选择并置连接而不是常规连接，即使它显然（对人类）没有提供任何价值。

    有几种方法可以解决逻辑中的这个差距，包括使用查询提示USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES')，但这会影响整个查询，而不仅仅是有问题的并置连接替代方案。正如 Erik 在他的回答中指出的那样，您还可以暗示使用旧版 CE。

    有关并置连接的更多信息，请参阅我的文章Improving Partitioned Table Join Performance

    • 9

  2. 这似乎是由于 SQL Server 2014 中引入的新基数估计器。

    如果你指示查询使用旧的，你会得到一个不同的计划和正确的估计。

    SELECT a.DateKey, a.Type
FROM A AS a
  JOIN B AS b
    ON b.DateKey = a.DateKey
    AND b.Type = a.Type
WHERE a.DateKey = 20140802
OPTION(USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

    坚果

    有关详细信息，请参阅以下链接：

    • 5

相关问题