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子句如何(即使为零百分比),这将选择每个页面上的每一行. （编辑：核心网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
