Comportamento estranho com tamanhos de amostra para atualizações de estatísticas

Fundo

Os dados para o objeto de estatísticas são coletados usando uma declaração do formulário:

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

Você pode coletar essa declaração com Extended Events ou Profiler ( SP:StmtCompleted).

As consultas de geração de estatísticas geralmente acessam a tabela base (em vez de um índice não clusterizado) para evitar o agrupamento de valores que ocorre naturalmente em páginas de índice não clusterizadas.

O número de linhas amostradas depende do número de páginas inteiras selecionadas para amostragem. Cada página da tabela está selecionada ou não. Todas as linhas nas páginas selecionadas contribuem para as estatísticas.

Números aleatórios

O SQL Server usa um gerador de números aleatórios para decidir se uma página se qualifica ou não. O gerador usado nesta instância é o gerador de números aleatórios Lehmer com valores de parâmetros conforme mostrado abaixo:

X próximo = X semente * 7 5 mod (2 31 - 1)

O valor de é calculado como a soma de:Xseed

• A parte inteira baixa da biginttabela base ( ), partition_idpor exemplo

  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;
  

• O valor especificado na REPEATABLEcláusula

  • Para amostrado UPDATE STATISTICS, o REPEATABLEvalor é 1.
  • Este valor é exposto no m_randomSeedelemento das informações de depuração interna do método de acesso mostradas nos planos de execução quando o sinalizador de rastreamento 8666 está habilitado, por exemplo<Field FieldName="m_randomSeed" FieldValue="1" />

Para o SQL Server 2012, esse cálculo ocorre em sqlmin!UnOrderPageScanner::StartScan:

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

onde memory at [rcx+30h]contém os 32 bits mais baixos do id da partição e memory at [rcx+2Ch]contém o REPEATABLEvalor em uso.

O gerador de números aleatórios é inicializado posteriormente no mesmo método, chamando sqlmin!RandomNumGenerator::Init, onde a instrução:

imul    r9d,r9d,41A7h

...multiplica a semente por 41A7hexadecimal (16807 decimal = 7 ⁵ ) como mostrado na equação acima.

Números aleatórios posteriores (para páginas individuais) são gerados usando o mesmo código básico embutido em sqlmin!UnOrderPageScanner::SetupSubScanner.

StatMan

Para a StatManconsulta de exemplo mostrada acima, as mesmas páginas serão coletadas para a instrução 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

Isso corresponderá à saída de:

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';

Caso de borda

Uma consequência do uso do gerador de números aleatórios MINSTD Lehmer é que os valores de semente zero e int.max não devem ser usados, pois isso fará com que o algoritmo produza uma sequência de zeros (selecionando cada página).

O código detecta zero e usa um valor do 'relógio' do sistema como a semente nesse caso. Não faz o mesmo se a semente for int.max ( 0x7FFFFFFF= 2 ³¹ - 1).

Podemos projetar esse cenário, pois a semente inicial é calculada como a soma dos 32 bits mais baixos do ID da partição e do REPEATABLEvalor. O REPEATABLEvalor que resultará na semente sendo int.max e, portanto, em cada página selecionada para amostra é:

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;

Trabalhando isso em um exemplo completo:

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);

Isso selecionará todas as linhas em todas as páginas, independentemente da TABLESAMPLEcláusula (mesmo zero por cento).

Essa é uma excelente pergunta! Vou começar com o que sei com certeza e depois passar para a especulação. Muitos detalhes sobre isso no meu post aqui .

Atualizações de estatísticas de amostra usadas TABLESAMPLEnos bastidores. É muito fácil encontrar documentação sobre isso online. No entanto, acredito que não seja bem conhecido que as linhas retornadas por TABLESAMPLEdependem parcialmente hobt_iddo objeto. Quando você descarta e recria o objeto, obtém um novo hobt_idpara que as linhas retornadas por amostragem aleatória sejam diferentes.

Se você excluir e reinserir os dados, eles hobt_idpermanecem os mesmos. Contanto que os dados sejam dispostos da mesma maneira no disco (uma varredura de ordem de alocação retorna os mesmos resultados na mesma ordem), os dados amostrados não devem ser alterados.

Você também pode alterar o número de linhas amostradas recriando o índice clusterizado na tabela. Por exemplo:

UPDATE STATISTICS dbo.Test IX_Test_TextValue;

DBCC SHOW_STATISTICS('dbo.Test', IX_Test_TextValue) WITH STAT_HEADER; -- 273862 rows

ALTER INDEX PK_Test on Test REBUILD;

UPDATE STATISTICS dbo.Test IX_Test_TextValue;

DBCC SHOW_STATISTICS('dbo.Test', IX_Test_TextValue) WITH STAT_HEADER; -- 273320 rows

Por que isso acontece, acredito que seja porque o SQL Server verifica o índice clusterizado em vez do índice não clusterizado ao coletar estatísticas de amostra em um índice. Eu também acho que há um valor oculto (para aqueles de nós que rastreiam as consultas de atualização de estatísticas ocultas) para REPEATABLEusado com TABLESAMPLE. Eu não provei nada disso, mas explica por que seu histograma e as linhas amostradas mudam com uma reconstrução do índice clusterizado.

Esqueci como TABLESAMPLE funcionava em termos de atribuição de uma probabilidade aleatória por página. - Martin Smith

Eu vi isso em Inside Microsoft SQL Server 2008: T-SQL Querying de Itzik Ben-Gan e não posso adicioná-lo como comentário, então posto aqui, acho que é interessante para outros também:

Veja também Amostragem Usando TABLESAMPLE de Roji. P. Thomas.