Recompilar um procedimento armazenado

Aplica-se a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSistema de Plataforma de Análise (PDW)Base de dados SQL no Microsoft Fabric

Este artigo descreve como recompilar um procedimento armazenado no SQL Server usando Transact-SQL. Há três maneiras de fazer isso: WITH RECOMPILE opção na definição do procedimento ou quando o procedimento é chamado, a sugestão de consulta RECOMPILE em instruções individuais, ou usando o procedimento armazenado do sistema sp_recompile.

Antes de começar

Recommendations

• Quando um procedimento é compilado pela primeira vez ou recompilado, o plano de consulta do procedimento é otimizado para o estado atual do banco de dados e seus objetos. Se um banco de dados sofrer alterações significativas em seus dados ou estrutura, a recompilação de um procedimento atualizará e otimizará o plano de consulta do procedimento para essas alterações. Isso pode melhorar o desempenho do processamento do procedimento.

• Há momentos em que a recompilação do procedimento deve ser forçada e outras vezes em que ocorre automaticamente. A recompilação automática ocorre sempre que o SQL Server é reiniciado. Também ocorre se uma tabela subjacente referenciada pelo procedimento tiver sofrido alterações de design físico.

• Outra razão para forçar um procedimento a recompilar é neutralizar o comportamento de "deteção de parâmetros" da compilação de procedimentos. Quando o SQL Server executa procedimentos, todos os valores de parâmetro usados pelo procedimento quando ele é compilado são incluídos como parte da geração do plano de consulta. Se esses valores representarem os típicos com os quais o procedimento é chamado posteriormente, o procedimento se beneficia do plano de consulta toda vez que compila e executa. Se os valores dos parâmetros no procedimento forem frequentemente atípicos, forçar uma recompilação do procedimento e um novo plano baseado em diferentes valores de parâmetros pode melhorar o desempenho.

• O SQL Server apresenta recompilação de procedimentos ao nível da instrução. Quando o SQL Server recompila procedimentos armazenados, somente a instrução que causou a recompilação é compilada, em vez do procedimento completo.

• Se determinadas consultas em um procedimento usam regularmente valores atípicos ou temporários, o desempenho do procedimento pode ser melhorado usando a dica de consulta RECOMPILE dentro dessas consultas. Como apenas as consultas que usam a dica de consulta serão recompiladas em vez do procedimento completo, o comportamento de recompilação em nível de instrução do SQL Server é imitado. Mas, além de usar os valores de parâmetro atuais do procedimento, a dica de consulta RECOMPILE também usa os valores de quaisquer variáveis locais dentro do procedimento armazenado quando você compila a instrução. Para obter mais informações, consulte Dica de consulta (Transact-SQL).

Note

Nos pools dedicados e sem servidor do Azure Synapse Analytics, os procedimentos armazenados não são código pré-compilado e, portanto, não podem ser recompilados. Para obter mais informações, consulte Usando procedimentos armazenados para pools SQL dedicados no Azure Synapse Analytics.

Segurança

Permissions

COM a opção RECOMPILE

Se essa opção for usada quando a definição de procedimento for criada, ela exigirá a permissão CREATE PROCEDURE no banco de dados e a permissão ALTER no esquema no qual o procedimento está sendo criado.

Se esta opção for usada numa instrução EXECUTE, essa opção exigirá permissões EXECUTE no procedimento. As permissões não são necessárias na instrução EXECUTE em si, mas as permissões de execução são necessárias no procedimento referenciado na instrução EXECUTE. Para obter mais informações, consulte EXECUTE (Transact-SQL).

RECOMPILAR dica de consulta

Esse recurso é usado quando o procedimento é criado e a dica é incluída nas instruções Transact-SQL do procedimento. Portanto, ele requer a permissão CREATE PROCEDURE no banco de dados e a permissão ALTER no esquema no qual o procedimento está sendo criado.

procedimento armazenado do sistema sp_recompile

Requer a permissão ALTER no procedimento especificado.

Usando Transact-SQL

1. Conecte-se ao Mecanismo de Banco de Dados.

2. Na barra Standard, selecione Nova Consulta.

3. Copie e cole o exemplo a seguir na janela de consulta e selecione Executar. Este exemplo cria a definição de procedimento.

   USE AdventureWorks2022;  
   GO  
   IF OBJECT_ID ( 'dbo.uspProductByVendor', 'P' ) IS NOT NULL   
       DROP PROCEDURE dbo.uspProductByVendor;  
   GO  
   CREATE PROCEDURE dbo.uspProductByVendor @Name varchar(30) = '%'  
   WITH RECOMPILE  
   AS  
       SET NOCOUNT ON;  
       SELECT v.Name AS 'Vendor name', p.Name AS 'Product name'  
       FROM Purchasing.Vendor AS v   
       JOIN Purchasing.ProductVendor AS pv   
         ON v.BusinessEntityID = pv.BusinessEntityID   
       JOIN Production.Product AS p   
         ON pv.ProductID = p.ProductID  
       WHERE v.Name LIKE @Name;  
   

Para recompilar um procedimento armazenado usando a opção WITH RECOMPILE

Selecione Nova Consulta, copie e cole o exemplo de código a seguir na janela de consulta e selecione Executar. Isso executa o procedimento e recompila o plano de consulta do procedimento.

USE AdventureWorks2022;  
GO  
EXECUTE HumanResources.uspProductByVendor WITH RECOMPILE;  
GO

Para recompilar um procedimento armazenado usando sp_recompile

Selecione Nova Consulta, copie e cole o exemplo a seguir na janela de consulta e selecione Executar. Isso não executa o procedimento, mas marca o procedimento a ser recompilado para que seu plano de consulta seja atualizado na próxima vez que o procedimento for executado.

USE AdventureWorks2022;  
GO  
EXEC sp_recompile N'dbo.uspProductByVendor';   
GO

Próximos passos

criar um procedimento armazenado
modificar um procedimento armazenado
Renomear um procedimento armazenado
exibir a definição de um procedimento armazenado
exibir as dependências de um procedimento armazenado
PROCEDIMENTO DE QUEDA (Transact-SQL)