Início / dba / Perguntas / 152209
Accepted
Chris Woods
Asked: 2016-10-14 08:24:53 +0800 CST

Consulta com variável não estimando corretamente

  • 772

Eu tenho uma consulta com vários joins que não está funcionando bem. Depois de muita triagem, resolvi desmontar a consulta e olhar apenas a tabela de condução. Abaixo está um exemplo da consulta baseada apenas na tabela de direção.

DECLARE @VAR AS INT

SET @VAR = 11652862

SELECT Field1, Field2
FROM tablea
WHERE Field2 = @VAR

Quando executo o código, o plano de execução real retorna com 15 mil linhas estimadas e 1 milhão de linhas reais. Sim, isso é apenas um simples SELECT...FROM TABLE, mas lembre-se de que isso é apenas parte de uma consulta maior, portanto, essa diferença de estimativa aumenta muito à medida que adicionamos mais junções.

Eu sei que o problema é @VARporque o otimizador não sabe o valor da variável quando compila a consulta em um plano, então está voltando com o número médio de linhas para a estimativa do campo. Se eu adicionar OPTION (RECOMPILE), forçará o valor quando compilar e retornará com o número correto de linhas.

Aqui está o kicker: Este não é um código personalizado que minha empresa pode editar. Faz parte de um aplicativo de terceiros que não consigo editar. A atualização das estatísticas não ajudará (olhei o histograma e ele está listando corretamente o valor da minha variável). Já tenho um índice NC de cobertura nesses dois campos. A única opção que consigo pensar é usar um plano de execução forçada nessa consulta. Eu nunca fui capaz de fazer isso com sucesso, no entanto. Alguém tem alguma outra ideia ou uma boa referência de instruções sobre planos de execução forçada?

Como foi perguntado nos comentários, a instrução create para o índice é:

CREATE NONCLUSTERED INDEX idx ON tablea (Field2)
sql-server performance

1 respostas

  1. Best Answer

    Tenho outras ideias além de um guia de plano e vou publicá-las aqui para sua consideração, mas não posso recomendar sua implementação. Existem pelo menos duas maneiras diferentes de aumentar as estimativas de linha da tabela. A primeira envolve a definição manual de estatísticas e a segunda envolve o redirecionamento do aplicativo de terceiros para usar uma exibição que você definiu em vez de acessar a tabela diretamente.

    Ambos CREATE STATISTICS e UPDATE STATISTICS têm uma opção STATS_STREAM. Aqui está o que o Books Online diz sobre isso:

    Identificado apenas para fins de informação. Não suportado. Não é garantido futura compatibilidade.

    É completamente compreensível que isso o impeça de usá-lo, mas o que permite fazer é transplantar as estatísticas de um objeto para outro. Suponha que você queira aumentar as estimativas de cardinalidade de consultas que usam tablea em 50X. Você pode pegar os dados da tablea, duplicá-los 50 vezes, coletar estatísticas com FULLSCAN e atualizar as estatísticas na tablea usando o valor STATS_STREAM da outra tabela junto com a opção NORECOMPUTE. Você provavelmente deseja alterar o valor ROWCOUNT também. Isso deve ter um efeito muito grande em todas as consultas que fazem referência à tabela. Você pode ter problemas com outras consultas, com a alteração dos dados subjacentes na tabela, com estatísticas sobre índices que não são atualizadas e assim por diante. Esta não é uma boa opção.

    Dependendo de como o aplicativo de terceiros se conecta ao SQL Server, você pode criar uma exibição com um esquema diferente do tablea e direcionar essa parte do aplicativo para usar na exibição. A exibição deve ter todas as mesmas colunas que tablea, mas deve ter código adicional que aumenta o número de linhas retornadas. Não tentei muito fazer isso funcionar porque parece muito pouco prático, mas essa abordagem foi inspirada no artigo de Adam Machanic sobre forçar planos paralelos :

    SELECT ta.*
FROM tablea ta
CROSS JOIN (
    SELECT t.t
    FROM
    ( 
    VALUES 
    (2), (1), (1), (1), (1), (1), (1), (1), (1), (1), 
    (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), 
    -- lots more values here ...
    (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)
   ) t(t)
WHERE t % 2 = 0
) t
OPTION (QUERYTRACEON 8690);

    Não fiquei muito satisfeito com essa consulta, mas não gastei muito tempo nela. Nessa forma, ele faz uma única varredura da tabela, mas o otimizador de consulta superestima a estimativa de cardinalidade em 100 vezes. O TF 8690 existe para evitar um spool de tabela sem sentido, mas isso provavelmente não é apropriado em uma exibição ou em uma consulta maior. Se o mesmo aplicativo inserir dados nessa tabela, você precisará lidar com isso de alguma forma. Esta não é uma boa opção.

    Para reiterar, não acho que você deva tentar nenhuma dessas opções. Seria muito melhor conversar com o fornecedor ou, se isso não funcionar, engolir e tentar novamente com um guia de plano. Não tenho experiência com isso, então não posso ajudar.

    • 1

relate perguntas