主页 / dba / 问题 / 180461
Accepted
Matthew McGiffen
Asked: 2017-07-22 06:46:49 +0800 CST

统计更新样本大小的奇怪行为

  • 772

我一直在研究使用 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 的输出时，我发现“Rows Sampled”随着每次完整执行而变化（即表被删除、重新创建和重新填充）。

例如：

采样行

  • 318618
  • 319240
  • 324198
  • 314154

我的期望是这个数字每次都是相同的，因为表格是相同的。顺便说一句，如果我只是删除数据并重新插入数据，我就不会出现这种行为。

这不是一个关键问题，但我有兴趣了解发生了什么。

sql-server sql-server-2012

3 个回答

  1. Best Answer

    背景

    使用以下形式的语句收集统计对象的数据：

    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)

    您可以使用 Extended Events 或 Profiler ( ) 收集此语句SP:StmtCompleted。

    统计生成查询经常访问基表（而不是非聚集索引）以避免在非聚集索引页上自然发生的值聚集。

    抽样的行数取决于选择抽样的整页数。表格的每一页都被选中或未被选中。所选页面上的所有行都有助于统计。

    随机数

    SQL Server 使用随机数生成器来决定页面是否合格。本例中使用的生成器是Lehmer 随机数生成器，其参数值如下所示：

    X下一个= X种子* 7 5 mod (2 31 - 1)

    的值计算为以下各项的总和：Xseed

    • bigint( ) 基表的低整数部分，partition_id例如

      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;

    • REPEATABLE子句中指定的值

      • 对于 sampled UPDATE STATISTICS，REPEATABLE值为 1。
      • m_randomSeed例如，当启用跟踪标志 8666 时，此值会暴露在执行计划中显示的访问方法的内部调试信息的元素中<Field FieldName="m_randomSeed" FieldValue="1" />

    对于 SQL Server 2012，此计算发生在sqlmin!UnOrderPageScanner::StartScan：

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

    其中 memory at[rcx+30h]包含分区 ID 的低 32 位，memory at[rcx+2Ch]包含REPEATABLE正在使用的值。

    稍后在同一方法中初始化随机数生成器，调用sqlmin!RandomNumGenerator::Init，其中指令：

    imul    r9d,r9d,41A7h

    ...将种子乘以41A7十六进制（16807 十进制 = 7 5），如上面的等式所示。

    以后的随机数（针对单个页面）是使用内联到sqlmin!UnOrderPageScanner::SetupSubScanner.

    统计人

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

    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';

    边缘案例

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

    代码检测到零，并在这种情况下使用系统“时钟”中的值作为种子。0x7FFFFFFF如果种子是 int.max ( = 2 31 - 1) ，它不会做同样的事情。

    我们可以设计这种情况，因为初始种子计算为分区 ID 的低 32 位和REPEATABLE值的总和。将REPEATABLE导致种子为 int.max 并因此为样本选择每个页面的值是：

    SELECT
    0x7FFFFFFF - (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;

    把它变成一个完整的例子：

    DECLARE @SQL nvarchar(4000) = 
    N'
    SELECT
        COUNT_BIG(*) 
    FROM dbo.Test AS T 
        TABLESAMPLE (0 PERCENT) 
        REPEATABLE (' +
        (
            SELECT TOP (1)
                CONVERT(nvarchar(11), 0x7FFFFFFF - 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
        ) + ')
        WITH (INDEX(0));';

PRINT @SQL;
--EXECUTE (@SQL);

    这将选择每个页面上的每一行，无论TABLESAMPLE子句怎么说（甚至是零百分比）。

    • 27

  2. 这是一个很好的问题！我将从我确定知道的开始，然后再进行推测。我的博客文章中有很多关于此的详细信息。

    抽样统计更新TABLESAMPLE在幕后使用。在网上很容易找到关于它的文档。TABLESAMPLE但是，我相信partially 取决于对象的行返回的行并不为人所知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. 我还没有证明任何一个，但它解释了为什么你的直方图和采样的行随着聚集索引的重建而改变。

    • 11

  3. 我忘记了 TABLESAMPLE 在每页分配随机概率方面的工作原理。-马丁·史密斯

    我在 Itzik Ben-Gan 的Inside Microsoft SQL Server 2008: T-SQL Querying 中看到了这一点，但我无法将其添加为评论，所以我将其发布在这里，我认为其他人也很感兴趣：

    在此处输入图像描述

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

    • 3

相关问题