Início / dba / Perguntas / 333295
Accepted
IngBond
Asked: 2023-11-20 23:16:12 +0800 CST

Por que o SQL Server prefere a varredura de índice em vez da busca?

  • 772

É assim que minha mesa está:

    CREATE TABLE [dbo].[ClosedTaskCustomFields](
    [ClosedTaskId] [uniqueidentifier] NOT NULL,
    [CustomFieldId] [uniqueidentifier] NOT NULL,
    [Value] [nvarchar](450) NULL
    ... 
 CONSTRAINT [PK_ClosedTaskCustomFields] PRIMARY KEY CLUSTERED 
(
    [ClosedTaskId] ASC,
    [CustomFieldId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

E também tenho esse índice para ingressar na tabela ClosedTask:

CREATE NONCLUSTERED INDEX [IX_ClosedTaskCustomFields_ClosedTaskId] ON [dbo].[ClosedTaskCustomFields]
(
    [ClosedTaskId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

Mas não consigo entender por que essa consulta lê toda a tabela ClosedTaskCustomFields para ingressar no ClosedTask se eu tiver índice.

      select count(1) from ClosedTaskCustomFields ctcf
      join ClosedTask c on c.id = ctcf.ClosedTaskId
      where c.State = 'Rejected'
-- Result count is 50k.

plano: https://www.brentozar.com/pastetheplan/?id=SJ9Y4xFEp insira a descrição da imagem aqui

sql-server

2 respostas

  1. Best Answer

    O SQL Server escolhe o plano que parece mais barato com base em suas estimativas. Isso significa que o plano que você tem em mente, com busca de índice em ambas as tabelas, teria um custo mais alto. Você pode testar isso sozinho usando uma FORCESEEKdica.

    Observe que um custo estimado mais alto não significa necessariamente que o plano seria executado mais lentamente do que a alternativa escolhida no seu hardware específico.

    Há muitos motivos pelos quais o plano mostrado tem um custo menor:

    1. O otimizador assume que todas as consultas começam sem dados no cache.
    2. A E/S sequencial tem um custo menor do que a E/S aleatória.
    3. Supõe-se que as buscas em uma árvore b sejam em grande parte aleatórias, com uma redução contínua quando as buscas são repetidas. Isso tenta levar em conta a possibilidade de tocar em páginas previamente trazidas para a memória do armazenamento persistente para a mesma consulta.
    4. O SQL Server não reduz custos no lado interno de uma junção de loops aninhados quando o paralelismo é usado. Isso tende a desfavorecer os planos de loops aninhados paralelos.
    5. Seu plano apresenta um bitmap . Isso é usado para rejeitar linhas que não podem ser unidas durante a verificação. É a razão pela qual a varredura atinge 1.169.875 linhas, mas retorna apenas 51.307 (número real de linhas para todas as execuções).
    6. Uma junção hash tem o menor custo de processamento por linha dos tipos de junção disponíveis. O custo extra de construção da tabela hash é compensado por esse benefício, assim como aqueles fornecidos pelo bitmap (não disponível com loops aninhados).

    Você pode descobrir que o otimizador escolhe naturalmente o plano de busca com uma MAXDOP 1dica ou um limite de custo mais alto para paralelismo em geral. Parece que você definiu o valor padrão de 5, que muitas pessoas consideram muito baixo atualmente.

    Caso contrário, você terá a maneira normal de substituir as opções do otimizador: usando uma dica como FORCESEEK. Eu não me incomodaria, a menos que a consulta seja crucial e economizar alguns milissegundos extras seja importante.

    Para obter mais informações sobre bitmaps em modo de linha, consulte meu artigo Bitmap Magic (ou… como o SQL Server usa filtros de bitmap).

    • 6

  2. O SQL-Server usou uma busca de índice ClosedTaskno Stateíndice (provavelmente porque esta coluna é usada na condição) e uma varredura de índice em ClosedTaskCustomFields.

    Se Statefosse muito seletivo, essa consulta selecionaria apenas uma pequena fração dos registros em cada tabela. Mas provavelmente não é, porque você tem apenas alguns estados. Portanto, provavelmente é mais barato apenas fazer uma varredura no índice na tabela de campos personalizados e pular o número relativamente pequeno de registros não correspondentes do que pesquisar. Uma busca de índice requer vários acessos ao disco para detalhar a árvore de índice. Portanto, não é necessariamente barato.

    As coisas eram diferentes se Statefosse muito seletivo, ou seja, se tivesse muitos estados.

    Mas isso é apenas uma suposição, pois não conheço o algoritmo utilizado pelo otimizador de consultas.

    • 3

relate perguntas