Перекомпиляция хранимой процедуры
В этом разделе описывается, как перекомпилировать хранимую процедуру в 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
Подключитесь к компоненту Компонент Database Engine.
На стандартной панели выберите пункт Создать запрос.
Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить. В этом примере создается определение процедуры.
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
Подключитесь к компоненту Компонент Database Engine.
На стандартной панели выберите пункт Создать запрос.
Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить. В этом примере создается простая процедура, возвращающая из представления всех сотрудников (с указанием имени и фамилии), их должности и названия отделов.
Затем скопируйте второй пример кода в окно запроса и нажмите кнопку Выполнить. Процедура будет выполнена с повторной компиляцией плана запроса.
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
Подключитесь к компоненту Компонент Database Engine.
На стандартной панели выберите пункт Создать запрос.
Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить. В этом примере создается простая процедура, возвращающая из представления всех сотрудников (с указанием имени и фамилии), их должности и названия отделов.
Затем скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить. Процедура не будет выполнена, но будет помечена для повторной компиляции, и при следующем выполнении процедуры ее план запроса будет обновлен.
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
[В начало]
См. также
Справочник
Основные понятия
Изменение имени хранимой процедуры