样本大小的异常行为可用于统计信息更新

我一直在研究抽样阈值，并使用SQL Server（2012）的统计信息更新，并注意到一些奇怪的行为。基本上，在某些情况下，即使使用相同的数据集，采样的行数似乎也有所不同。

我运行以下查询：

--Drop table if exists
IF (OBJECT_ID('dbo.Test')) IS NOT NULL DROP TABLE dbo.Test;

--Create Table for Testing
CREATE TABLE dbo.Test(Id INT IDENTITY(1,1) CONSTRAINT PK_Test PRIMARY KEY CLUSTERED, TextValue VARCHAR(20) NULL);

--Insert enough data so we have more than 8Mb (the threshold at which sampling kicks in)
INSERT INTO dbo.Test(TextValue) 
SELECT TOP 1000000 'blahblahblah'
FROM sys.objects a, sys.objects b, sys.objects c, sys.objects d;  

--Create Index on TextValue
CREATE INDEX IX_Test_TextValue ON dbo.Test(TextValue);

--Update Statistics without specifying how many rows to sample
UPDATE STATISTICS dbo.Test IX_Test_TextValue;

--View the Statistics
DBCC SHOW_STATISTICS('dbo.Test', IX_Test_TextValue) WITH STAT_HEADER;

当我查看SHOW_STATISTICS的输出时，我发现“采样行”有所不同每次完整执行（即删除，重新创建和填充表）。

例如：

采样行

我的期望是，每次表格相同时，此数字将相同。顺便说一句，如果我只是删除数据并重新插入，就不会出现这种情况。

这不是一个关键问题，但我会对了解正在发生的事情感兴趣。

您要插入的文件组中有多少个文件？我在2016年尝试过几次，两次都将表格分为3584页，每行279行，第一页分为64行。我看到的两个不同的样本大小分别为314712和315270-都是279的精确倍数。

@JoeObbish-它总是读取整页的AFAIK，所以我对此并不感到惊讶。由于某种原因，我认为问题中的数字与该模式不匹配。但是重做了数学。 318618 = 1142 * 279，319240 = 1144 * 279 + 64，324198 = 1162 * 279和314154 = 1126，因此方差是采样的页数。

@MartinSmith只需一个文件-279数字很有趣，我总是喜欢理解所涉及的模式

#1 楼

背景

使用以下形式的语句收集统计对象的数据：（SP:StmtCompleted）。

统计信息生成查询通常访问基表（而不是非聚集索引），以避免自然出现在非聚集索引页面上的值的聚集。采样的行数取决于选择采样的整个页面数。该表的每个页面都被选中或未被选中。所选页面上的所有行都有助于统计。

随机数

SQL Server使用随机数生成器来确定页面是否合格。在这种情况下使用的生成器是Lehmer随机数生成器，其参数值如下所示： >

（Xseed）基本表的bigint的低整数部分，例如

SELECT 
    StatMan([SC0], [SC1], [SB0000]) 
FROM 
(
    SELECT TOP 100 PERCENT 
        [SC0], [SC1], STEP_DIRECTION([SC0]) OVER (ORDER BY NULL) AS [SB0000]
    FROM 
    (
        SELECT 
            [TextValue] AS [SC0], 
            [Id] AS [SC1] 
        FROM [dbo].[Test] 
            TABLESAMPLE SYSTEM (2.223684e+001 PERCENT) 
            WITH (READUNCOMMITTED) 
    ) AS _MS_UPDSTATS_TBL_HELPER 
    ORDER BY 
        [SC0], 
        [SC1], 
        [SB0000] 
) AS _MS_UPDSTATS_TBL
OPTION (MAXDOP 1)

指定的值在partition_id子句中
对于采样的REPEATABLE，UPDATE STATISTICS的值为1。启用了跟踪标记8666，例如REPEATABLE

对于SQL Server 2012，此计算发生在m_randomSeed中：

 <Field FieldName="m_randomSeed" FieldValue="1" />

其中sqlmin!UnOrderPageScanner::StartScan的内存包含分区ID的低32位，

mov     edx,dword ptr [rcx+30h]
add     edx,dword ptr [rcx+2Ch]

的内存包含正在使用的[rcx+30h]值。 />
随机数生成器是初始的稍后以相同的方法调用[rcx+2Ch]进行修改，其中的指令是：

 REPEATABLE

。将种子乘以sqlmin!RandomNumGenerator::Init十六进制（16807十进制= 75），如上式所示。

之后的随机数（用于单个页面）使用内联到

imul    r9d,r9d,41A7h

的相同基本代码生成。

StatMan

对于上面显示的示例41A7查询，将收集相同的页面关于T-SQL语句：

Xnext = Xseed * 75 mod (231 - 1)

这将匹配以下输出：

SELECT
    P.[partition_id] & 0xFFFFFFFF
FROM sys.partitions AS P
WHERE
    P.[object_id] = OBJECT_ID(N'dbo.Test', N'U')
    AND P.index_id = 1;

Edge case

使用MINSTD Lehmer随机数生成器的一个结果是，不应使用种子值零和int.max，因为这将导致算法生成零序列（选择每一页）。

在这种情况下，代码检测到零，并使用系统“ clock”中的值作为种子。如果种子为int.max（sqlmin!UnOrderPageScanner::SetupSubScanner = 231-1），则不会做同样的事情。分区ID和StatMan值。 0x7FFFFFFF值将导致种子为int.max，因此每个样本页面都被选择为：

SELECT 
    COUNT_BIG(*) 
FROM dbo.Test AS T 
    TABLESAMPLE SYSTEM (2.223684e+001 PERCENT)  -- Same sample %
    REPEATABLE (1)                              -- Always 1 for statman
    WITH (INDEX(0));                            -- Scan base object

将其工作为完整示例：

SELECT 
    DDSP.rows_sampled
FROM sys.stats AS S
CROSS APPLY sys.dm_db_stats_properties(S.[object_id], S.stats_id) AS DDSP
WHERE 
    S.[object_id] = OBJECT_ID(N'dbo.Test', N'U')
    AND S.[name] = N'IX_Test_TextValue';

无论REPEATABLE子句说什么（即使为零），这都会选择每一页上的每一行。

#2 楼

这是一个很好的问题！我将从确定的知识开始，然后再进行猜测。有关此问题的许多详细信息，请参见此处。

抽样统计信息更新在幕后使用TABLESAMPLE。在线查找有关该文档的文档非常容易。但是，我相信由TABLESAMPLE返回的行部分取决于对象的hobt_id并不为人所知。删除并重新创建对象时，会得到一个新的hobt_id，因此随机采样返回的行是不同的。

如果删除并重新插入数据，则hobt_id保持不变。只要在磁盘上以相同的方式布置数据（分配顺序扫描以相同的顺序返回相同的结果），那么采样的数据就不应更改。

您还可以更改数字通过重建表上的聚集索引采样的行数。例如：

UPDATE STATISTICS dbo.Test IX_Test_TextValue;

DBCC SHOW_STATISTICS('dbo.Test', IX_Test_TextValue) WITH STAT_HEADER; -- 273862 rows

ALTER INDEX PK_Test on Test REBUILD;

UPDATE STATISTICS dbo.Test IX_Test_TextValue;

DBCC SHOW_STATISTICS('dbo.Test', IX_Test_TextValue) WITH STAT_HEADER; -- 273320 rows

关于发生这种情况的原因，我认为这是因为SQL Server在收集索引上的抽样统计信息时会扫描聚集索引而不是非聚集索引。我还认为与REPEATABLE一起使用的TABLESAMPLE有一个隐藏的（对于我们这些人来说，是跟踪隐藏的统计信息更新查询的）值。我还没有证明这一点，但是它解释了为什么您的直方图和采样的行会随着聚簇索引的重建而变化。

#3 楼

我忘记了TABLESAMPLE如何为每页分配随机概率。 -Martin Smith

我在Inside Microsoft SQL Server 2008中看到了这一点：Itzik Ben-Gan的T-SQL查询，我无法将其添加为注释，所以我认为将其发布在此处对其他人也很有趣：

另请参见Roji的使用TABLESAMPLE进行采样。 P.托马斯。

编程黑洞网

样本大小的异常行为可用于统计信息更新

评论

#1 楼

#2 楼

#3 楼