Início / dba / Perguntas / 280998
Accepted
D-K
Asked: 2020-12-07 09:17:44 +0800 CST

SQL Server - Posso remover cirurgicamente um plano de consulta em cache incorreto ou estou perseguindo a ideia errada?

  • 772

Deixe-me preparar o palco:

  • SQL Server > 2016
  • Vários bancos de dados com os mesmos esquemas
  • Os dados são semelhantes, mas não idênticos
  • Os mesmos índices existem etc., todos os sistemas, exceto um, funcionam como esperado
  • O exame do plano de execução recomenda a criação de um índice com TODAS as colunas como INCLUDE. A mesma coluna base já existe sem as inclusões.

O problema:

  • Em UM dos bancos de dados, o mecanismo de consulta não utiliza um índice existente e usa uma verificação completa da tabela
  • Executando manualmente a consulta (que usa um parâmetro) - varredura completa da tabela!
  • Executando manualmente a consulta sem um parâmetro (data fixa usada) - usa o índice.
  • Executando manualmente a consulta com um parâmetro e adicionando WITH (INDEX(update_ts_INDEX))- usa o índice. A consulta vai de 2 minutos a menos de um segundo.

Hipótese:

  • Acredito que o cache de consulta possa estar corrompido - mas não sei se isso é possível. Por comércio, sou um desenvolvedor java que passou os últimos 3 anos investigando o ajuste de desempenho do servidor SQL, etc.
  • O fato de executar manualmente a consulta sem um parâmetro - o mecanismo de consulta escolhe o índice certo - me impressiona. Ideias?

Notas:

  • Nos outros servidores, que executam o mesmo código e consultas, não consigo encontrar nenhuma evidência de que essa mesma consulta esteja demorando. Eles devem estar usando o índice.
  • Acabei de executar o comando manualmente, em outra região com um parâmetro, e ele usou o índice, então o problema está de fato em APENAS uma região - um banco de dados.

Exemplo de consulta:

DECLARE @P1 DATETIME = GETDATE() - .1;

SELECT value_1, update_ts, value_2 FROM PRODUCTION_TABLE 
WHERE (PRODUCTION_TABLE.update_ts > @P1);

Se eu remover a variável e codificar manualmente uma data, ela usará o index.

Pergunta: Posso remover cirurgicamente um plano de consulta em cache incorreto para essa consulta específica?

--- ATUALIZAR ---

O uso OPTION (RECOMPILE)faz com que o mecanismo de consulta escolha o índice correto.

sql-server query-performance

2 respostas

  1. Você pode!

    Execute isto para encontrar o ID do seu plano:

    select plan_handle, creation_time, last_execution_time, execution_count, qt.text
FROM 
   sys.dm_exec_query_stats qs
   CROSS APPLY sys.dm_exec_sql_text (qs.[sql_handle]) AS qt

    Em seguida, coloque esse ID no procedimento armazenado chamado DBCC FREEPROCCACHE

    DBCC FREEPROCCACHE (plan_handle_id_goes_here)

    Você sempre pode simplesmente liberar a coisa toda. Ele adicionará alguns segundos à primeira vez que cada consulta for executada, mas não mais do que uma redefinição de servidor (ou um IISRESEt em um servidor da Web) faria de qualquer maneira.

    É assim que você limpa tudo:

    DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL;

    Verifique aqui para mais informações https://serverfault.com/questions/91285/how-do-i-remove-a-specific-bad-plan-from-the-sql-server-query-cache

    • 2
  2. Best Answer

    Seu plano provavelmente não é corrupto, por si só. Seus dados provavelmente não gostam de ter uma distribuição uniforme de dados para esse campo, e o plano provavelmente foi criado usando um valor discrepante (ou seu código é excessivamente complexo, mas acho que não, com base em sua amostra. Se for complexo, comece com uma refatoração). Deixando de lado o Query Store, os planos são descartados quando os servidores são reiniciados, há pressões de memória etc. Em seguida, eles são recriados, geralmente com base nos parâmetros que você fornece, que podem ou não ser discrepantes.

    Você tem algumas opções com base nas circunstâncias.

    Se você tiver o Query Store, use-o. Solte o plano, execute novamente com um valor representativo e fixe-o. Como observado acima, o problema de remover um plano voltado para um valor discrepante é que há uma chance de ele voltar. Fixar um plano evita isso completamente.

    Se este for um bit de SQL executado com pouca frequência (algumas vezes por hora) e for executado por uma quantidade razoável de tempo (mais de alguns segundos, o que parece), basta recriar um novo plano a cada vez e o código é não muito complexo; as economias potenciais valem o custo. OPTION RECOMPIE

    Se, no entanto, você tiver um valor representativo geral para seu parâmetro, poderá adicionar a OPTIMIZE FOR @var = '2020-01-01'dica de consulta. Isso significa que quando um novo plano é gerado novamente, ele não usará o valor fornecido para determinar o plano, mas o valor fornecido. Tenha em mente que quando você atinge um valor discrepante (por exemplo, NULLs em algumas vezes campos NULLABLE), você não obterá o plano ideal. Esta é a rota que eu tenho seguido com mais frequência em circunstâncias semelhantes e, no seu caso, eu simplesmente codificaria uma data recente (se isso fizer sentido).

    Há outras coisas que você pode fazer, mas na minha experiência o valor delas cai drasticamente rapidamente.

    • 1

relate perguntas