ストアド プロシージャの再コンパイルRecompile a Stored Procedure

適用対象: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel 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. これには 3 つの方法があります。プロシージャ定義内またはプロシージャの呼び出し時に 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.

  • プロシージャの再コンパイルを強制的に行うもう 1 つの理由は、プロシージャのコンパイル時に "パラメーターを見つけ出す" 動作の影響を少なくすることです。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 RECOMPILE オプションWITH 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).

RECOMPILE クエリ ヒントRECOMPILE 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_recompile システム ストアド プロシージャsp_recompile System Stored Procedure
指定したプロシージャに対する ALTER 権限が必要です。Requires ALTER permission on the specified procedure.

Transact-SQL の使用Using Transact-SQL

WITH RECOMPILE オプションを使用してストアド プロシージャを実行するにはTo recompile a stored procedure by using the WITH RECOMPILE option

  1. データベース エンジンDatabase Engineに接続します。Connect to the データベース エンジンDatabase 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 RECOMPILE オプションを使用してストアド プロシージャを実行するにはTo recompile a stored procedure by using the WITH RECOMPILE option

  1. データベース エンジンDatabase Engineに接続します。Connect to the データベース エンジンDatabase 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.

    次に、2 番目のコード例をコピーしてクエリ ウィンドウに貼り付け、 [実行] をクリックします。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_recompile を使用してストアド プロシージャを実行するにはTo recompile a stored procedure by using sp_recompile

  1. データベース エンジンDatabase Engineに接続します。Connect to the データベース エンジンDatabase 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)