Início / dba / Perguntas / 120064
Accepted
Geoff Patterson
Asked: 2015-11-05 06:24:23 +0800 CST

Por que a função LEN() subestima mal a cardinalidade no SQL Server 2014?

  • 772

Eu tenho uma tabela com uma coluna de string e um predicado que verifica se há linhas com um determinado comprimento. No SQL Server 2014, vejo uma estimativa de 1 linha, independentemente do comprimento que estou verificando. Isso está gerando planos muito ruins porque, na verdade, existem milhares ou até milhões de linhas e o SQL Server está optando por colocar essa tabela no lado externo de um loop aninhado.

Existe uma explicação para a estimativa de cardinalidade de 1,0003 para o SQL Server 2014 enquanto o SQL Server 2012 estima 31.622 linhas? Existe uma boa solução alternativa?

Aqui está uma pequena reprodução do problema:

-- Create a table with 1MM rows of dummy data
CREATE TABLE #customers (cust_nbr VARCHAR(10) NOT NULL)
GO

INSERT INTO #customers WITH (TABLOCK) (cust_nbr)
    SELECT TOP 1000000 
        CONVERT(VARCHAR(10),
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) AS cust_nbr
    FROM master..spt_values v1
    CROSS JOIN master..spt_values v2
GO

-- Looking for string of a certain length.
-- While both CEs yield fairly poor estimates, the 2012 CE is much
-- more conservative (higher estimate) and therefore much more likely
-- to yield an okay plan rather than a drastically understimated loop join.
-- 2012: 31,622 rows estimated, 900K rows actual
-- 2014: 1 row estimated, 900K rows actual
SELECT COUNT(*)
FROM #customers
WHERE LEN(cust_nbr) = 6
OPTION (QUERYTRACEON 9481) -- Optionally, use 2012 CE
GO

Aqui está um script mais completo mostrando testes adicionais

Também li o whitepaper sobre o Estimador de cardinalidade do SQL Server 2014 , mas não encontrei nada que esclarecesse a situação.

sql-server sql-server-2014

2 respostas

  1. Best Answer

    Para o CE herdado, vejo que a estimativa é de 3,16228% das linhas - e essa é uma heurística de "número mágico" usada para coluna = predicados literais (existem outras heurísticas baseadas na construção de predicados - mas o LENenrolado em torno da coluna para o os resultados de CE herdados correspondem a essa estrutura de suposição). Você pode ver exemplos disso em uma postagem sobre Suposições de seletividade na ausência de estatísticas , de Joe Sack, e Estimativa de comparação constante-constante , de Ian Jose.

    -- Legacy CE: 31622.8 rows
SELECT  COUNT(*)
FROM    #customers
WHERE   LEN(cust_nbr) = 6
OPTION  ( QUERYTRACEON 9481); -- Legacy CE
GO

    Agora, quanto ao novo comportamento do CE, parece que agora está visível para o otimizador (o que significa que podemos usar estatísticas). Fiz o exercício de observar a saída da calculadora abaixo e você pode observar a geração automática de estatísticas associada como um ponteiro:

    -- New CE: 1.00007 rows
SELECT  COUNT(*)
FROM    #customers
WHERE   LEN(cust_nbr) = 6
OPTION  ( QUERYTRACEON 2312 ); -- New CE
GO

-- View New CE behavior with 2363 (for supported option use XEvents)
SELECT  COUNT(*)
FROM    #customers
WHERE   LEN(cust_nbr) = 6
OPTION  (QUERYTRACEON 2312, QUERYTRACEON 2363, QUERYTRACEON 3604, RECOMPILE); -- New CE
GO

/*
Loaded histogram for column QCOL:
[tempdb].[dbo].[#customers].cust_nbr from stats with id 2
Using ambient cardinality 1e+006 to combine distinct counts:
  999927
 
Combined distinct count: 999927
Selectivity: 1.00007e-006
Stats collection generated:
  CStCollFilter(ID=2, CARD=1.00007)
      CStCollBaseTable(ID=1, CARD=1e+006 TBL: #customers)
 
End selectivity computation
*/
 
EXEC tempdb..sp_helpstats '#customers';


--Check out AVG_RANGE_ROWS values (for example - plenty of ~ 1)
DBCC SHOW_STATISTICS('tempdb..#customers', '_WA_Sys_00000001_B0368087');
--That's my Stats name yours is subject to change

    Infelizmente, a lógica depende de uma estimativa do número de valores distintos, que não é ajustada para o efeito da LENfunção.

    Possível solução alternativa

    Você pode obter uma estimativa baseada em trie em ambos os modelos CE reescrevendo o LENcomo LIKE:

    SELECT COUNT_BIG(*)
FROM #customers AS C
WHERE C.cust_nbr LIKE REPLICATE('_', 6);

    plano LIKE

    Informações sobre sinalizadores de rastreamento usados:

    • 2363: mostra muitas informações, incluindo estatísticas sendo carregadas.
    • 3604: imprime a saída dos comandos DBCC na guia de mensagens.
    • 20

  2. Existe uma explicação para a estimativa de cardinalidade de 1,0003 para o SQL 2014 enquanto o SQL 2012 estima 31.622 linhas?

    Acho que a resposta de @Zane cobre muito bem essa parte.

    Existe uma boa solução alternativa?

    Você pode tentar criar uma coluna computada não persistente para LEN(cust_nbr)e (opcionalmente) criar um índice não clusterizado nessa coluna computada. Isso deve fornecer estatísticas precisas.

    Eu fiz alguns testes e aqui está o que eu encontrei:

    • As estatísticas foram criadas automaticamente na coluna computada não persistente, quando nenhum índice foi definido nela.
    • Adicionar o índice não agrupado na coluna computada não apenas não ajudou, mas também prejudicou um pouco o desempenho. CPU e tempos decorridos ligeiramente superiores. Custo estimado ligeiramente mais alto (seja o que for que valha a pena).
    • Tornar a coluna computada como PERSISTED(sem índice) foi melhor do que as outras duas variações. Linhas estimadas eram mais precisas. A CPU e o tempo decorrido foram melhores (como esperado, pois não precisava calcular nada por linha).
    • Não consegui criar um Índice Filtrado ou Estatísticas Filtradas na Coluna Computada (devido ao fato de estar sendo computada), mesmo que fosse PERSISTED:-(
    • 13

relate perguntas