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

Применимо к: SQL Server Azure SQL DatabaseУправляемый экземпляр SQL AzureAnalytics Platform System (PDW)

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

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

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

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

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

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

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

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

Примечание

В выделенных и бессерверных пулах Azure Synapse Analytics хранимые процедуры не являются предварительно скомпилированным кодом и поэтому не могут быть перекомпилированы. Дополнительные сведения см. в статье Использование хранимых процедур для выделенных пулов SQL в Azure Synapse Analytics.

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

Разрешения

ПАРАМЕТР WITH RECOMPILE

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

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

указание запроса RECOMPILE

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

sp_recompile системной хранимой процедуре

Необходимо разрешение ALTER на указанную процедуру.

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

1. Установите соединение с компонентом Компонент Database Engine.

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

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

   USE AdventureWorks2022;  
   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

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

USE AdventureWorks2022;  
GO  
EXECUTE HumanResources.uspProductByVendor WITH RECOMPILE;  
GO

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

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

USE AdventureWorks2022;  
GO  
EXEC sp_recompile N'dbo.uspProductByVendor';   
GO

Дальнейшие действия

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