Перекомпиляция хранимой процедуры

В этом разделе описывается, как перекомпилировать хранимую процедуру в SQL Server 2012 с помощью Transact-SQL. Это можно сделать тремя способами: указать параметр WITH RECOMPILE в определении процедуры или при вызове процедуры, задать указание запроса RECOMPILE в отдельных инструкциях или использовать системную хранимую процедуру sp_recompile. В этом разделе описывается использование параметра WITH RECOMPILE при создании определения процедуры и выполнении существующей процедуры. Также описывается использование системной хранимой процедуры sp_recompile для перекомпиляции существующей процедуры.

В этом разделе

• Перед началом работы выполните следующие действия.

  Рекомендации

  Безопасность

• Для перекомпиляции хранимой процедуры используется:

  Transact-SQL

Перед началом

Рекомендации

• Когда процедура компилируется впервые или повторно, выполняется оптимизация плана запроса процедуры для текущего состояния базы данных и ее объектов. Если данные или структура базы данных подвергаются значительным изменениям, то при перекомпиляции процедуры ее план запроса обновляется и оптимизируется в соответствии с этими изменениями. Это может повысить производительность обработки процедуры.

• Иногда необходимо принудительно выполнить перекомпиляцию процедуры, а иногда это выполняется автоматически. Автоматическая перекомпиляция выполняется при каждом перезапуске SQL Server. Она также проводится, если в базовой таблице, на которую ссылается процедура, происходят изменения физической структуры.

• Другая причина для принудительного перекомпилирования процедуры — это нейтрализация пробного сохранения параметров при компиляции процедуры. Когда SQL Server выполняет процедуры, значения всех используемых при компиляции параметров включаются в формируемый план запроса. Если эти значения типичны для последующих вызовов процедуры, то компиляция и выполнение хранимой процедуры с этим планом запроса происходит быстрее. Если значения параметров для процедуры часто оказываются нетипичными, то принудительная перекомпиляция процедуры и создание нового плана на основе других значений параметров может повысить производительность.

• SQL Server обладает возможностью перекомпиляции процедур на уровне инструкций. Во время перекомпиляции хранимой процедуры SQL Server заново компилирует только вызвавшую этот процесс инструкцию, а не всю процедуру.

• Если некоторые запросы в процедуре регулярно используют нетипичные или временные значения, то можно повысить производительность процедуры, используя указание запроса RECOMPILE в таких запросах. Поскольку перекомпиляцию будут проходить только запросы, использующие это указание, а не вся процедура, то повторная компиляция SQL Server будет работать на уровне инструкций. Однако, помимо использования текущих значений параметров процедуры, указание запроса RECOMPILE при компиляции инструкции также использует значения локальных переменных в хранимой процедуре. Дополнительные сведения см. в разделе Указания запросов (Transact-SQL).

Безопасность

Разрешения

• Параметр WITH RECOMPILE
  Если этот параметр используется при создании определения процедуры, то необходимо разрешение CREATE PROCEDURE в базе данных и разрешение ALTER на схему, в которой создается процедура.

  Если этот параметр используется в инструкции EXECUTE, требуются разрешения EXECUTE на процедуру. Разрешения на саму инструкцию EXECUTE не требуются, однако требуются разрешения на выполнение процедуры, упоминаемой в инструкции EXECUTE. Дополнительные сведения см. в разделе EXECUTE (Transact-SQL).

• Указание запроса RECOMPILE
  Эта возможность используется при создании процедуры, и указание включается в инструкции Transact-SQL в процедуре. Таким образом, требуется разрешение CREATE PROCEDURE в базе данных и разрешение ALTER на схему, в которой создается процедура.

• Системная хранимая процедура sp_recompile
  Необходимо разрешение ALTER на указанную процедуру.

[В начало]

Использование Transact-SQL

Перекомпиляция хранимой процедуры с использованием параметра WITH RECOMPILE

1. Подключитесь к компоненту Компонент Database Engine.

2. На стандартной панели выберите пункт Создать запрос.

3. Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить. В этом примере создается определение процедуры.

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

Перекомпиляция хранимой процедуры с использованием параметра WITH RECOMPILE

1. Подключитесь к компоненту Компонент Database Engine.

2. На стандартной панели выберите пункт Создать запрос.

3. Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить. В этом примере создается простая процедура, возвращающая из представления всех сотрудников (с указанием имени и фамилии), их должности и названия отделов.

   Затем скопируйте второй пример кода в окно запроса и нажмите кнопку Выполнить. Процедура будет выполнена с повторной компиляцией плана запроса.

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

Перекомпиляция хранимой процедуры с использованием процедуры sp_recompile

1. Подключитесь к компоненту Компонент Database Engine.

2. На стандартной панели выберите пункт Создать запрос.

3. Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить. В этом примере создается простая процедура, возвращающая из представления всех сотрудников (с указанием имени и фамилии), их должности и названия отделов.

   Затем скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить. Процедура не будет выполнена, но будет помечена для повторной компиляции, и при следующем выполнении процедуры ее план запроса будет обновлен.

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

[В начало]

См. также

Справочник

DROP PROCEDURE (Transact-SQL)

Основные понятия

Создание хранимой процедуры

Изменение хранимой процедуры

Изменение имени хранимой процедуры

Просмотр определения хранимой процедуры

Просмотр зависимостей хранимой процедуры