Compartilhar via

Recompilar um procedimento armazenado

Este tópico descreve como recompilar um procedimento armazenado no SQL Server 2014 usando o Transact-SQL. Há três maneiras 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 do 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. Ele também descreve o uso do procedimento armazenado do sistema sp_recompile para recompilar um procedimento existente.

Nesse 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, recompilar um procedimento atualizará e otimiza o plano de consulta do procedimento para essas alterações. Isso pode melhorar o desempenho de 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 ocorrerá se uma tabela subjacente referenciada pelo procedimento tiver passado por alterações de design físico.

  • Outro motivo para forçar um procedimento a recompilar é contrariar o comportamento de "detecção de parâmetros" da compilação do procedimento. Quando o SQL Server executa procedimentos, todos os valores de parâmetro usados pelo procedimento quando ele compila são incluídos como parte da geração do plano de consulta. Se esses valores representarem os comuns com os quais o procedimento é chamado posteriormente, o procedimento se beneficiará do plano de consulta sempre que for compilado e executado. Se os valores de parâmetro no procedimento forem frequentemente atípicos, forçar um recompilação do procedimento e um novo plano com base em valores de parâmetros diferentes poderá melhorar o desempenho.

  • O SQL Server apresenta a recompilação de procedimentos no 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 usarem regularmente valores atípicos ou temporários, o desempenho do procedimento poderá ser melhorado usando a dica de consulta RECOMPILE dentro dessas consultas. Como somente as consultas que usam a dica de consulta serão recompiladas em vez do procedimento completo, o comportamento de recompilação no nível da 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 qualquer variável local dentro do procedimento armazenado ao compilar a instrução. Para obter mais informações, consulte Dica de Consulta (Transact-SQL).

Segurança

Permissões

WITH RECOMPILE Opção
Se essa opção for usada quando a definição do 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 essa opção for usada em uma instrução EXECUTE, ela requer permissões EXECUTE no procedimento. As permissões não são necessárias na própria instrução EXECUTE, 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).

RECOMPILE Dica de consulta
Esse recurso é usado quando o procedimento é criado e a dica é incluída nas instruções Transact-SQL dentro 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.

sp_recompile Procedimento armazenado do sistema
Requer permissão ALTER no procedimento especificado.

Usando 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 utilizando 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 (primeiros e sobrenomes fornecidos), seus cargos e seus nomes de departamento de uma visão.

    Em seguida, 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 associado ao 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 seus primeiros e últimos nomes, seus cargos e os nomes de seus departamentos a partir de uma visã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 procedimento a ser recompilado para que seu plano de consulta seja atualizado na próxima vez 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)

  • Last updated on