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

  • [アーティクル]

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAnalytics Platform System (PDW)

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

はじめに

推奨事項

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

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

  • プロシージャの再コンパイルを強制的に行うもう 1 つの理由は、プロシージャのコンパイル時に "パラメーターを見つけ出す" 動作の影響を少なくすることです。 SQL Server でプロシージャが実行されるとき、コンパイル時にプロシージャによって使用されるパラメーター値は、クエリ プランの生成の一部に含まれます。 これらの値が、その後呼び出されるプロシージャの標準的な値を表す場合は、プロシージャのコンパイルや実行のたびに、そのクエリ プランからメリットを得ることができます。 プロシージャのパラメーター値が非定型の値である場合は、プロシージャを強制的に再コンパイルし、異なるパラメーター値に基づく新しいプランを生成することにより、パフォーマンスを向上させることができます。

  • SQL Server には、プロシージャをステートメント レベルで再コンパイルする機能が備わっています。 SQL Server でストアド プロシージャを再コンパイルすると、プロシージャ全体ではなく、再コンパイルが必要なステートメントだけがコンパイルされます。

  • プロシージャの特定のクエリで通常使用される値が非定型の値や一時的な値である場合は、それらのクエリ内で RECOMPILE クエリ ヒントを使用することにより、プロシージャのパフォーマンスを向上させることができます。 再コンパイルされるのは、プロシージャ全体ではなく、クエリ ヒントを使用したクエリのみであるため、 SQL Serverのステートメント レベルの再コンパイル動作を模倣できます。 ただし、RECOMPILE クエリ ヒントを使用した場合、ステートメントをコンパイルするときに、プロシージャの現在のパラメーター値に加えてストアド プロシージャ内の任意のローカル変数の値も使用されます。 詳細については、「 クエリ ヒント (Transact-SQL)」を参照してください。

注意

Azure Synapse Analytics 専用プールとサーバーレス プールでは、ストアド プロシージャは事前コンパイル済みのコードではなく、再コンパイルできません。 詳細については、「Azure Synapse Analytics の専用 SQL プールにストアド プロシージャを使用する」を参照してください。

セキュリティ

アクセス許可

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)