No SQL Server, os procedimentos armazenados são uma ferramenta poderosa que permite aos desenvolvedores escrever código T-SQL reutilizável. Eles oferecem vários benefícios, incluindo melhor desempenho, reutilização de código, manutenibilidade e segurança. No entanto, é importante entender como os procedimentos armazenados são compilados e como podem ser recompilados para garantir um desempenho ideal.

Quando um Procedimento Armazenado é Compilado?

Contrariamente à crença popular, os procedimentos armazenados não são compilados durante sua criação inicial. Em vez disso, eles são compilados em sua primeira execução. Isso significa que o otimizador de consultas cria um plano de execução para o procedimento armazenado e o armazena no cache de planos de consulta. Execuções subsequentes do mesmo procedimento armazenado usarão esse plano em cache, evitando a necessidade de compilação desnecessária de consultas.

Monitorando a Compilação de Procedimentos Armazenados

Para monitorar o processo de compilação de procedimentos armazenados no SQL Server, você pode usar ferramentas como Visualizações de Gerenciamento Dinâmico (DMVs) e Eventos Estendidos. DMVs são visualizações do sistema que armazenam informações sobre contadores de desempenho do SQL Server e outras métricas. Eventos Estendidos é uma ferramenta de monitoramento mais avançada que captura e relata vários eventos que ocorrem no SQL Server.

Ao criar um evento estendido, você pode capturar eventos relacionados à compilação de procedimentos armazenados, como o plano de compilação inicial, acertos e falhas de cache e remoções de planos. Isso permite que você obtenha insights sobre o processo de compilação e entenda como os procedimentos armazenados estão sendo compilados e recompilados.

Recompilando Procedimentos Armazenados

Em alguns casos, você pode querer forçar a recompilação de um procedimento armazenado, seja para resolver problemas de desempenho ou para garantir que o plano de execução mais atualizado seja usado. Existem várias maneiras de fazer isso:

1. Usando sp_recompile

O procedimento de sistema sp_recompile pode ser usado para recompilar um procedimento armazenado específico. Ao executar sp_recompile 'NomeDoProcedimento', você pode remover o plano de execução do procedimento do cache, forçando o SQL Server a recompilá-lo na próxima execução.

2. Usando a Dica WITH RECOMPILE

A dica WITH RECOMPILE pode ser adicionada à definição do procedimento armazenado para forçá-lo a ser recompilado toda vez que for executado. Isso pode ser útil em casos em que o “parameter sniffing” ou outros fatores podem causar um plano de execução subótimo. Ao alterar o procedimento armazenado e adicionar WITH RECOMPILE no final, você garante que um novo plano de execução seja gerado para cada execução.

3. Alterando o Procedimento Armazenado

Ao alterar um procedimento armazenado, também é removida a entrada do plano de consulta para o procedimento armazenado do cache de planos, resultando em uma recompilação na próxima execução. Isso pode ser observado monitorando o evento sp_cache_remove no evento estendido.

Conclusão

Neste artigo, exploramos os conceitos de compilação e recompilação de procedimentos armazenados no SQL Server. Aprendemos como os procedimentos armazenados são compilados em sua primeira execução e como podem ser recompilados usando opções como sp_recompile e a dica WITH RECOMPILE. Compreender esses conceitos é crucial para otimizar o desempenho de suas aplicações SQL Server e garantir que os planos de execução mais eficientes sejam utilizados.