主页 / dba / 问题 / 345635
Accepted
J. Mini
Asked: 2025-03-08 06:24:00 +0800 CST

auto_stats 扩展事件是否会错误报告时间表中的样本百分比？

  • 772

启动 auto_stats 扩展事件，过滤掉一些噪音，并准备监控它。

CREATE EVENT SESSION [AutoStatsTest] ON SERVER 
ADD EVENT sqlserver.auto_stats(
    WHERE ([duration]>1 AND [object_id] > 0) ) 
ADD TARGET package0.ring_buffer

ALTER EVENT SESSION [AutoStatsTest] ON SERVER
STATE = START
GO

找到一个时态表。我手头有一份 StackOverflow2010 数据库的副本，所以我直接对Votes系统进行了版本控制。

ALTER TABLE Votes ADD
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN
    CONSTRAINT DF_InsurancePolicy_ValidFrom DEFAULT SYSUTCDATETIME(),
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
    CONSTRAINT DF_InsurancePolicy_ValidTo DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),
PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo);
GO

ALTER TABLE Votes
    SET (SYSTEM_VERSIONING = ON);
GO

使用你的时态表，做一些能够触发统计信息自动更新的操作。这个方法对我来说很有效

SELECT TOP (1000) [BountyAmount]
FROM [StackOverflow2010].[dbo].[Votes]
WHERE [PostId] < [BountyAmount]

UPDATE [StackOverflow2010].[dbo].[Votes]
SET BountyAmount = 4*PostId

SELECT TOP (1000) [BountyAmount]
FROM [StackOverflow2010].[dbo].[Votes]
WHERE [PostId] < [BountyAmount]

现在检查扩展事件的 XML

SELECT target_data
FROM sys.dm_xe_session_targets AS xet
JOIN sys.dm_xe_sessions AS xe
ON (xe.address = xet.event_session_address)
WHERE xe.name = 'AutoStatsTest'

如果您像我一样，您会看到一些报告 100% sample_percentage 的字段。

        <data name="sample_percentage">
            <type name="int64" package="package0">
            </type>
            <value>
                100
            </value>
        </data>

考虑到持续时间（在我那台破机器上只有 800 微秒），我知道这完全是胡扯。查阅了资料后sys.dm_db_stats_properties，我发现实际百分比远低于 100。

/*
Save yourself the pain of writing
the stats query
and just use sp_BlitzIndex from GitHub
*/
EXEC sp_blitzindex @databasename = 'StackOverflow2010', @tablename = 'Votes'

总而言之，auto_stats 扩展事件报告的时态表的 sample_percentage 值似乎有误。这是 SQL Server 的 bug 还是我的误解？如果是我的误解，那么我可以在哪里阅读更多内容？据我所知，单个扩展事件的文档非常缺乏。

我只在时态表中见过这种情况。我在 SQL Server 2022 的真实环境中（该表有数 TB 大小，所以当我看到主键统计信息的 100% 采样率时，我惊呼不已）和本地 2019 测试环境中都发现了这种情况。

添加列存储索引似乎有助于重现这种情况，但我不是 100% 确定。

CREATE NONCLUSTERED COLUMNSTORE INDEX IX_ColStore
on dbo.Votes
(
    Id,
    PostId
)

我确信这里确实存在一个 bug。然而，我尝试重现它时，结果并不一致。我怀疑你需要一个 TB 级的表格。

最后更新：我不再解释这一点了。该表的统计信息显示，用于更新统计信息的行数是实际执行计划（在生产环境中运行时实时捕获，扫描非聚集列存储索引）中实际行数的 90 倍。实际执行计划中的行数比表的行数少 10,000 倍，而 auto_stats 扩展事件显示表中 100% 的行已被读取。所有这些不可能同时成立。

我怀疑实际上可能是删除的位图导致了这一切。

sql-server

1 个回答

  1. Best Answer

    这不是时态表，而是列存储。

    集群或非集群都可以，所以我推测是被删除的位图导致的。我会使用 StackOverflow 数据库，因为我手边有它。

    首先，关闭 Tuple Mover。我们不想让它干扰。

    DBCC TRACEON (634, -1)

    现在，取任意一张大表，删除其所有索引。之后，为其添加列存储索引。

    CREATE CLUSTERED COLUMNSTORE INDEX IX_ColStore
ON dbo.Posts;

    现在在你的表上创建一些统计信息，因为两种基于磁盘的列存储默认都不执行此操作。由于我也想关闭这个问题，所以我保留了一个较低的样本百分比。

    CREATE STATISTICS MyStat ON [dbo].[Posts] ([CreationDate])
WITH PERSIST_SAMPLE_PERCENT = ON, SAMPLE 1 PERCENT

    敲敲桌子，让统计数据过时。你或许可以选个比我更早的日期。

    UPDATE [StackOverflow2010].[dbo].[Posts]
SET [CreationDate] = DATEADD(DAY, 1, [CreationDate])
WHERE CreationDate < '20100101';

    并确认它们已经过时

    EXEC sp_blitzindex @databasename = 'StackOverflow2010', @tablename = 'Posts'

    现在从扩展事件开始监视自动统计更新。

    CREATE EVENT SESSION [AutoStatsTest] ON SERVER 
ADD EVENT sqlserver.auto_stats(
    WHERE ([duration]>1 AND [object_id] > 0) ) 
ADD TARGET package0.ring_buffer

ALTER EVENT SESSION [AutoStatsTest] ON SERVER
STATE = START
GO

    在另一个选项卡中，我还抓取了执行计划。这里没有展示。

    现在运行一个查询，该查询将触发自动统计更新

    SELECT TOP (12000) [CreationDate]
FROM [StackOverflow2010].[dbo].[Posts]
WHERE (select 1) = 1 AND CreationDate < '20220102'

    之后，检查扩展事件的输出

    SELECT target_data
FROM sys.dm_xe_session_targets AS xet
JOIN sys.dm_xe_sessions AS xe
ON (xe.address = xet.event_session_address)
WHERE xe.name = 'AutoStatsTest'

    我的报告很多，但特别是这个

            <data name="sample_percentage">
            <type name="int64" package="package0">
            </type>
            <value>
                100
            </value>
        </data>
        <data name="duration">
            <type name="uint64" package="package0">
            </type>
            <value>
                550773
            </value>
        </data>
[...]
        <data name="statistics_list">
            <type name="unicode_string" package="package0">
            </type>
            <value>
                <![CDATA[Updated: StackOverflow2010.dbo.Posts.MyStat]]>
            </value>
        </data>

    最后检查统计信息实际使用的行数。

    EXEC sp_blitzindex @databasename = 'StackOverflow2010', @tablename = 'Posts'

    spBlitzIndex 输出 如图所示，我们实际上只读取了 3,729,195 行中的 37,164 行。也就是 1%，但 auto_stats 显示我们读取了 100%。执行计划似乎也报告了 1%。

    所以，是的，这确实是 SQL Server 的一个 bug。我推测删除的位图会以某种方式干扰 auto_stats，但我不知道具体是怎么回事。

    • 0

相关问题