Início / dba / Perguntas / 198313
Accepted
Paul Williams
Asked: 2018-02-20 14:55:53 +0800 CST

Join on NULL Key Column Optimization como Table and Index Scans

  • 772

Eu tenho uma pergunta sobre este plano de consulta .

Temos uma tabela em um ambiente de teste, Order_Details_Taxes, que possui 11.225.799 linhas. Esta tabela tem uma coluna, OrdTax_PLTax_LoadDtl_Key, que é NULL em cada linha. Este ambiente de teste está configurado de forma que esta coluna seja sempre NULL. Há um índice nesta coluna.

Executei algumas consultas nessa tabela usando um valor NULL para uma coluna. Um NULL INNER JOIN nunca produzirá nenhum resultado.

declare @Keys table (KeyValue decimal(15,0))
insert into @Keys (KeyValue) values (null)

select OrdTax_PLTax_LoadDtl_Key
from @Keys
inner join Order_Details_Taxes
    on OrdTax_PLTax_LoadDtl_Key = KeyValue

select *
from @Keys
inner join Order_Details_Taxes
    on OrdTax_PLTax_LoadDtl_Key = KeyValue

Essas são as primeiras consultas no plano de consulta. O primeiro selectcomeça na tabela de cem milhões de linhas e se junta a @Keys. O segundo selectcomeça a partir de @Keys, mas faz uma varredura de índice clusterizado nesta tabela.

Eu sei que @Tables temporárias são questionáveis ​​na maioria dos casos, então mudei minha consulta para usar uma #Table temporária:

if object_id ('tempdb..#Keys') is not null
    drop table #Keys
create table #Keys (KeyValue decimal(15,0))
insert into #Keys (KeyValue) values (null)

select OrdTax_PLTax_LoadDtl_Key
from #Keys
inner join Order_Details_Taxes
    on OrdTax_PLTax_LoadDtl_Key = KeyValue

select *
from #Keys
inner join Order_Details_Taxes
    on OrdTax_PLTax_LoadDtl_Key = null

Essas consultas foram otimizadas e executadas exatamente como eu esperava - obtenha o valor #Keys NULL primeiro e procure por Order_Details_Taxes. São as últimas consultas no plano de consulta vinculadas.

Por que as consultas nas quais usei uma variável @Table executam varreduras de índice e tabela nessa tabela grande, quando estou unindo usando de uma tabela que tem um único valor NULL para uma tabela com apenas NULLs nesse valor de chave?

Suponho que a resposta seja limitações estatísticas e/ou de cardinalidade das variáveis ​​@Table, mas o plano de consulta resultante não foi intuitivo para mim.

ANSI_NULLsestá ativado para esta tabela e minha sessão SQL.

sql-server sql-server-2014

1 respostas

  1. Best Answer

    O comportamento que você está vendo é causado pela falta de estatísticas na variável da tabela. Quando quero saber mais sobre por que o otimizador de consultas escolheu um plano específico, às vezes adiciono dicas e comparo as consultas lado a lado. Essa abordagem é útil aqui.

    Primeiro, criarei uma tabela com estrutura próxima o suficiente da sua para ver o mesmo comportamento:

    CREATE TABLE dbo.Order_Details_Taxes (
    OrdTax_PLTax_LoadDtl_Key decimal(15,0),
    FILLER VARCHAR(30)
);

INSERT INTO dbo.Order_Details_Taxes WITH (TABLOCK)
SELECT NULL, REPLICATE('Z', 30)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

CREATE INDEX [IX_OrdTax_PLTax_LoadDtl_Key] ON Order_Details_Taxes (OrdTax_PLTax_LoadDtl_Key);

    Para ver como o otimizador de consulta custa os diferentes tipos de junção, posso obter um plano estimado para o seguinte:

    declare @Keys table (KeyValue decimal(15,0))
insert into @Keys (KeyValue) values (null)

select OrdTax_PLTax_LoadDtl_Key
from @Keys
inner join Order_Details_Taxes
    on OrdTax_PLTax_LoadDtl_Key = KeyValue;

select OrdTax_PLTax_LoadDtl_Key
from @Keys
inner join Order_Details_Taxes
    on OrdTax_PLTax_LoadDtl_Key = KeyValue
OPTION (LOOP JOIN, MAXDOP 1);

select OrdTax_PLTax_LoadDtl_Key
from @Keys
inner join Order_Details_Taxes
    on OrdTax_PLTax_LoadDtl_Key = KeyValue
OPTION (HASH JOIN, MAXDOP 1);

    Aqui está uma captura de tela dos planos estimados:

    planos de variáveis ​​de mesa

    O SQL Server não sabe nada sobre o valor da linha na variável de tabela, então cria o plano de loop aninhado usando a densidade das estatísticas em OrdTax_PLTax_LoadDtl_Key. Todas as linhas têm o mesmo valor nas estatísticas, portanto, a densidade é 1. Uma das suposições gerais dos modelos do otimizador de consulta é que os dados existem se o usuário final estiver procurando por eles. Portanto, espera-se que sua busca de índice retorne o mesmo número de linhas que a varredura e tenha o mesmo custo, apesar do histograma conter apenas NULLs. Nesse caso, o otimizador não volta e aplica conhecimento especial sobre NULLs para alterar o plano. Você poderia argumentar que o otimizador poderia ser melhorado para fazer isso, mas isso parece um cenário incomum.

    A diferença de custos dos planos acaba por se resumir aos custos das próprias operadoras de adesão. Por qualquer motivo, o otimizador de consulta custa a junção de loop mais alta do que a junção de mesclagem. A junção de hash também tem um custo alto, mas para isso o SQL Server espera precisar calcular milhões de hashes para que o custo mais alto seja mais compreensível.

    O que acontece se você obtiver o mesmo plano com uma tabela temporária que não possui estatísticas? A maneira correta de fazer isso é desabilitar a criação automática de estatísticas para a tabela, mas vou usar um atalho:

    if object_id ('tempdb..#Keys') is not null
    drop table #Keys
create table #Keys (KeyValue decimal(15,0))
CREATE STATISTICS s1 on #Keys (KeyValue) WITH NORECOMPUTE;
insert into #Keys (KeyValue) values (null)

    Tudo parece igual ao plano de variável da tabela:

    tabela temporária sem estatísticas

    É por isso que eu disse que o comportamento é causado pela falta de estatísticas. Quando você usa uma tabela temporária e permite a criação de estatísticas automáticas, o otimizador tem um histograma na coluna da tabela temporária. Ele pode usar essas informações para gerar estimativas de cardinalidade mais precisas para o plano de junção de loop aninhado e a busca de índice:

    estatísticas da tabela temporária

    O histograma sugere que nenhuma coluna será correspondida, então você acaba com a estimativa de cardinalidade mínima de 1 linha fora da busca. Os custos da junção de loop e da busca são reduzidos de acordo, e o plano de junção de loop aninhado tem de longe o menor custo dos três tipos de junção.

    Ter alguns valores NULL na tabela externa de uma junção é um cenário significativamente mais comum do que ingressar em uma tabela com todos os NULLs. Em outras palavras, eu esperaria mais suporte de modelo melhor para comparar dois histogramas que contêm NULL em comparação com um histograma para apenas NULLs em comparação com um valor desconhecido. Com um melhor suporte de modelo, você pode obter melhores estimativas de cardinalidade e, nesse caso, as melhores estimativas de cardinalidade resultam em um plano de consulta significativamente mais eficiente.

    • 6

relate perguntas