主页 / dba / 问题 / 312754
Accepted
SEarle1986
Asked: 2022-06-01 02:16:34 +0800 CST

当表中有 >100k 不同值时，为什么 SQL Server 在统计直方图中没有 200 个桶

  • 772

鉴于我正在使用AdventureWorks2016 OLTP 数据库，为什么当该列中有 113k 个不同的值时PK_TransactionHistory_TransactionID，表上索引的统计直方图Production.TransactionHistory仅包含 3 个直方图“桶”？

下面的一个例子：

USE AdventureWorks2016

/* ensure statistics are as accurate as they can be */
UPDATE STATISTICS Production.TransactionHistory WITH FULLSCAN

然后我们可以查看更新后的直方图

/* look at the statistics for the primary key column */
DBCC SHOW_STATISTICS (
    'Production.TransactionHistory', 
    'PK_TransactionHistory_TransactionID')
WITH HISTOGRAM;

我看到了输出：

在此处输入图像描述

注意最大和最小事务 ID：

SELECT MIN(TransactionID) FROM Production.TransactionHistory /* 100000 */
SELECT MAX(TransactionID) FROM Production.TransactionHistory /* 213442 */

SQL Server 似乎为最大值创建了一个“桶”，一个用于最小值，一个用于介于两者之间的所有值（它知道它们都是不同的）

我注意到如果我从这个表中删除主键

ALTER TABLE Production.TransactionHistory DROP CONSTRAINT PK_TransactionHistory_TransactionID

然后插入一些重复的值

INSERT INTO [Production].[TransactionHistory]
(
    TransactionID,
    [ProductID],
    [ReferenceOrderID],
    [ReferenceOrderLineID],
    [TransactionDate],
    [TransactionType],
    [Quantity],
    [ActualCost],
    [ModifiedDate]
)
VALUES
(200001,1,1,1,GETDATE(),'P',1,1,GETDATE()),
(200011,1,1,1,GETDATE(),'P',1,1,GETDATE()),
(200021,1,1,1,GETDATE(),'P',1,1,GETDATE()),
(200031,1,1,1,GETDATE(),'P',1,1,GETDATE())

更新表上的统计信息，然后查看列的统计信息（而不是我们删除的PK）

USE AdventureWorks2016

/* ensure statistics are as accurate as they can be */
UPDATE STATISTICS Production.TransactionHistory WITH FULLSCAN

/* look at the statistics for the primary key column */
DBCC SHOW_STATISTICS (
    'Production.TransactionHistory', 
    'TransactionID')
WITH HISTOGRAM;

我们仍然有两个存储桶，尽管 DISTINCT_RANGE_ROWS 已相应更新

在此处输入图像描述

为什么 SQL Server 不使用此处直方图中的 200 个“桶”？这是否与填充 8KB 统计页面所需的资源有关，并且使用所有 200 个存储桶意味着它可能需要重新定义何时将新数据添加到表中？

sql-server-2016 statistics

1 个回答

  1. Best Answer

    在这种情况下，直方图与插入 4 个重复值之前几乎无法区分。那时，唯一的、连续的系列完全可以用三个步骤来描述。

    不同之处在于范围行 = 113441 而不是 113445，不同的范围行仍然 = 113441，平均范围行 = 1 而不是 1.000035。

    所以。在最多 200 个加 NULL 插槽直方图中捕获四个重复项不是更好吗？

    不，不一定。

    为什么？因为优化器的统计数据不只是暂时的。优化器统计信息将持续到下次更新优化器统计信息。由于 SQL Server 2016 及以后的默认自动统计阈值超过 25,000 行，因此 SQRT(1000 * 行)。在这种情况下，阈值是 COLMODCTR > 10651.06。因此，在至少对 TransactionId 进行 10652 次修改之前不会自动更新，我们已经看到有重复。给定下一个自动更新统计阈值 106652 次修改的情况下，一个其他唯一的连续序列中仍然存在 4 个重复项的一般值可以表示 - 这可能是在序列中创建漏洞的删除、几个或多个值的重复项，或者从前一个 max + 1 开始的唯一连续数字的范围？

    优化器统计数据，就像优化器所做的所有工作一样，并不是为了在所有情况下都达到最佳情况，无论付出多少努力或时间。相反，考虑到基数估计和其他优化器工作中的建模限制，在付出努力和时间的情况下提供“足够好”的结果。

    这是具有约束、索引和统计信息的查询通知模式整形总是很重要的原因之一。还有一个原因是基于模式的查询整形，包括 T-SQL 代码格式和提示，总是很重要的:-)

    • 3

相关问题