次の方法で共有

ストアド プロシージャを再コンパイルする

このトピックでは、Transact-SQL を使用して SQL Server 2014 でストアド プロシージャを再コンパイルする方法について説明します。 これを行うには、3 つの方法があります。プロシージャ定義 WITH RECOMPILE オプション、プロシージャが呼び出されたとき、個々のステートメントに対する RECOMPILE クエリ ヒント、または sp_recompile システム ストアド プロシージャを使用する方法です。 このトピックでは、プロシージャ定義を作成し、既存のプロシージャを実行するときに WITH RECOMPILE オプションを使用する方法について説明します。 また、sp_recompile システム ストアド プロシージャを使用して既存のプロシージャを再コンパイルする方法についても説明します。

このトピックについて

開始する前に

推奨事項

  • プロシージャが初めてコンパイルされたとき、または再コンパイルされるとき、プロシージャのクエリ プランは、データベースとそのオブジェクトの現在の状態に合わせて最適化されます。 データベースのデータまたは構造が大幅に変更された場合、プロシージャを再コンパイルすると、それらの変更に対するプロシージャのクエリ プランが更新され、最適化されます。 これにより、プロシージャの処理パフォーマンスが向上します。

  • プロシージャの再コンパイルを強制する必要がある場合や、プロシージャが自動的に実行される場合があります。 自動再コンパイルは、SQL Server が再起動されるたびに発生します。 また、プロシージャによって参照される基になるテーブルが物理的な設計変更を受けた場合にも発生します。

  • プロシージャを強制的に再コンパイルするもう 1 つの理由は、プロシージャ コンパイルの "パラメーター スニッフィング" 動作に対抗するためです。 SQL Server がプロシージャを実行すると、コンパイル時にプロシージャによって使用されるパラメーター値は、クエリ プランの生成の一部として含まれます。 これらの値が、プロシージャが後で呼び出される一般的な値を表す場合、プロシージャはコンパイルと実行のたびにクエリ プランの恩恵を受けます。 プロシージャのパラメーター値が通常とは異なる場合は、プロシージャの再コンパイルを強制し、異なるパラメーター値に基づいて新しいプランを実行すると、パフォーマンスが向上する可能性があります。

  • SQL Server には、プロシージャのステートメント レベルの再コンパイルが用意されています。 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. 次の例をコピーしてクエリ ウィンドウに貼り付け、 [実行] をクリックします。 この例では、すべての従業員 (指定された姓と名)、役職、および部門名をビューから返す簡単なプロシージャを作成します。

    次に、2 番目のコード例をコピーしてクエリ ウィンドウに貼り付け、[ 実行] をクリックします。 これにより、プロシージャが実行され、プロシージャのクエリ プランが再コンパイルされます。

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)