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

ОБЛАСТЬ ПРИМЕНЕНИЯ: даSQL Server даБаза данных SQL Azure даAzure Synapse Analytics (хранилище данных SQL) даParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

В этом разделе описывается, как перекомпилировать хранимую процедуру в SQL ServerSQL Server с помощью Transact-SQLTransact-SQL.This topic describes how to recompile a stored procedure in SQL ServerSQL Server by using Transact-SQLTransact-SQL. Это можно сделать тремя способами: указать параметр WITH RECOMPILE в определении процедуры или при вызове процедуры, задать указание запроса RECOMPILE в отдельных инструкциях или использовать системную хранимую процедуру sp_recompile.There are three ways to do this: WITH RECOMPILE option in the procedure definition or when the procedure is called, the RECOMPILE query hint on individual statements, or by using the sp_recompile system stored procedure. В этом разделе описывается использование параметра WITH RECOMPILE при создании определения процедуры и выполнении существующей процедуры.This topic describes using the WITH RECOMPILE option when creating a procedure definition and executing an existing procedure. Также описывается использование системной хранимой процедуры sp_recompile для перекомпиляции существующей процедуры.It also describes using the sp_recompile system stored procedure to recompile an existing procedure.

В этом разделеIn This Topic

Перед началомBefore You Begin

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

  • Когда процедура компилируется впервые или повторно, выполняется оптимизация плана запроса процедуры для текущего состояния базы данных и ее объектов.When a procedure is compiled for the first time or recompiled, the procedure's query plan is optimized for the current state of the database and its objects. Если данные или структура базы данных подвергаются значительным изменениям, то при перекомпиляции процедуры ее план запроса обновляется и оптимизируется в соответствии с этими изменениями.If a database undergoes significant changes to its data or structure, recompiling a procedure updates and optimizes the procedure's query plan for those changes. Это может повысить производительность обработки процедуры.This can improve the procedure's processing performance.

  • Иногда необходимо принудительно выполнить перекомпиляцию процедуры, а иногда это выполняется автоматически.There are times when procedure recompilation must be forced and other times when it occurs automatically. Автоматическая перекомпиляция выполняется при каждом перезапуске SQL ServerSQL Server .Automatic recompiling occurs whenever SQL ServerSQL Server is restarted. Она также проводится, если в базовой таблице, на которую ссылается процедура, происходят изменения физической структуры.It also occurs if an underlying table referenced by the procedure has undergone physical design changes.

  • Другая причина для принудительного перекомпилирования процедуры — это нейтрализация пробного сохранения параметров при компиляции процедуры.Another reason to force a procedure to recompile is to counteract the "parameter sniffing" behavior of procedure compilation. Когда SQL ServerSQL Server выполняет процедуры, значения всех используемых при компиляции параметров включаются в формируемый план запроса.When SQL ServerSQL Server executes procedures, any parameter values that are used by the procedure when it compiles are included as part of generating the query plan. Если эти значения типичны для последующих вызовов процедуры, то компиляция и выполнение хранимой процедуры с этим планом запроса происходит быстрее.If these values represent the typical ones with which the procedure is subsequently called, then the procedure benefits from the query plan every time that it compiles and executes. Если значения параметров для процедуры часто оказываются нетипичными, то принудительная перекомпиляция процедуры и создание нового плана на основе других значений параметров может повысить производительность.If parameter values on the procedure are frequently atypical, forcing a recompile of the procedure and a new plan based on different parameter values can improve performance.

  • SQL ServerSQL Server обладает возможностью перекомпиляции процедур на уровне инструкций.features statement-level recompilation of procedures. Во время перекомпиляции хранимой процедуры SQL ServerSQL Server заново компилирует только вызвавшую этот процесс инструкцию, а не всю процедуру.When SQL ServerSQL Server recompiles stored procedures, only the statement that caused the recompilation is compiled, instead of the complete procedure.

  • Если некоторые запросы в процедуре регулярно используют нетипичные или временные значения, то можно повысить производительность процедуры, используя указание запроса RECOMPILE в таких запросах.If certain queries in a procedure regularly use atypical or temporary values, procedure performance can be improved by using the RECOMPILE query hint inside those queries. Поскольку перекомпиляцию будут проходить только запросы, использующие это указание, а не вся процедура, то повторная компиляция SQL ServerSQL Serverбудет работать на уровне инструкций.Since only the queries using the query hint will be recompiled instead of the complete procedure, SQL ServerSQL Server's statement-level recompilation behavior is mimicked. Однако, помимо использования текущих значений параметров процедуры, указание запроса RECOMPILE при компиляции инструкции также использует значения локальных переменных в хранимой процедуре.But in addition to using the procedure's current parameter values, the RECOMPILE query hint also uses the values of any local variables inside the stored procedure when you compile the statement. Дополнительные сведения см. в разделе Указания запросов (Transact-SQL).For more information, see Query Hint (Transact-SQL).

безопасностьSecurity

PermissionsPermissions

ПараметрWITH RECOMPILEWITH RECOMPILE Option
Если этот параметр используется при создании определения процедуры, то необходимо разрешение CREATE PROCEDURE в базе данных и разрешение ALTER на схему, в которой создается процедура.If this option is used when the procedure definition is created, it requires CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created.

Если этот параметр используется в инструкции EXECUTE, требуются разрешения EXECUTE на процедуру.If this option is used in an EXECUTE statement, it requires EXECUTE permissions on the procedure. Разрешения на саму инструкцию EXECUTE не требуются, однако требуются разрешения на выполнение процедуры, упоминаемой в инструкции EXECUTE.Permissions are not required on the EXECUTE statement itself but execute permissions are required on the procedure referenced in the EXECUTE statement. Дополнительные сведения см. в разделе EXECUTE (Transact-SQL).For more information, see EXECUTE (Transact-SQL).

Указание запроса RECOMPILERECOMPILE Query Hint
Эта возможность используется при создании процедуры, и указание включается в инструкции Transact-SQLTransact-SQL в процедуре.This feature is used when the procedure is created and the hint is included in Transact-SQLTransact-SQL statements in the procedure. Таким образом, требуется разрешение CREATE PROCEDURE в базе данных и разрешение ALTER на схему, в которой создается процедура.Therefore, it requires CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created.

Выполнение системной хранимой процедурыsp_recompilesp_recompile System Stored Procedure
Необходимо разрешение ALTER на указанную процедуру.Requires ALTER permission on the specified procedure.

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

Перекомпиляция хранимой процедуры с использованием параметра WITH RECOMPILETo recompile a stored procedure by using the WITH RECOMPILE option

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

  2. На панели «Стандартная» нажмите Создать запрос.From the Standard bar, click New Query.

  3. Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить.Copy and paste the following example into the query window and click Execute. В этом примере создается определение процедуры.This example creates the procedure definition.

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 RECOMPILETo recompile a stored procedure by using the WITH RECOMPILE option

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

  2. На панели «Стандартная» нажмите Создать запрос.From the Standard bar, click New Query.

  3. Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить.Copy and paste the following example into the query window and click Execute. В этом примере создается простая процедура, возвращающая из представления всех сотрудников (с указанием имени и фамилии), их должности и названия отделов.This example creates a simple procedure that returns all employees (first and last names supplied), their job titles, and their department names from a view.

    Затем скопируйте второй пример кода в окно запроса и нажмите кнопку Выполнить.And then copy and paste the second code example into the query window and click Execute. Процедура будет выполнена с повторной компиляцией плана запроса.This executes the procedure and recompiles the procedure's query plan.

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

Перекомпиляция хранимой процедуры с использованием процедуры sp_recompileTo recompile a stored procedure by using sp_recompile

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

  2. На панели «Стандартная» нажмите Создать запрос.From the Standard bar, click New Query.

  3. Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить.Copy and paste the following example into the query window and click Execute. В этом примере создается простая процедура, возвращающая из представления всех сотрудников (с указанием имени и фамилии), их должности и названия отделов.This example creates a simple procedure that returns all employees (first and last names supplied), their job titles, and their department names from a view.

    Затем скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить.Then, copy and paste the following example into the query window and click Execute. Процедура не будет выполнена, но будет помечена для повторной компиляции, и при следующем выполнении процедуры ее план запроса будет обновлен.This does not execute the procedure but it does mark the procedure to be recompiled so that its query plan is updated the next time that the procedure is executed.

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

См. также:See Also

Создание хранимой процедуры Create a Stored Procedure
Изменение хранимой процедуры Modify a Stored Procedure
Изменение имени хранимой процедуры Rename a Stored Procedure
Просмотр определения хранимой процедуры View the Definition of a Stored Procedure
Просмотр зависимостей хранимой процедуры View the Dependencies of a Stored Procedure
DROP PROCEDURE (Transact-SQL)DROP PROCEDURE (Transact-SQL)