sql-server – 统计信息更新的样本大小的奇怪行为
我一直在玩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 我的期望是,每次表格相同时,这个数字都是相同的.顺便说一下,如果我只是删除数据并重新插入它,我就不会得到这种行为. 这不是一个关键问题,但我有兴趣了解正在发生的事情. 解决方法背景使用以下形式的语句收集统计对象的数据: 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. 对于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子句如何(即使为零百分比),这将选择每个页面上的每一行. (编辑:西双版纳站长网) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |