Artigo
04/01/2012

Recompilando procedimentos armazenados

Como um banco de dados é alterado por ações como adicionar índices ou alterar dados em colunas indexadas, os planos de consulta originais usados para acessar essas tabelas devem ser otimizados novamente por recompilação. Essa otimização ocorre automaticamente na primeira vez em que um procedimento armazenado é executado após a reinicialização do SQL Server. Isso também ocorrerá se uma tabela subjacente usada pelo procedimento armazenado for alterada. Mas se um novo índice for adicionado com o qual o procedimento armazenado possa se beneficiar, a otimização só ocorrerá da próxima vez em que o procedimento armazenado for executado depois da reinicialização do SQL Server. Nessa situação, talvez convenha forçar a recompilação do procedimento armazenado na sua próxima execução.

Outro motivo para forçar a recompilação de um procedimento armazenado é neutralizar, quando necessário, o comportamento de 'detecção de parâmetro' da compilação de procedimento armazenado. Quando o SQL Server executa procedimentos armazenados, quaisquer valores de parâmetro que sejam usados pelo procedimento em sua compilação são incluídos como parte da geração do plano de consulta. Se esses forem valores típicos usados para chamar o procedimento subsequentemente, o procedimento armazenado se beneficiará do plano de consulta toda vez que for compilado ou executado. Caso contrário, o desempenho poderá ser afetado.

O SQL Server 2008 R2 oferece recompilação de procedimentos armazenados em nível de instrução. Quando o SQL Server 2008 R2 recompila procedimentos armazenados, apenas a instrução que causou a recompilação é compilada, e não o procedimento inteiro. Como resultado, o SQL Server usa os valores de parâmetro existentes na instrução recompilada ao gerar novamente um plano de consulta. Esses valores podem diferir dos originalmente passados ao procedimento.

Forçando a recompilação de um procedimento armazenado

SQL Server fornece três modos de forçar a recompilação de um procedimento armazenado:

O procedimento armazenado do sistema sp_recompile força uma recompilação de um procedimento armazenado na sua próxima execução. Isso é feito através da exclusão do plano existente do cache de procedimento, forçando a criação de um novo plano na próxima execução do procedimento.
A criação de um procedimento armazenado que especifica a opção WITH RECOMPILE em sua definição indica que o SQL Server não armazena em cache um plano para esse procedimento armazenado; o procedimento armazenado é recompilado toda vez que é executado. Use a opção WITH RECOMPILE quando os procedimentos armazenados utilizarem parâmetros cujos valores difiram muito entre execuções do procedimento armazenado e levem à criação de planos de execução diferentes a cada vez. O uso dessa opção é incomum e torna a execução do procedimento armazenado mais lenta porque exige a recompilação desse procedimento a cada execução.

Se você desejar que apenas consultas individuais dentro do procedimento armazenado sejam recompiladas, e não o procedimento armazenado inteiro, especifique a dica de consulta RECOMPILE em cada consulta a ser compilada. Esse comportamento imita o comportamento de recompilação em nível da instrução do SQL Server, citado antes nesta seção. Mas, além de usar os valores de parâmetro atuais do procedimento armazenado, a dica de consulta RECOMPILE também usa os valores de quaisquer variáveis locais dentro do procedimento armazenado ao compilar a instrução. Use essa opção quando valores atípicos ou temporários forem usados em apenas um subconjunto de consultas pertencentes ao procedimento armazenado. Para obter mais informações, consulte Dica de consulta (Transact-SQL).

Você pode forçar o procedimento armazenado a ser recompilado especificando a opção WITH RECOMPILE ao executar o procedimento armazenado. Use essa opção apenas se o parâmetro sendo fornecido for atípico ou se os dados tiverem sido alterados significativamente desde que o procedimento armazenado foi criado.

Observação
Se um objeto referenciado por um procedimento armazenado for excluído ou renomeado, um erro será retornado quando o procedimento armazenado for executado. Entretanto, se um objeto referenciado em um procedimento armazenado for substituído por um objeto com o mesmo nome, o procedimento armazenado será executado sem precisar ser recriado.