Início / dba / Perguntas / 153295
Accepted
Fza
Asked: 2016-10-26 09:57:12 +0800 CST

Estimativa de Cardinalidade para Operador LIKE (Variáveis ​​Locais)

  • 772

Fiquei com a impressão de que, ao usar o LIKEoperador em todas as otimizações para cenários desconhecidos, tanto o legado quanto os novos CEs usam uma estimativa de 9% (supondo que estatísticas relevantes estejam disponíveis e o otimizador de consulta não precise recorrer a suposições de seletividade).

Ao executar a consulta abaixo no banco de dados de crédito, obtenho diferentes estimativas nos diferentes CEs. Sob o novo CE, recebo uma estimativa de 900 linhas que eu esperava, sob o CE herdado, recebo uma estimativa de 241,416 e não consigo descobrir como essa estimativa é derivada. Alguém é capaz de lançar alguma luz?

-- New CE (Estimate = 900)
DECLARE @LastName VARCHAR(15) = 'BA%'
SELECT * FROM [Credit].[dbo].[member]
WHERE [lastname] LIKE @LastName;

-- Forcing Legacy CE (Estimate = 241.416)
DECLARE @LastName VARCHAR(15) = 'BA%'
SELECT * FROM [Credit].[dbo].[member]
WHERE [lastname] LIKE @LastName
OPTION (
QUERYTRACEON 9481,
QUERYTRACEON 9292,
QUERYTRACEON 9204,
QUERYTRACEON 3604
);

No meu cenário, já tenho o banco de dados de crédito definido para o nível de compatibilidade 120, por isso na segunda consulta estou usando sinalizadores de rastreamento para forçar o CE herdado e também para fornecer informações sobre quais estatísticas são usadas/consideradas pelo otimizador de consulta. Posso ver que as estatísticas da coluna 'sobrenome' estão sendo usadas, mas ainda não consigo descobrir como a estimativa de 241,416 é derivada.

Não consegui encontrar nada on-line além deste artigo de Itzik Ben-Gan , que afirma "Ao usar o predicado LIKE em todas as otimizações para cenários desconhecidos, tanto o legado quanto os novos CEs usam uma estimativa de 9 por cento". As informações nesse post parecem estar incorretas.

sql-server sql-server-2014

2 respostas

  1. Best Answer

    O palpite para LIKE o seu caso é baseado em:

    • G: O palpite padrão de 9% ( sqllang!x_Selectivity_Like)
    • M: Um fator de 6 (número mágico)
    • D: Comprimento médio dos dados em bytes (de estatísticas), arredondado para número inteiro

    Especificamente, sqllang!CCardUtilSQL7::ProbLikeGuessusa:

    Selectivity (S) = G / M * LOG(D)

    Notas:

    • oLOG(D) termo é omitido se Destiver entre 1 e 2.
    • Se Dfor menor que 1 (incluindo falta ou NULLestatísticas):
      D = FLOOR(0.5 * maximum column byte length)

    Esse tipo de estranheza e complexidade é bastante típico do CE original.

    No exemplo da pergunta, o comprimento médio é 5 (5,6154 deDBCC SHOW_STATISTICS arredondado para baixo):

    Estimativa = 10.000 * (0,09 / 6 * LOG(5)) = 241,416

    Outros valores de exemplo:

     D   = Estimar usando a fórmula para S
 15 = 406.208
 14 = 395,859
 13 = 384.742
 12 = 372.736
 11 = 359.684
 10 = 345,388
 09 = 329.584
 08 = 311.916
 07 = 291.887
 06 = 268.764
 05 = 241.416
 04 = 207.944
 03 = 164,792
 02 = 150.000 (LOG não utilizado)
 01 = 150.000 (LOG não utilizado)
 00 = 291.887 (LOG 7) /* FLOOR(0.5 * 15) [15 já que o sobrenome é varchar(15)] */

    Equipamento de teste

    DECLARE
    @CharLength integer = 5, -- Set length here
    @Counter integer = 1;

CREATE TABLE #T (c1 varchar(15) NULL);

-- Add 10,000 rows
SET NOCOUNT ON;
SET STATISTICS XML OFF;

BEGIN TRANSACTION;
WHILE @Counter <= 10000
BEGIN
    INSERT #T (c1) VALUES (REPLICATE('X', @CharLength));
    SET @Counter = @Counter + 1;
END;
COMMIT TRANSACTION;

SET NOCOUNT OFF;
SET STATISTICS XML ON;

-- Test query
DECLARE @Like varchar(15);
SELECT * FROM #T AS T 
WHERE T.c1 LIKE @Like;

DROP TABLE #T;
    • 28

  2. Testei no SQL Server 2014 com o CE legado e também não obtive 9% como estimativa de cardinalidade. Não consegui encontrar nada preciso online, então fiz alguns testes e encontrei um modelo que se encaixa em todos os casos de teste que tentei, mas não tenho certeza se está completo.

    No modelo que encontrei, a estimativa é derivada do número de linhas na tabela, o tamanho médio da chave das estatísticas para a coluna filtrada e, às vezes, o tamanho do tipo de dados da coluna filtrada. Existem duas fórmulas diferentes usadas para a estimativa.

    Se FLOOR(comprimento médio da chave) = 0, a fórmula de estimativa ignora as estatísticas da coluna e cria uma estimativa com base no comprimento do tipo de dados. Eu testei apenas com VARCHAR(N), então é possível que haja uma fórmula diferente para NVARCHAR(N). Aqui está a fórmula para VARCHAR(N):

    (estimativa de linha) = (linhas na tabela) * (-0,004869 + 0,032649 * log10(comprimento do tipo de dados))

    Isso tem um ajuste muito bom, mas não é perfeitamente preciso:

    primeiro gráfico de fórmula

    O eixo x é o comprimento do tipo de dados e o eixo y é o número de linhas estimadas para uma tabela com 1 milhão de linhas.

    O otimizador de consulta usaria essa fórmula se você não tivesse estatísticas na coluna ou se a coluna tivesse valores NULL suficientes para direcionar o comprimento médio da chave para menos de 1.

    Por exemplo, suponha que você tenha uma tabela com 150k linhas com filtragem em VARCHAR(50) e nenhuma estatística de coluna. A previsão de estimativa de linha é:

    150000 * (-0,004869 + 0,032649 * log10(50)) = 7590,1 linhas

    SQL para testar:

    CREATE TABLE X_CE_LIKE_TEST_1 (
STRING VARCHAR(50)
);

CREATE STATISTICS X_STAT_CE_LIKE_TEST_1 ON X_CE_LIKE_TEST_1 (STRING) WITH NORECOMPUTE;

WITH
    L0 AS (SELECT 1 AS c UNION ALL SELECT 1),
    L1 AS (SELECT 1 AS c FROM L0 A CROSS JOIN L0 B),
    L2 AS (SELECT 1 AS c FROM L1 A CROSS JOIN L1 B),
    L3 AS (SELECT 1 AS c FROM L2 A CROSS JOIN L2 B),
    L4 AS (SELECT 1 AS c FROM L3 A CROSS JOIN L3 B CROSS JOIN L2 C),
    NUMS AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS NUM FROM L4)  
    INSERT INTO X_CE_LIKE_TEST_1 WITH (TABLOCK) (STRING)
    SELECT TOP (150000) 'ZZZZZ'
    FROM NUMS
    ORDER BY NUM;

DECLARE @LastName VARCHAR(15) = 'BA%'
SELECT * FROM X_CE_LIKE_TEST_1
WHERE STRING LIKE @LastName;

    O SQL Server fornece uma contagem de linha estimada de 7242,47, que é quase fechada.

    Se FLOOR(comprimento médio da chave) >= 1, uma fórmula diferente é usada com base no valor de FLOOR(comprimento médio da chave). Aqui está uma tabela de alguns dos valores que eu tentei:

    1    1.5%
2    1.5%
3    1.64792%
4    2.07944%
5    2.41416%
6    2.68744%
7    2.91887%
8    3.11916%
9    3.29584%
10   3.45388%

    Se FLOOR(tamanho médio da chave) < 6, use a tabela acima. Caso contrário, use a seguinte equação:

    (estimativa de linha) = (linhas na tabela) * (-0,003381 + 0,034539 * log10(FLOOR(tamanho médio da chave)))

    Este tem um ajuste melhor do que o outro, mas ainda não é perfeitamente preciso.

    segundo gráfico de fórmula

    O eixo x é o comprimento médio da chave e o eixo y é o número de linhas estimadas para uma tabela com 1 milhão de linhas.

    Para dar outro exemplo, suponha que você tenha uma tabela com 10 mil linhas com um comprimento médio de chave de 5,5 para as estatísticas na coluna filtrada. A estimativa de linha seria:

    10000 * 0,241416 = 241,416 linhas.

    SQL para testar:

    CREATE TABLE X_CE_LIKE_TEST_2 (
STRING VARCHAR(50)
);

WITH
    L0 AS (SELECT 1 AS c UNION ALL SELECT 1),
    L1 AS (SELECT 1 AS c FROM L0 A CROSS JOIN L0 B),
    L2 AS (SELECT 1 AS c FROM L1 A CROSS JOIN L1 B),
    L3 AS (SELECT 1 AS c FROM L2 A CROSS JOIN L2 B),
    L4 AS (SELECT 1 AS c FROM L3 A CROSS JOIN L3 B CROSS JOIN L2 C),
    NUMS AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS NUM FROM L4)  
    INSERT INTO X_CE_LIKE_TEST_2 WITH (TABLOCK) (STRING)
    SELECT TOP (10000) 
    CASE 
      WHEN NUM % 2 = 1 THEN REPLICATE('Z', 5) 
      ELSE REPLICATE('Z', 6)
    END
    FROM NUMS
    ORDER BY NUM;

CREATE STATISTICS X_STAT_CE_LIKE_TEST_2 ON X_CE_LIKE_TEST_2 (STRING) 
WITH NORECOMPUTE, FULLSCAN;

DECLARE @LastName VARCHAR(15) = 'BA%'
SELECT * FROM X_CE_LIKE_TEST_2
WHERE STRING LIKE @LastName;

    A estimativa de linha é 241.416, que corresponde ao que você tem na pergunta. Haveria algum erro se eu usasse um valor que não estivesse na tabela.

    Os modelos aqui não são perfeitos, mas acho que ilustram muito bem o comportamento geral.

    • 15

relate perguntas