Recentemente, lidei com um procedimento armazenado problemático. Às vezes, corre lindamente rápido, outras vezes, corre muito, muito tempo. Eu determinei que a detecção de parâmetros ruins era a causa.
Apenas para informação - Os parâmetros no proc são datetime e a consulta usa esses parâmetros para pesquisar intervalos de datas.
De qualquer forma, foi isso que tentei:
- Recriou o proc e usou
WITH RECOMPILE- Não ajudou - Recriou o proc e adicionou
OPTION (RECOMPILE)- Não ajudou - Recriou o proc e adicionou
OPTION (OPTIMIZE FOR UNKNOWN)- Executa rápido - Recriou o proc e usou variáveis locais - Executa rápido
Para ajudar no meu entendimento... Está usando variáveis locais e OPTIMIZE FOR UNKNOWNexatamente a mesma coisa da maneira que usa dados estatísticos de densidade média para produzir um plano?
Eu tentei algumas combinações de coisas também:
- Recriou o proc e adicionou
OPTIMIZE FOR UNKNOWN&OPTION (RECOMPILE)- Executa rápido - Recriado o proc com variáveis locais &
OPTION (RECOMPILE)- Executa lento
Eu li sobre os perigos potenciais do uso OPTIMIZE FOR UNKNOWNe, em muitos casos, o uso de variáveis locais é apresentado como se fosse a mesma coisa. Isso é o que me leva a pensar que é a mesma coisa.
MAS - Como explico que a tentativa 6 corre devagar.
Quero dizer que sim, as estatísticas são atualizadas, mas com uma taxa de amostragem menor que zero% - as tabelas são ENORMES +- 1,6 bilhão de linhas.
Também pode ser digno de nota - eu usei o awesome sp_blitzcachee filtrei no proc específico - Há um aviso de tempo limite de compilação para ele - Minha intuição está me dizendo que é algo a ser observado aqui.
Ambos OPTIMIZE FOR UNKNOWN e variáveis locais (mais sobre isso em um segundo) usam médias das estatísticas para determinar contagens de linha. A detecção de parâmetros está indo atrás de valores específicos para obter contagens de linha específicas, resultando em planos diferentes como você está vendo. RECOMPILE está apenas forçando-o a ir atrás de um valor específico diferente. Em alguns casos, esta é a resposta certa. OTIMIZAR PARA um valor também é específico.
Tudo isso é muito motivado pelas circunstâncias. Às vezes, um plano genérico baseado em médias será melhor. Outras vezes, um plano específico será melhor (OPTIMIZE FOR ). Ainda outras vezes, você não pode e não quer saber o que funciona melhor, então RECOMPILE se torna seu amigo (embora introduza outras dores).
Agora, prometi um pouco mais sobre variáveis locais. Uma coisa a saber é que, na compilação, o valor não será conhecido, portanto, assim como OPTIMIZE FOR UNKNOWN, você obterá médias. No entanto, uma recompilação em nível de instrução realmente saberá qual é o valor da variável local e você poderá ver planos ruins gerados se estiver na situação em que precisa desse valor médio. Então, geralmente, eu não aconselharia o uso da variável local e, em vez disso, ficaria com OPTIMIZE FOR UNKNOWN.
Além disso, o Query Store e o Plan Forcing são uma ótima maneira de lidar com a detecção incorreta de parâmetros sem mexer no código. Apenas dizendo.