Поделиться через

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

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

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

Перед началом работы

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

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

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

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

  • SQL Server поддерживает перекомпиляцию процедур на уровне отдельных SQL инструкций. Когда 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. Подключитесь к движку СУБД.

  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;

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

  1. Подключитесь к движку СУБД.

  2. На панели «Стандартная» нажмите Создать запрос.

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

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

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

Перекомпилировать хранимую процедуру с помощью sp_recompile

  1. Подключитесь к движку СУБД.

  2. На панели «Стандартная» нажмите Создать запрос.

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

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

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

См. также

Создание хранимой процедуры
Изменение хранимой процедуры
Изменение имени хранимой процедуры
Просмотр определения хранимой процедуры
Просмотр зависимостей хранимой процедуры
DROP PROCEDURE (Transact-SQL)

  • Last updated on