Aguarde… Carregando

Eu preciso dar um sp_recompile em uma procedure após alterá-la?

Fala Pessoal,

Você já ouviu alguém dizer que quando se altera uma procedure devemos dar um sp_recompile nessa SP?

Até essa semana eu acreditava realmente que isso era necessário. O argumento que me deram quando eu aprendi e que eu mesmo respondia para alguém quando era questionado sobre o motivo de executar a sp_recompile em uma procedure após alterá-la era o seguinte:

“Quando alteramos uma procedure devemos executar o sp_recompile nessa procedure para forçarmos uma recompilação do seu plano de execução que pode ter sido alterado com a manutenção que foi realizada nessa procedure.”

Realmente, faz todo o sentido, pois ao alterarmos uma procedure podemos incluir ou excluir uma query, o que com certeza altera o plano de execução dessa procedure. Entretanto, realizando a leitura do meu livro SQL Server 2008 Internals, eu li que a execução do comando ALTER PROCEDURE exclui o plano dessa procedure do cache. Na mesma hora veio a voz do Silvio Santos na minha cabeça: “Ma ma eu só acreditoooo… veeeendoo, hi hiiii”.Então subi minha Vm para realizar meus testes.

Inicialmente, criei uma tabela e populei a mesma com 1000 registros.

Em seguida criei duas procedures para realizar os testes.

Com a query abaixo podemos ver quais os planos de execução estão em cache. Chamarei essa query de QUERY A para referenciar as execuções posteriores da mesma.

Neste momento, essa query não retorna nenhum registro. Entretanto, após a execução do script abaixo.

Executando a QUERY A novamente temos o seguinte resultado.

FIGURA 1

Podemos claramente verificar no valor da coluna Usecounts que o mesmo plano de execução da stpConsulta_Vendas foi utilizado 4 vezes, sendo duas chamadas diretas e duas chamadas de dentro da stpConsulta_Vendas_2. Já o plano de execução da stpConsulta_Vendas_2 foi utilizado as duas vezes que foi chamado explicitamente.

Agora que é a hora da verdade, vamos alterar nossa procedure e ver se o planos de execução será eliminado do cache.

Executando a Query A, temos o seguinte resultado.

FIGURA 2

Logo, o plano de execução da procedure stpConsulta_Vendas que havia sido executado 4 vezes foi removido do cache. Com isso, confirmamos o fato de que quando alteramos uma procedure, o plano de execução dessa procedure já é excluído do cache automaticamente, não necessitando assim de executarmos o comando sp_recompile para essa procedure.

Aproveitando, vamos executar o comando sp_recompile para a stoConsulta_Vendas_2 para visualizarmos que o plano de execução dessa procedure também será excluído do cache.

exec sp_recompile stpConsulta_Vendas_2

Executando novamente a QUERY A é possível identificar que o plano de execução dessa procedure também foi retirado do cache.

Em suma, a partir de agora nunca mais executarei um sp_recompile após alterar uma procedure. Vivendo e aprendendo.

 
Gostou desse Post?

Cadastre seu e-mail para receber novos Posts e curta minha Página no Facebook para receber Dicas de Leituras e Eventos sobre SQL Server.

Abraços,

Fabrício Lima

MCITP – Database Administrator

Consultor e Instrutor SQL Server

Trabalha com SQL Server desde 2006

6 thoughts on “Eu preciso dar um sp_recompile em uma procedure após alterá-la?

  1. Fabricio,Ótima dica, também estou lendo o SQL Server 2008 Internals e estou gostando muito!.Dicas valiosas como essas, são indispensáveis!Abraço..

  2. Fabricio,Muito legal seu post, serviu inclusive como referencia para um treinamento que ministrei de SQL Server 2008 em Natal/RN. Realmente o SQL Server 2008 Internals abre a cabeça da gente para a "verdade" com informações verdadeiras e não as suposições muitos livros e blogs apresentam.Aproveita que voce está lendo o Internals, marca um treinamento com a Kellen Delaney. 🙂 Depois é tirar o MCM e correr para o abraço.Saudações,Alexandre Lopes

  3. Valeu Alexandre… Realmente, ele acaba com muitos mitos que eu acreditava…. E olha que eu só li uns 3 capitulos dele até agora…Ja combinei com minha amiga Kellen o curso… rsabraços

  4. No 2005 já tive de recompilar uma procedure que foi trocado os parâmetros de entrada e a procedure continuava a receber os parâmetros velhos, acho que na versão 2008 em diante devem ter melhorado, evitando assim a necessidade de recompilar os objetos.

Deixe uma resposta

%d blogueiros gostam disto: