主页 / dba / 问题 / 339713
Accepted
Paul White
Asked: 2024-05-22 23:24:21 +0800 CST

抽样统计百分比内部计算

  • 772

SQL Server构建采样统计时，采样百分比是如何计算的？

例如，更新Stack Overflow 2013 数据库中dbo.Votes表的PostId列的统计信息：

UPDATE STATISTICS dbo.Votes
(
    _WA_Sys_00000002_0AD2A005
) 
WITH MAXDOP = 1;

SQL Server 构建一个内部语句：

SELECT 
    StatMan
    (
        [SC0], 
        [SB0000]
    ) 
    FROM 
    (
        SELECT TOP 100 PERCENT 
            [SC0], 
            step_direction([SC0]) over (order by NULL) AS [SB0000]  
        FROM 
        (
            SELECT [PostId] AS [SC0] 
            FROM [dbo].[Votes] 
                TABLESAMPLE SYSTEM (9.234204e-01 PERCENT) 
                WITH (READUNCOMMITTED) 
        ) AS _MS_UPDSTATS_TBL_HELPER 
        ORDER BY 
            [SC0], 
            [SB0000] 
    ) AS _MS_UPDSTATS_TBL  
OPTION (MAXDOP 1)

它从哪里来9.234204e-01 PERCENT？

sql-server

1 个回答

  1. Best Answer

    采样百分比是根据表中的行数和为统计信息收集选择的索引中的叶级页数得出的。该“索引”通常是基表（聚集索引或堆）。

    这两个输入参数可以从 DMV 获得：

    DECLARE 
    @NumRows float,
    @NumPages float;

SELECT 
    @NumRows = CONVERT(float, SUM(IPS.record_count)),
    @NumPages = CONVERT(float, SUM(IPS.page_count))
FROM sys.dm_db_index_physical_stats
(
    DB_ID(), 
    OBJECT_ID(N'dbo.Votes', 'U'), 
    1, 
    NULL, 
    N'DETAILED'
) AS IPS
WHERE
    IPS.index_level = 0;

    计算公式为：

    DECLARE
    @MinPages float = 1.024e+003,
    @HundredPercent float = 1e+002,
    @Magic1 float = 1.5e+001,
    @Magic2 float = 5.5e-001;

SELECT
    SamplePercentage =
        IIF
        (
            @NumPages < @MinPages,
            @HundredPercent,
            @HundredPercent *
            LEAST
            (
                @MinPages +
                ROUND
                (
                    @NumPages *
                    (
                        @Magic1 * 
                        ROUND
                        (
                            POWER
                            (
                                @NumRows,
                                @Magic2
                            ), 
                            0, 
                            1
                        )
                    ) / 
                    @NumRows, 
                    0, 
                    1
                ),
                @NumPages
            ) / 
            @NumPages
        );

    这将返回：

    样本百分比
    0.923420362201546

    9.234204e-01 PERCENT这与问题中给出的相符。

    • 5

相关问题