Enorme desaceleração na consulta do SQL Server ao adicionar curinga (ou superior)

Esta é uma decisão do otimizador baseado em custo.

Os custos estimados utilizados nesta escolha são incorretos, pois pressupõe independência estatística entre valores em diferentes colunas.

É semelhante ao problema descrito em Row Goals Gone Rogue , onde os números pares e ímpares são negativamente correlacionados.

É fácil de reproduzir.

CREATE TABLE dbo.animal(
    id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    colour varchar(50) NOT NULL,
    species varchar(50) NOT NULL,
    Filler char(10) NULL
);

/*Insert 20 million rows with 1% black and 1% swan but no black swans*/
WITH T
     AS (SELECT TOP 20000000 ROW_NUMBER() OVER (ORDER BY @@SPID) AS RN
         FROM   master..spt_values v1,
                master..spt_values v2,
                master..spt_values v3)
INSERT INTO dbo.animal
            (colour,
             species)
SELECT CASE
         WHEN RN % 100 = 1 THEN 'black'
         ELSE CAST(RN % 100 AS VARCHAR(3))
       END,
       CASE
         WHEN RN % 100 = 2 THEN 'swan'
         ELSE CAST(RN % 100 AS VARCHAR(3))
       END
FROM   T 

/*Create some indexes*/
CREATE NONCLUSTERED INDEX ix_species ON  dbo.animal(species);
CREATE NONCLUSTERED INDEX ix_colour ON  dbo.animal(colour);

Agora tente

SELECT TOP 10 *
FROM   animal
WHERE  colour LIKE 'black'
       AND species LIKE 'swan' 

Isso fornece o plano abaixo, cujo custo é de 0.0563167.

O plano é capaz de realizar uma junção de mesclagem entre os resultados dos dois índices na idcoluna. ( Mais detalhes sobre o algoritmo de junção de mesclagem aqui ).

A junção de mesclagem requer que ambas as entradas sejam ordenadas pela chave de junção.

Os índices não clusterizados são ordenados por (species, id)e (colour, id)respectivamente (índices não clusterizados não exclusivos sempre têm o localizador de linha adicionado ao final da chave implicitamente , se não adicionado explicitamente). A consulta sem curingas está executando uma busca de igualdade em species = 'swan'e colour ='black'. Como cada busca está recuperando apenas um valor exato da coluna inicial, as linhas correspondentes serão ordenadas por id, portanto, esse plano é possível.

Os operadores do plano de consulta são executados da esquerda para a direita . Com o operador esquerdo solicitando linhas de seus filhos, que por sua vez solicitam linhas de seus filhos (e assim por diante até que os nós folha sejam alcançados). O TOPiterador parará de solicitar mais linhas de seu filho assim que 10 forem recebidas.

O SQL Server possui estatísticas nos índices que informam que 1% das linhas correspondem a cada predicado. Ele assume que essas estatísticas são independentes (ou seja, não correlacionadas positiva ou negativamente), de modo que, em média, depois de processar 1.000 linhas correspondentes ao primeiro predicado, ele encontrará 10 correspondentes ao segundo e poderá sair. (o plano acima na verdade mostra 987 em vez de 1.000, mas próximo o suficiente).

Na verdade, como os predicados são negativamente correlacionados, o plano real mostra que todas as 200.000 linhas correspondentes precisavam ser processadas de cada índice, mas isso é mitigado até certo ponto porque as linhas unidas por zero também significam que nenhuma pesquisa foi realmente necessária.

Compare com

SELECT TOP 10 *
FROM   animal
WHERE  colour LIKE 'black%'
       AND species LIKE 'swan' 

Que dá o plano abaixo que é custeado em0.567943

A adição do caractere curinga à direita agora causou uma varredura de índice. O custo do plano ainda é bastante baixo para uma varredura em uma tabela de 20 milhões de linhas.

Adicionar querytraceon 9130mostra mais algumas informações

SELECT TOP 10 *
FROM   animal
WHERE  colour LIKE 'black%'
       AND species LIKE 'swan'       
OPTION (QUERYTRACEON 9130)  

Pode-se ver que o SQL Server calcula que só precisará verificar cerca de 100.000 linhas antes de encontrar 10 correspondentes ao predicado e TOPparar de solicitar linhas.

Novamente, isso faz sentido com a suposição de independência como10 * 100 * 100 = 100,000

Finalmente, vamos tentar forçar um plano de interseção de índice

SELECT TOP 10 *
FROM   animal WITH (INDEX(ix_species), INDEX(ix_colour))
WHERE  colour LIKE 'black%'
       AND species LIKE 'swan' 

Isso fornece um plano paralelo para mim com custo estimado de 3,4625

A principal diferença aqui é que o colour like 'black%'predicado agora pode corresponder a várias cores diferentes. Isso significa que as linhas de índice correspondentes para esse predicado não têm mais garantia de serem classificadas na ordem de id.

Por exemplo, o index seek on like 'black%'pode retornar as seguintes linhas

+------------+----+
|   Colour   | id |
+------------+----+
| black      | 12 |
| black      | 20 |
| black      | 23 |
| black      | 25 |
| blackberry |  1 |
| blackberry | 50 |
+------------+----+

Dentro de cada cor, os ids são ordenados, mas os ids em cores diferentes podem não ser.

Como resultado, o SQL Server não pode mais executar uma interseção de índice de junção de mesclagem (sem adicionar um operador de classificação de bloqueio) e opta por executar uma junção de hash. O Hash Join está bloqueando a entrada de compilação, então agora o custo reflete o fato de que todas as linhas correspondentes precisarão ser processadas a partir da entrada de compilação, em vez de assumir que terá que digitalizar apenas 1.000 como no primeiro plano.

No entanto, a entrada da sondagem não é bloqueante e ainda estima incorretamente que será capaz de interromper a sondagem após o processamento de 987 linhas a partir disso.

(Mais informações sobre iteradores sem bloqueio vs. com bloqueio aqui)

Dado o aumento dos custos das linhas extras estimadas e da junção de hash, a varredura de índice clusterizado parcial parece mais barata.

Na prática, é claro, a varredura de índice clusterizado "parcial" não é parcial e precisa percorrer 20 milhões de linhas inteiras, em vez das 100 mil presumidas ao comparar os planos.

Aumentar o valor de TOP(ou removê-lo totalmente) eventualmente encontra um ponto de inflexão em que o número de linhas que estima que a varredura de CI precisará cobrir torna esse plano mais caro e reverte para o plano de interseção de índice. Para mim, o ponto de corte entre os dois planos é TOP (89)vs.TOP (90)

Para você, pode ser diferente, pois depende da largura do índice clusterizado.

Removendo TOPe forçando a varredura do CI

SELECT *
FROM   animal WITH (INDEX = 1)
WHERE  colour LIKE 'black%'
       AND species LIKE 'swan' 

É calculado 88.0586em minha máquina para minha tabela de exemplo.

Se o SQL Server soubesse que o zoológico não tinha cisnes negros e que precisaria fazer uma varredura completa em vez de apenas ler 100.000 linhas, esse plano não seria escolhido.

Eu tentei estatísticas de várias colunas animal(species,colour)e animal(colour,species)estatísticas filtradas, animal (colour) where species = 'swan'mas nenhuma delas ajudou a convencê-lo de que cisnes negros não existem e a TOP 10varredura precisará processar mais de 100.000 linhas.

Isso se deve à "suposição de inclusão", em que o SQL Server basicamente assume que, se você estiver procurando por algo, provavelmente existe.

Em 2008+, há um sinalizador de rastreamento documentado 4138 que desativa os objetivos de linha. O efeito disso é que o plano é custeado sem a suposição de que TOPpermitirá que os operadores filhos sejam encerrados antecipadamente sem ler todas as linhas correspondentes. Com esse sinalizador de rastreamento no lugar, obtenho naturalmente o plano de interseção de índice mais ideal.

SELECT TOP 10 *
FROM   animal
WHERE  colour LIKE 'black%'
       AND species LIKE 'swan'
OPTION (QUERYTRACEON 4138)       

Este plano agora custa corretamente para ler as 200 mil linhas completas em ambas as buscas de índice, mas sobrecarrega as pesquisas de chave (2 mil estimados versus 0 real. TOP 10Isso restringiria isso a um máximo de 10, mas o sinalizador de rastreamento impede que isso seja levado em consideração) . Ainda assim, o plano é significativamente mais barato do que a varredura de CI completa, portanto, é selecionado.

Claro que este plano pode não ser ideal para combinações que são comuns. Como cisnes brancos.

Um índice composto em animal (colour, species)ou idealmente animal (species, colour)permitiria que a consulta fosse muito mais eficiente para ambos os cenários.

Para fazer uso mais eficiente do índice composto, o LIKE 'swan'também precisaria ser alterado para = 'swan'.

A tabela abaixo mostra os predicados de busca e predicados residuais mostrados nos planos de execução para todas as quatro permutações.

+----------------------------------------------+-------------------+----------------------------------------------------------------+----------------------------------------------+
|                 WHERE clause                 |       Index       |                         Seek Predicate                         |              Residual Predicate              |
+----------------------------------------------+-------------------+----------------------------------------------------------------+----------------------------------------------+
| colour LIKE 'black%' AND species LIKE 'swan' | ix_colour_species | colour >= 'black' AND colour < 'blacL'                         | colour like 'black%' AND species like 'swan' |
| colour LIKE 'black%' AND species LIKE 'swan' | ix_species_colour | species >= 'swan' AND species <= 'swan'                        | colour like 'black%' AND species like 'swan' |
| colour LIKE 'black%' AND species = 'swan'    | ix_colour_species | (colour,species) >= ('black', 'swan')) AND colour < 'blacL'    | colour LIKE 'black%' AND species = 'swan'    |
| colour LIKE 'black%' AND species = 'swan'    | ix_species_colour | species = 'swan' AND (colour >= 'black' and colour <  'blacL') | colour like 'black%'                         |
+----------------------------------------------+-------------------+----------------------------------------------------------------+----------------------------------------------+

Achando isso intrigante, fiz algumas pesquisas e me deparei com este Q/A Como (e por que) o TOP impacta um plano de execução?

Basicamente, usar o TOP altera o custo das operadoras que o seguem (de maneira não trivial), o que faz com que o plano geral também mude (seria ótimo se você incluísse ExecPlans com e sem TOP 10), o que altera bastante a execução geral do A pergunta.

Espero que isto ajude.

Por exemplo, eu tentei em um banco de dados e: -quando nenhum top é invocado, o paralelismo é usado -com TOP, o paralelismo não é usado

Portanto, novamente, mostrar seus planos de execução forneceria mais informações.

Tenha um bom dia

Acredito que isso pode ser devido à natureza subjacente do MSSQL 2005 e à maneira como o otimizador de consulta decide qual plano de execução é o mais eficiente.

Se você usar uma variável SQL, ela deve 'enganar' o otimizador de consulta para usar correspondências de hash em vez de loops aninhados, o que resultará em um grau muito maior de paralelismo.

Tentar:

DECLARE @topn INT = 10
SELECT TOP (@topn) *
FROM    animal
WHERE   colour LIKE 'black%' 
AND species LIKE 'swan'