a versão DBA de um clássico XKCD.

quando você identificar que o cheiro de parâmetro é um problema, você precisa testar se a implementação de dicas de ‘recompile’ irá ajudar a qualidade do plano. (Não sei qual é o parâmetro que cheira? Aprenda com este post ou este vídeo gratuito de 30 minutos.) Você deve decidir: que dica ou comando você usa, e onde você coloca? Que compromissos você está fazendo quando se trata de ser capaz de sintonizar o seu servidor SQL no futuro?

Esta publicação corre através de opções comuns que você tem que empurrar (ou bater) servidor SQL para gerar um novo plano de execução. Vou dar alguns prós e contras para cada método e explicar o que é útil e o que vale a pena evitar.

para manter as coisas relativamente simples, estou apenas discutindo como isso se aplica aos procedimentos armazenados hoje-este post não cobre outras formas de consultas parametrizadas (ou não parametrizadas). Se você tem grandes perguntas nessas áreas, sinta-se livre para sugerir isso para um post futuro nos comentários.aviso: Dicas de Recompilamento podem acabar com o seu desempenho acendendo o seu CPUs quando usado incorretamente. Trata com cuidado!

Útil Exibições de Gerenciamento Dinâmico

Quando eu falar sobre o impacto na execução do plano de cache, vou me referir a dois DMVs:

• sys.dm_exec_query_stats – Essa DMV é útil para ver os principais instruções em SQL Server, independentemente se eles são parte de um procedimento ou não. Veja aqui uma pesquisa de amostra.
• sys.dm_exec_procedure_stat – este DMV só está disponível no servidor SQL 2008 e superior. Ele rastreia as métricas de execução para procedimentos armazenados. Confira uma pesquisa de exemplo em livros Online.

para ambos os DMVs, ter um plano de execução no cache Está ligado a ser capaz de ver métricas de execução: número de execuções totais, CPU total e média, leituras lógicas, etc. Quando um plano de execução é removido do cache devido à recompilação, pressão de memória, reinicialização ou outras ações, as métricas de execução relacionadas são removidas também.

executando um procedimento com uma dica de recompilação em tempo de execução

uma opção que eu adoro para testes rápidos e fáceis é a capacidade de chamar um procedimento armazenado com uma dica de recompilação no tempo de execução. Isso é ótimo porque você não precisa mudar nenhum código compilado. A sua sugestão também se aplica ao que está a fazer em tempo de execução.

para fazer isto, basta usar uma sintaxe como esta:

However, there’s a little bit of a gotcha. If you have nested stored procedures, the recompile hint only applies to code in the outermost procedure. Você ainda pode obter parâmetro farejando qualquer chamada para sub-procedimentos dentro do procedimento armazenado em si.

assim, enquanto eu gosto disso às vezes para testes, se você tem algum ninho em seus procedimentos armazenados, isso pode não ajudá-lo a obter um plano de execução verdadeiramente novo.

usando o procedimento de armazenamento do sistema sp_recompile

isto às vezes é útil se você precisar intervir durante um incidente de produção se você tiver um plano ruim sendo usado repetidamente por sessões de entrada. You can force recompilation of plans by running sp_recompile against an object– most common choices are a table or a stored procedure. The command looks like this (a table in this example):

quando esta é executada, os planos relacionados não são imediatamente removidos da ‘cache’ do plano de execução do servidor SQL. Em vez disso, a magia acontece da próxima vez que consultas referenciarem o objeto recompiled executado. Nesse ponto, as estatísticas de execução existentes no sys. dm_exec_query_stat serão reinicializadas para as demonstrações do plano.

contagens de execução no sys.dm_ exec_ procedure_stat não será necessariamente reposto na próxima utilização se marcar uma tabela usada por um procedimento armazenado para recompilação. Mas se você marcar o procedimento armazenado para recompilação, ele será reiniciado no próximo uso. (Não se incomode em tentar memorizar isso, apenas leia o próximo parágrafo.)

Há grandes desvantagens com este. Este comando necessita de alta Permissão — o utilizador que o executa necessita de permissões ‘alter’ na tabela. Também requer um alto nível de bloqueio para completar. Em sistemas ocupados, vi este comando participar em algumas correntes de bloqueio desagradáveis. Cuidado com isso!

embora isso possa ocasionalmente ser útil para solução de problemas e testes, não faça parte do código de produção.

recompilar dicas nos cabeçalhos de procedimentos armazenados

quando tiver procedimentos que deseja gerar novos planos, você começa a procurar como usar dicas dentro do procedimento. A opção que a maioria das pessoas descobre primeiro é usar ‘com RECOMPILE’ no cabeçalho do procedimento armazenado, como este:

Usando RECOMPILAR o procedimento armazenado cabeçalho é bastante drástica — o procedimento não cache de um plano de execução quando ele é executado. Isso significa que:

• Sem a execução de planos em cache para rever
• Sem a execução estatísticas registradas em sys.dm_exec_query_stats
• Sem a execução estatísticas registradas em sys.dm_exec_procedure_stats

Uau, isso é uma chatice. Se você precisa identificar o impacto que este procedimento está tendo em seu servidor, você tem que executar algum tipo de rastreamento ou sessão de eventos estendidos e colher e interpretar os resultados. Isso não é rápido, e correr vestígios pode ter impacto no desempenho.tipo um grande negativo. Por essa razão, não gosto muito desta escolha.

recompilar dicas sobre declarações individuais

Esta opção é um pouco mais trabalhosa, mas tem um retorno muito melhor ao longo do tempo. With this option you take the RECOMPILE hint and apply it only to statements in the stored procedure where you’d like to implement the recompile, like this:

mesmo que tenha de usar a sugestão em todas as afirmações do procedimento e não no cabeçalho, isto ainda tem benefícios! Ao colocar a sugestão de recompile no nível de instrução no proc, você magicamente obter:

• Limitado planos de execução em cache para revisão (última execução)
• Limitado de execução estatísticas registradas em sys.dm_exec_query_stats. Você só vai ficar estatísticas para a última execução, mas o plan_generation_num coluna incremento de, pelo menos, dando-lhe a visão de que algo está acontecendo.
• estatísticas de execução gravadas em sys.dm_ exec_ procedure_stat

o facto de obter alguma informação nestes DMV pode ser super útil ao longo do tempo. Aplicar dicas de recompil apenas para as declarações que precisam deles também é apenas mais responsável– ele reduz o risco de queimar-se ao longo do tempo com CPU burn.

DBCC FREEPROCCACHE – a opção nuclear

isto não é estritamente uma sugestão de recompilação – mas certamente causa recompilação. O servidor SQL tem um comando que você pode usar para dizer: “Iniciar de novo com novos planos de execução”. Normalmente é usado assim.:

This command makes everything start fresh– for every query:

• utilização da CPU pode aumentar como novos planos são compilados
• Execução estatísticas são apagadas em sys.dm_exec_query_stats (imediatamente)
• Execução estatísticas são apagadas em sys.dm_exec_procedure_stats (imediatamente)

Este comando não está mal, não há um tempo e um lugar na solução de problemas quando usando ele pode ajudar você a chegar à causa raiz de um problema de desempenho rapidamente. No entanto, uma vez que afeta todo o cache e também pode impactar o desempenho, você deve ser muito cuidadoso ao usá-lo. Eu não recomendo usar este comando em qualquer trabalho regular, processos automatizados, ou código de produção.

Como do SQL Server 2008, você pode usar este comando um pouco mais suave, você pode remover um plano específico de cache usando o “plano de processar” ou “identificador sql”– mas é claro que você tem que descobrir o que esse identificador é e por que você quiser removê-lo. Isto pode vir em útil em algumas situações de nicho, mas na prática não aparece muito. Também podes limpar uma reserva de governador de recursos, mas, bem, terias de estar a usar o governador de recursos.o que significa tudo isto?

quando você está testando, há um lugar na sua caixa de ferramentas tanto para ‘EXEC procedure WITH RECOMPILE’ quanto para o sp_recompile procedure. Use – os com cuidado e cuidado com possíveis repercussões, particularmente com sp_recompile.

Quando você está implementando dicas em código, não tem blinders de RECOMPILE on– Você pode usar ‘Otimizar para’ dicas às vezes com sucesso também (embora às vezes você está otimizando para medíocre). E às vezes, o SQL dinâmico também pode ajudá-lo.

mas se usar dicas de RECOMPILAMENTO, por favor mantenha as suas recompilações ao nível da instrução– e não nos cabeçalhos dos seus procedimentos.

para saber mais, confira nossos fundamentos da classe de Sniffing de Parâmetros.