Como controlar o desempenho de consultas que usam dicas RECOMPILE

Como controlar o desempenho de consultas que usam dicas RECOMPILE


Digamos que tenhamos um procedimento armazenado com duas consultas – a segunda consulta usa uma dica de recompilação e você pode reconhecê-la na minha sessão de detecção de parâmetros:

A primeira consulta sempre terá o mesmo plano, mas a segunda consulta obterá planos diferentes e retornará números diferentes de linhas, dependendo da reputação em que passamos.

Vou fazer algumas configurações, liberar o cache do plano e executar o proc:

Quando o executo para @Reputation = 2, a segunda consulta retorna 9.149 linhas.

Como uma consulta recompilada aparece no cache do plano

Se eu verificar sys.dm_exec_query_stats agora, existem algumas colunas com resultados interessantes – vou movê-las um pouco para que você possa vê-las:

Leia Também  Aprenda SQL: práticas de consulta SQL

Como controlar o desempenho de consultas que usam dicas RECOMPILE 1

Linha 1 é a primeira consulta no procedimento armazenado. É um COUNT (*) e retorna apenas 1 linha.

A linha 2 é a segunda consulta e retorna 9.149 linhas para reputação = 2.

Portanto, agora, se estou inclinado a fazer contas, posso adicionar o número total de linhas para ambas as instruções e ver o número total de linhas retornadas pela consulta. Pode parecer estranho usar linhas como uma medida agora, mas as colunas do sys.dm_exec_query_stats – CPU, leituras, gravações, duração etc. – se comportam da mesma maneira que vou mostrar aqui, e os números das linhas são mais repetível do que alguns dos outros, então vamos usar linhas.

Se eu executar o procedimento armazenado duas vezes – para dois parâmetros diferentes – e depois verificar as métricas, as coisas parecerão diferentes:

Os resultados:

Como controlar o desempenho de consultas que usam dicas RECOMPILE 2

O plano da primeira consulta ficou na memória, agora mostra 2 execuções e 2 linhas totais retornadas. Suas métricas de linha estão corretas ao longo da vida útil do tempo em cache do procedimento armazenado.

No entanto, a segunda consulta – aquela com a dica de recompilação – possui um novo plano no cache, mas também novas métricas. Você não está apenas recompilando o plano de execução, mas também não está obtendo métricas do plano de consulta aqui. (Tudo bem, e essa parte da qual eu também estava ciente.)

Mas a parte que continuo esquecendo é que, quando estou analisando os totais do procedimento armazenado em sp_BlitzCache, os valores total, mínimo e máximo são inúteis:

Como controlar o desempenho de consultas que usam dicas RECOMPILE 3

No sp_BlitzCache, adicionamos os totais de cada instrução em um proc e os apresentamos como os números totais do proc. O problema é que elas não são verdadeiras sempre que há uma dica de recompilação: os totais são subnotificados e os valores médios / min / máx apenas refletem a última execução de qualquer consulta com uma dica de recompilação.

Leia Também  Uma visão geral dos tipos de gráfico no Power BI

O cache do plano não é adequado para rastrear consultas com dicas RECOMPILE, mas o Query Store é.

Como uma consulta recompilada aparece no Query Store

Vou ativar o Query Store no banco de dados Stack Overflow e depois executar as duas consultas novamente:

E, em seguida, consulte o Query Store:

Como controlar o desempenho de consultas que usam dicas RECOMPILE 4

Tenho uma imagem muito melhor do número de vezes que as consultas recompiladas foram executadas E do número de linhas que eles retornaram a cada vez. (E, claro, você também obtém outras métricas de ajuste de consulta muito mais úteis, como CPU, leituras, duração etc.)

Quando você decide usar as dicas RECOMPILE,
você provavelmente deseja ativar o Query Store.

O Query Store foi enviado pela primeira vez no SQL Server 2016 e cerca de metade de todos os servidores de produção tem mais de 2016. Hoje, em 2020, quando seus esforços de ajuste de consulta exigirem que você insira dicas RECOMPILE, provavelmente você deve parar por um momento para considerar o Query Store também. Isso facilitará a solução de problemas de desempenho.

O Query Store exige um pouco mais de planejamento do que apenas colocar uma dica RECOMPILE. Aqui estão as coisas que você deseja pensar:

Essas coisas são mais funcionais? Sim, absolutamente, e a combinação de dicas RECOMPILE e a ativação do log do Query Store é terá impacto no desempenho do seu servidor. Você não deseja ativar essas opções, a menos que esteja convencido de que é a melhor maneira de resolver um problema de detecção de parâmetros. Também existem outras soluções – como uma atualização rápida, se você não estiver familiarizado com nenhuma das outras maneiras, confira meu vídeo do SQLDay Poland:

cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br