Recompilar um procedimento armazenado

Artigo
04/03/2023

Aplica-se a:SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure PDW (Analytics Platform System)

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

Antes de começar

Recomendações

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 passar por 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 de procedimento deve ser forçada e outros em que ela ocorre automaticamente. A recompilação automática ocorre sempre que o SQL Server é reiniciado. Isso também ocorrerá se uma tabela subjacente referenciada pelo procedimento tiver passado por alterações de design físicas.
Outro motivo para forçar a recompilação de um procedimento é neutralizar o comportamento “sugador de parâmetro” da compilação de procedimento. Quando o SQL Server executa procedimentos armazenados, quaisquer valores de parâmetro usados pelo procedimento em sua compilação são incluídos como parte da geração do plano de consulta. Se esses valores representam aqueles típicos com os quais o procedimento é chamado subsequentemente, então o procedimento beneficia-se do plano de consulta cada vez que ele é compilado e executado. Se valores de parâmetros no procedimento forem frequentemente atípicos, forçar uma recompilação do procedimento e um novo plano baseado em valores de parâmetros diferentes poderá melhorar o desempenho.
SQL Server oferece recompilação no nível da instrução de procedimentos. Quando o SQL Server recompila procedimentos armazenados, apenas a instrução que causou a recompilação é compilada, e não o procedimento completo.
Se certas consultas em um procedimento usarem valores atípicos ou temporários regularmente, o desempenho do procedimento poderá ser melhorado com o uso da dica de consulta RECOMPILE nessas consultas. Como apenas as consultas que usam a dica de consulta serão recompiladas, em vez de o procedimento completo, o comportamento de recompilação no nível da instrução no SQL Server será imitado. Além de usar os valores de parâmetro atuais do procedimento, a dica de consulta RECOMPILE também usa os valores de todas as variáveis locais no procedimento armazenado quando você compila a instrução. Para obter mais informações, veja Dica de consulta (Transact-SQL).

Observação

Em 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 de SQL dedicados no Azure Synapse Analytics.

Segurança

Permissões

Opção WITH RECOMPILE

Se a opção for usada quando a definição de procedimento for criada, serão necessárias as permissões CREATE PROCEDURE no banco de dados e ALTER no esquema no qual o procedimento está sendo criado.

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

dica de consulta RECOMPILE

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

sp_recompile procedimento armazenado do sistema

Exige a permissão ALTER no procedimento especificado.

Usando o Transact-SQL

Conecte-se ao Mecanismo de Banco de Dados.
Na barra Padrão , selecione Nova Consulta.

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 para recompilação de modo que seu plano de consulta seja atualizada na próxima vez em que o procedimento for executado.

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

Próximas etapas

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
DROP PROCEDURE (Transact-SQL)