Partilhar via

Recompilar um procedimento armazenado

  • Artigo

Este tópico descreve como recompilar um procedimento armazenado no SQL Server 2014 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 RECOMPILE dica de consulta em instruções individuais ou usando o procedimento armazenado do sp_recompile sistema. 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 do sistema sp_recompile para recompilar um procedimento existente.

Neste tópico

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).

Segurança

Permissões

WITH RECOMPILE Opção
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).

RECOMPILE Dica de consulta
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

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

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

  2. Na barra Padrão, clique em Nova Consulta.

  3. 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;

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

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

  2. Na barra Padrão, clique em Nova Consulta.

  3. 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  
EXECUTE HumanResources.uspGetAllEmployees WITH RECOMPILE;  
GO

Para recompilar um procedimento armazenado usando sp_recompile

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

  2. Na barra Padrão, clique em Nova Consulta.

  3. 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  
EXEC sp_recompile N'HumanResources.uspGetAllEmployees';  
GO

Consulte Também

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)