Início / dba / Perguntas / 108352
Accepted
8kb
Asked: 2015-07-29 10:03:32 +0800 CST

Por que a variável de tabela está forçando uma varredura de índice enquanto a tabela temporária usa busca e pesquisa de favoritos?

  • 772

Estou tentando entender por que usar uma variável de tabela está impedindo o otimizador de usar uma busca de índice e, em seguida, marcar uma pesquisa em vez de uma varredura de índice.

Preenchendo a tabela:

CREATE TABLE dbo.Test 
(
    RowKey INT NOT NULL PRIMARY KEY, 
    SecondColumn CHAR(1) NOT NULL DEFAULT 'x',
    ForeignKey INT NOT NULL 
) 

INSERT dbo.Test 
(
    RowKey, 
    ForeignKey
) 
SELECT TOP 1000000 
    ROW_NUMBER() OVER (ORDER BY (SELECT 0)),
    ABS(CHECKSUM(NEWID()) % 10)     
FROM sys.all_objects s1
CROSS JOIN sys.all_objects s2 

CREATE INDEX ix_Test_1 ON dbo.Test (ForeignKey)

Preencha uma variável de tabela com um único registro e tente pesquisar a chave primária e a segunda coluna pesquisando na coluna da chave estrangeira:

DECLARE @Keys TABLE (RowKey INT NOT NULL) 

INSERT @Keys (RowKey) VALUES (10)

SELECT 
    t.RowKey,
    t.SecondColumn
FROM
    dbo.Test t 
INNER JOIN 
    @Keys k
ON
    t.ForeignKey = k.RowKey

Segue abaixo o plano de execução:

insira a descrição da imagem aqui

Agora, a mesma consulta usando uma tabela temporária:

CREATE TABLE #Keys (RowKey INT NOT NULL) 

INSERT #Keys (RowKey) VALUES (10) 

SELECT 
    t.RowKey,
    t.SecondColumn
FROM
    dbo.Test t 
INNER JOIN 
    #Keys k
ON
    t.ForeignKey = k.RowKey

Este plano de consulta usa uma pesquisa de busca e marcador:

insira a descrição da imagem aqui

Por que o otimizador deseja fazer a pesquisa de favoritos com a tabela temporária, mas não com a variável da tabela?

A variável de tabela é usada neste exemplo para representar dados provenientes de um tipo de tabela definido pelo usuário em um procedimento armazenado.

Percebo que a busca de índice pode não ser apropriada se o valor da chave estrangeira ocorrer centenas de milhares de vezes. Nesse caso, uma varredura provavelmente seria uma escolha melhor. Para o cenário que criei, não havia nenhuma linha com valor 10. Ainda acho o comportamento interessante e gostaria de saber se há uma razão para isso.

SQL Fiddle

Adicionar OPTION (RECOMPILE)não alterou o comportamento. O UDDT tem uma chave primária.

@@VERSIONé SQL Server 2008 R2 (SP2) - 10.50.4042.0 (X64) (Build 7601: Service Pack 1) (Hipervisor)

sql-server sql-server-2008-r2

2 respostas

  1. Best Answer

    O motivo do comportamento é que o SQL Server não pode determinar quantas linhas corresponderão a ForeignKey, pois não há índice com RowKey como a coluna inicial (ele pode deduzir isso das estatísticas na tabela #temp, mas essas não existem para variáveis ​​de tabela/UDTTs), portanto, faz uma estimativa de 100.000 linhas, o que é melhor tratado com uma varredura do que com uma pesquisa+pesquisa. Quando o SQL Server percebe que há apenas uma linha, já é tarde demais.

    Você pode construir seu UDTT de forma diferente; em versões mais modernas do SQL Server, você pode criar índices secundários em variáveis ​​de tabela, mas essa sintaxe não está disponível no 2008 R2.

    BTW, você pode obter o comportamento de busca (pelo menos em meus testes limitados) se tentar evitar o bitmap/sonda sugerindo uma junção de loops aninhados:

    DECLARE @Keys TABLE (RowKey INT PRIMARY KEY); -- can't hurt

INSERT @Keys (RowKey) VALUES (10);

SELECT 
     t.RowKey
    ,t.SecondColumn
FROM
    dbo.Test t 
INNER JOIN 
    @Keys k
ON
    t.ForeignKey = k.RowKey
    OPTION (LOOP JOIN);

    Aprendi esse truque com Paul White há vários anos. Obviamente, você deve ter cuidado ao colocar qualquer tipo de dica de junção no código de produção - isso pode falhar se as pessoas fizerem alterações nos objetos subjacentes e esse tipo específico de junção não for mais possível ou não for mais ideal.

    Para consultas mais complexas e quando você muda para o SQL Server 2012 ou superior, é possível que o sinalizador de rastreamento 2453 possa ajudar. Esse sinalizador não ajudou com essa junção simples, no entanto. E as mesmas isenções de responsabilidade se aplicam - isso é apenas uma coisa alternativa que você geralmente não deve fazer sem uma tonelada de documentação e procedimentos rigorosos de teste de regressão em vigor.

    Além disso, o Service Pack 1 está sem suporte há muito tempo, você deve obter o Service Pack 3 + MS15-058 .

    • 15

  2. Variáveis ​​de tabela e tabelas temporárias são tratadas de várias maneiras diferentes. Há uma ótima resposta aqui com muitos detalhes sobre onde eles são diferentes.

    Especificamente no seu caso, acho que o fato de as tabelas temporárias poderem ter estatísticas adicionais geradas e planos paralelos, enquanto as variáveis ​​de tabela têm estatísticas mais limitadas (sem estatísticas no nível da coluna) e nenhum plano paralelo é o seu culpado.

    Você pode muito bem despejar a variável da tabela em uma tabela temporária durante o procedimento armazenado.

    • 3

relate perguntas