Início / dba / Perguntas / 345334
Accepted
Paul White
Asked: 2025-02-18 16:34:13 +0800 CST

Classificação desnecessária com TOP PERCENT?

  • 772

Configurar

-- Create a heap table of numbers from 1 to 100
SELECT TOP (100)
    i = IDENTITY(int, 1, 1)
INTO #T
FROM master.dbo.spt_values;

-- Add a clustered primary key
ALTER TABLE #T
    ADD PRIMARY KEY CLUSTERED (i);

Consulta de teste

Exibir 9% das linhas na ordem da chave primária:

SELECT TOP (9e) PERCENT 
    i 
FROM #T 
ORDER BY 
    i ASC;

DROP TABLE #T;

db<>demonstração de violino

Resultados

Resultados do SSMS

Plano de execução

Plano de execução do SSMS

Pergunta

Por que o SQL Server classifica a coluna quando o índice clusterizado fornece exatamente essa ordem?

Mais linhas

Se eu aumentar o número de linhas na tabela, obtenho um Eager Spool em vez de uma Sort e o índice é escaneado em ordem:

-- Create a heap table of numbers from 1 to 1,000
SELECT TOP (1000)
    i = IDENTITY(int, 1, 1)
INTO #T
FROM master.dbo.spt_values;

-- Add a clustered primary key
ALTER TABLE #T
    ADD PRIMARY KEY CLUSTERED (i);

-- 0.9% now
SELECT TOP (9e-1) PERCENT 
    i 
FROM #T 
ORDER BY 
    i ASC;

DROP TABLE #T;

plano com 1.000 linhas

sql-server

2 respostas

  1. Best Answer

    a resposta do e-core

    Eventualmente, serei Dunked On™️ pelo cavalheiro da Nova Zelândia (ou talvez um cavalheiro de Rugby), mas não deixarei que isso me impeça de girar na velocidade máxima do e-core enquanto os p-cores estão ocupados fazendo torradas e chá.

    classificar v. spool

    Sorts e Eager Table Spools atuam como operadores de bloqueio para receber todas as linhas da varredura. O trabalho deles é contar as linhas.

    • Com a classificação, não importa a ordem em que as linhas chegam, mas todas elas precisam chegar antes que a classificação comece a colocá-las na ordem solicitada.

    • Com o Eager Spool, é apenas um Table Spool, e não um Index Spool, então as linhas precisam chegar em ordem. Daí o Ordered Scan neste caso. O Spool pode preservar a ordenação, mas não fornece o mecanismo de ordenação.

    O trabalho deles continua o mesmo, no entanto! Contar todas as linhas que aparecem para responder a uma pergunta importante que o Top está fazendo.

    principal

    O Top precisa saber o que é 9%. Ele não sabe disso até que o Sort ou Spool obtenha todas as linhas do Scan.

    A função do Top é definir uma Meta de Linha de 9% das linhas que chegam ao Sort ou ao Spool, solicitando uma linha por vez até que essa Meta de Linha seja atingida.

    Para fazer isso, ele faz algumas contas©️.

    Este post foi escrito originalmente em Crayola Crayons

    • 16

  2. A chave para entender por que a Classificação ou Spool é necessária é pensar em como o plano funciona.

    Estratégia

    O operador Top precisa limitar as linhas retornadas a alguma porcentagem do resultado total potencial. Para saber quando parar, ele precisa saber a contagem total de linhas para calcular quantas linhas essa porcentagem representa.

    Neste exemplo trivial, essa informação de contagem de linhas poderia vir da cardinalidade conhecida da tabela de origem. Em casos mais complexos com junções, filtros e agregação, não haveria alternativa a contar fisicamente as linhas no ponto certo, ou seja, logo antes do Top .

    Para garantir que contamos todas as linhas, precisamos de um operador de bloqueio. Os dois candidatos que o SQL Server usa são um Sort ou um Spool .

    Custeio

    Quando o TOP PERCENTé associado a uma ORDER BYcláusula, o otimizador considera o Sort primeiro porque a entrada ordenada é sempre um requisito. Esse sort pode ser eliminado mais tarde se um operador inferior naturalmente fornecer linhas ordenadas, por exemplo, como resultado de uma busca ou varredura ordenada.

    Se a opção Sort aparecer em um plano completo que seja barato o suficiente, o otimizador não vai se incomodar em tentar encontrar algo mais barato. Caso contrário, ele também vai gerar uma alternativa Spool e custar isso.

    Para entradas muito pequenas, o modelo de custo produz um custo menor para um Sort do que para um Spool . Isso significa que, mesmo que o otimizador custe ambas as alternativas, ele ainda pode escolher o sort.

    Estamos falando de diferenças muito pequenas aqui, então não importa muito. Ainda assim, o modelo de custo assume um custo inicial ligeiramente maior para um Spool do que para um Sort , enquanto o Sort tem um custo por linha maior.

    Encomenda

    Se a classificação for escolhida como a opção mais barata para obter uma contagem total de linhas, não há necessidade específica de solicitar a ordenação de sua subárvore de entrada — resultados corretos serão retornados em ambos os casos.

    Assim, o index scan tem a Ordered:Falsepropriedade, que deixa para o Storage Engine decidir como buscar linhas. Neste exemplo, isso quase certamente significa que as linhas serão de fato retornadas em ordem de índice clusterizado porque a tabela é muito pequena para se qualificar para um Allocation Ordered Scan .

    A opção Spool , por outro lado, não pode classificar linhas, então sua árvore de entrada é necessária para produzir linhas classificadas, que o spool então preserva. O otimizador decide que a maneira mais barata de obter dados ordenados é solicitar ao Storage Engine uma varredura de índice clusterizado ordenado por meio da Ordered:Truepropriedade.

    A necessidade de contar linhas e apresentá-las corretamente ordenadas explica por que o plano contém uma Classificação ou um Spool .

    Internos

    Quando Open()é chamado no operador Top, ele abre sua subárvore. O Sort ou Spool consome toda a sua entrada durante a Openfase. No momento em que a execução retorna ao operador Top (ainda em sua fase de abertura), o Sort ou Spool está totalmente preenchido. O acesso à tabela foi encerrado (incluindo sua Close()chamada.

    A próxima coisa que o Top faz é redefinir . Ele pode fazer isso várias vezes para um Segment Top, mas neste caso, acontece apenas uma vez no início. É aqui que o Top transforma a porcentagem especificada em um número definido de linhas.

    Cooperação

    A única coisa notável sobre esse plano é a cooperação entre o Top e seu filho Sort ou Spool , quando o Top pergunta ao seu operador filho o número total de linhas que ele tem:

    Top perguntando ao seu filho Classificar por estatísticas
    Top perguntando ao seu filho Classificar por estatísticas

    Top pedindo estatísticas ao seu filho Spool
    Top pedindo estatísticas ao seu filho Spool

    Após transformar a porcentagem solicitada em um número definido de linhas (arredondando para cima se o resultado tiver um componente fracionário), o Top continua o processamento normalmente em sua GetRow()fase, solicitando uma linha por vez do Sort ou Spool até que o número alvo de linhas seja visto no Top .

    Para completar, observe que o Top também retorna uma linha por vez. Essas linhas de resultado são empacotadas em buffers de saída prontos para transmissão ao cliente.

    Finalmente, é interessante que se o otimizador considerar a opção Spool , ele gere essa alternativa usando uma regra de exploração chamada EnforceHPandAccCard. Não há necessidade de Halloween Protection (HP) neste plano, mas precisamos Acc ess (ou Acc umular) Card inality.

    • 12

relate perguntas