Recompilar um procedimento armazenado

Artigo
04/02/2014

Este tópico descreve como recompilar um procedimento armazenado no SQL Server 2012 usando o Transact-SQL. Há três modos de fazer isso: a opção WITH RECOMPILE na definição do procedimento ou quando o procedimento é chamado, a dica de consulta RECOMPILE em instruções individuais ou usando o procedimento armazenado de sistema sp_recompile. Este tópico descreve como usar a opção WITH RECOMPILE ao criar uma definição de procedimento e executar um procedimento existente. Também descreve como usar o procedimento armazenado de sistema sp_recompile para recompilar um procedimento existente.

Neste tópico

Antes de começar:

Recomendações

Segurança
Para recompilar um procedimento armazenado usando:

Transact-SQL

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.
O 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, consulte Dica de consulta (Transact-SQL).

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, consulte 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.
Procedimento armazenado do sistema sp_recompile
Exige a permissão ALTER no procedimento especificado.

Ícone de seta usado com o link Voltar ao Início [Início]

Usando Transact-SQL

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

Conecte-se ao Mecanismo de Banco de Dados.
Na barra Padrão, clique em Nova Consulta.
Copie e cole o exemplo a seguir na janela de consulta e clique em Executar. Este exemplo cria a definição de procedimento.

USE AdventureWorks2012;
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;
GO

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

Conecte-se ao Mecanismo de Banco de Dados.
Na barra Padrão, clique em Nova Consulta.
Copie e cole o exemplo a seguir na janela de consulta e clique em Executar. Este exemplo cria um procedimento simples que retorna todos os funcionários (com os nomes e sobrenomes fornecidos), os cargos e os nomes de departamento em uma exibição.

E copie e cole o segundo exemplo de código na janela de consulta e clique em Executar. Isso executa o procedimento e recompila o plano de consulta do procedimento.

USE AdventureWorks2012;
GO
IF OBJECT_ID ( 'HumanResources.uspGetAllEmployees', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetAllEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
    SET NOCOUNT ON;
    SELECT LastName, FirstName, Department
    FROM HumanResources.vEmployeeDepartmentHistory;
GO

USE AdventureWorks2012;
GO
EXECUTE HumanResources.uspGetAllEmployees WITH RECOMPILE;
GO

Para recompilar um procedimento armazenado usando sp_recompile

Conecte-se ao Mecanismo de Banco de Dados.
Na barra Padrão, clique em Nova Consulta.
Copie e cole o exemplo a seguir na janela de consulta e clique em Executar. Este exemplo cria um procedimento simples que retorna todos os funcionários (com os nomes e sobrenomes fornecidos), os cargos e os nomes de departamento em uma exibição.

Em seguida, copie e cole o exemplo a seguir na janela de consulta e clique em 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 AdventureWorks2012;
GO
IF OBJECT_ID ( 'HumanResources.uspGetAllEmployees', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetAllEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
    SET NOCOUNT ON;
    SELECT LastName, FirstName, Department
    FROM HumanResources.vEmployeeDepartmentHistory;
GO

USE AdventureWorks2012;
GO
EXEC sp_recompile N'HumanResources.uspGetAllEmployees';
GO

Ícone de seta usado com o link Voltar ao Início [Início]