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


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

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure платформенная система аналитики (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. Соединитесь с ядром СУБД .

  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)