加入收藏 | 设为首页 | 会员中心 | 我要投稿 西双版纳站长网 (https://www.0691zz.com.cn/)- 数据计算、IT业界、服务器、教程、云日志!
当前位置: 首页 > 站长学院 > MsSql教程 > 正文

sql-server – 统计信息更新的样本大小的奇怪行为

发布时间:2020-12-25 18:00:35 所属栏目:MsSql教程 来源:网络整理
导读:我一直在玩SQL Server(2012)上的统计更新来调查采样阈值,并注意到一些奇怪的行为.基本上,在某些情况下,采样的行数似乎会有所不同 – 即使使用相同的数据集也是如此. 我运行这个查询: --Drop table if existsIF (OBJECT_ID('dbo.Test')) IS NOT NULL DROP TAB

我一直在玩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

我的期望是,每次表格相同时,这个数字都是相同的.顺便说一下,如果我只是删除数据并重新插入它,我就不会得到这种行为.

这不是一个关键问题,但我有兴趣了解正在发生的事情.

解决方法

背景

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

SELECT 
    StatMan([SC0],[SC1],[SB0000]) 
FROM 
(
    SELECT TOP 100 PERCENT 
        [SC0],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],[SB0000] 
) AS _MS_UPDSTATS_TBL
OPTION (MAXDOP 1)

您可以使用扩展事件或事件探查器(SP:StmtCompleted)收集此语句.

统计信息生成查询通常访问基表(而不是非聚簇索引)以避免在非聚簇索引页上自然发生的值的聚类.

采样的行数取决于为采样选择的整个页面的数量.表的每个页面都是选中的,或者不是.所选页面上的所有行都有助于统计.

随机数

SQL Server使用随机数生成器来确定页面是否符合条件.在这种情况下使用的生成器是Lehmer random number generator,参数值如下所示:

Xnext = Xseed * 75 mod (231 - 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子句中指定的值

>对于采样的UPDATE STATISTICS,REPEATABLE值为1.
>当启用跟踪标志8666时,此值在执行计划中显示的访问方法的内部调试信息的m_randomSeed元素中公开,例如< Field FieldName =“m_randomSeed”FieldValue =“1”/>

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

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

其中[rcx 30h]的内存包含分区ID的低32位,[rcx 2Ch]的内存包含正在使用的REPEATABLE值.

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

imul    r9d,r9d,41A7h

…将种子乘以41A7十六进制(16807十进制= 75),如上面的等式所示.

后来的随机数(针对单个页面)使用内联到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,因为这将导致算法产生一系列零(选择每一页).

代码检测到零,并在此情况下使用系统“clock”中的值作为种子.如果种子是int.max(0x7FFFFFFF = 231-1),它不会这样做.

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

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子句如何(即使为零百分比),这将选择每个页面上的每一行.

(编辑:西双版纳站长网)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

    热点阅读