プランガイド

適用対象: SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceMicrosoft Fabric SQL Database

SQL Server の実際のクエリのテキストを直接変更することが不可能な場合や望ましくない場合に、プラン ガイドを使用してクエリのパフォーマンスを最適化することができます。 プラン ガイドは、クエリ ヒントまたは固定クエリ プランをクエリにアタッチすることにより、クエリの最適化を促します。 プラン ガイドは、サード パーティ ベンダーが提供するデータベース アプリケーションのクエリの小さなサブセットで、期待どおりのパフォーマンスが得られない場合に役に立ちます。 プラン ガイドでは、最適化する Transact-SQL ステートメントのほか、使用するクエリ ヒントを含む OPTION 句またはクエリの最適化に使用する特定のクエリ プランのいずれかを指定します。 クエリが実行されると、SQL Server により Transact-SQL ステートメントがプラン ガイドと照合され、実行時にクエリに OPTION 句がアタッチされるか、指定されたクエリ プランが使用されます。 通常、クエリにとって最適な実行プランが SQL Server クエリ オプティマイザーによって選択されるため、ヒントは、経験を積んだ開発者やデータベース管理者が最後の手段としてのみ使用することをおすすめします。

作成できるプラン ガイドの総数の上限は、使用可能なシステム リソースによって決まります。 ただし、プラン ガイドは、ミッションクリティカルなクエリのパフォーマンスの向上と安定化を図る目的にのみ使用する必要があります。 プラン ガイドの使用により配置済みのアプリケーションのクエリ負荷の多くが影響を受けることがないようにしてください。

この機能によって強制され、生成される実行プランは、強制されているプランと同じか、または似たものになります。 生成されるプランは、プラン ガイドによって指定されたプランと同じではない可能性があるため、プランのパフォーマンスは変化することがあります。 まれに、パフォーマンスの違いが大きくマイナスになる可能性があります。その場合、管理者は強制されたプランを削除する必要があります。

プラン ガイドは、Microsoft SQL Server のすべてのエディションで使用できるわけではありません。 SQL Serverの各エディションでサポートされる機能の一覧については、「 SQL Server 2016 の各エディションがサポートする機能」を参照してください。 プラン ガイドはどのエディションでも表示できます。 また、プラン ガイドを含むデータベースは、どのエディションに対してもアタッチできます。 アップグレード済みのバージョンの SQL Server にデータベースを復元またはアタッチした場合、プラン ガイドはまったく影響を受けません。

プラン ガイドの種類

次の種類のプラン ガイドを作成できます。

OBJECT プラン ガイド

OBJECT プラン ガイドでは、Transact-SQL ストアド プロシージャ、スカラー ユーザー定義関数、複数ステートメント テーブル値ユーザー定義関数、および DML トリガーのコンテキストで実行されるクエリが照合されます。

@Country_region パラメーターを受け取る次のストアド プロシージャが、AdventureWorks2025 データベースに対して配置されたデータベース アプリケーションに存在するとします。

CREATE PROCEDURE Sales.GetSalesOrderByCountry (@Country_region nvarchar(60))  
AS  
BEGIN  
    SELECT *  
    FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c,   
        Sales.SalesTerritory AS t  
    WHERE h.CustomerID = c.CustomerID  
        AND c.TerritoryID = t.TerritoryID  
        AND CountryRegionCode = @Country_region  
END;  

このストアド プロシージャは @Country_region = N'AU' (オーストラリア) 用にコンパイルおよび最適化されているものとします。 ただし、オーストラリアでは比較的少数の販売注文しか発生していないため、多くの販売注文が発生している国やリージョンのパラメーター値を使用してクエリを実行するとパフォーマンスが低下します。 販売注文数が最も多いのは米国なので、 @Country_region = N'US' 用に生成されたクエリ プランのパフォーマンスは、 @Country_region パラメーターにどの値を使用しても低下しません。

ストアド プロシージャを変更して OPTIMIZE FOR クエリ ヒントをクエリに追加することで、この問題に対処できます。 ただし、ストアド プロシージャは配置済みアプリケーション内にあるので、アプリケーション コードを直接変更することはできません。 代わりに、 AdventureWorks2025 データベースに次のプラン ガイドを作成できます。

sp_create_plan_guide   
@name = N'Guide1',  
@stmt = N'SELECT *FROM Sales.SalesOrderHeader AS h,  
        Sales.Customer AS c,  
        Sales.SalesTerritory AS t  
        WHERE h.CustomerID = c.CustomerID   
            AND c.TerritoryID = t.TerritoryID  
            AND CountryRegionCode = @Country_region',  
@type = N'OBJECT',  
@module_or_batch = N'Sales.GetSalesOrderByCountry',  
@params = NULL,  
@hints = N'OPTION (OPTIMIZE FOR (@Country_region = N''US''))';  

sp_create_plan_guide ステートメントの指定したクエリが実行されると、そのクエリは最適化される前に変更され、 OPTIMIZE FOR (@Country = N''US'') 句が含められます。

SQL プラン ガイド

SQL プラン ガイドは、スタンドアロン Transact-SQL ステートメントのコンテキストで実行されるクエリと、データベース オブジェクトの一部ではないバッチと一致します。 また、SQL ベースのプラン ガイドを使用して、指定した形式にパラメーター化されたクエリを照合することもできます。 SQL プラン ガイドは、スタンドアロン Transact-SQL ステートメントおよびバッチに適用されます。 これらのステートメントは、よく sp_executesql システム ストアド プロシージャを使用してアプリケーションから送信されます。 たとえば、次のスタンドアロン バッチについて考えてみましょう。

SELECT TOP 1 * FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC;  

このクエリに並列実行プランが生成されないようにするには、次のプラン ガイドを作成し、 MAXDOP パラメーターで 1 クエリ ヒントを @hints に設定します。

sp_create_plan_guide   
@name = N'Guide2',   
@stmt = N'SELECT TOP 1 * FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC',  
@type = N'SQL',  
@module_or_batch = NULL,   
@params = NULL,   
@hints = N'OPTION (MAXDOP 1)';  

別の例として、sp_executesql を使用して送信された次の SQL ステートメントについて考えます。

exec sp_executesql N'SELECT * FROM Sales.SalesOrderHeader
where SalesOrderID =  @so_id', N'@so_id int', @so_id = 43662;  

このクエリの毎回の実行について一意のプランを作成するには、次のプラン ガイドを作成し、OPTION (RECOMPILE) クエリ ヒントを @hints パラメーターで使用します。

exec sp_create_plan_guide   
@name = N'PlanGuide1_SalesOrders',   
@stmt = N'SELECT * FROM Sales.SalesOrderHeader
where SalesOrderID =  @so_id',
@type = N'SQL',  
@module_or_batch = NULL,   
@params = N'@so_id int',   
@hints = N'OPTION (recompile)';

SQL プラン ガイドは、PARAMETERIZATION データベース オプションが FORCED に SET されている場合や、パラメーター化されたクエリ クラスを指定する TEMPLATE プラン ガイドが作成された場合に、同じ形式にパラメーター化するクエリにも作成できます。

TEMPLATE プラン ガイド

TEMPLATE プラン ガイドでは、指定した形式にパラメーター化されたスタンドアロン クエリが照合されます。 これらのプラン ガイドは、クエリクラスのデータベースの現在の PARAMETERIZATION データベース SET オプションをオーバーライドするために使用されます。

TEMPLATE プラン ガイドは、次のいずれかの状況で作成できます。

• PARAMETERIZATION データベース オプションは FORCED に SET されていますが、 単純パラメーター化の規則に従ってコンパイルするクエリがあります。

• PARAMETERIZATION データベース オプションは SIMPLE (既定の設定) に SET されていますが、クエリのクラスで 強制パラメーター化 を試行する必要があります。

プラン ガイドの照合要件

プラン ガイドは、作成されたデータベースに限定されます。 したがって、クエリの実行時に使用されているデータベース内に存在するプラン ガイドだけをクエリと照合できます。 たとえば、 AdventureWorks2025 が現在のデータベースの場合に次のクエリを実行するとします。

SELECT FirstName, LastName FROM Person.Person;

この場合、 AdventureWorks2025 データベース内のプラン ガイドだけがこのクエリと照合されます。 ただし、 AdventureWorks2025 が現在のデータベースの場合に、次のステートメントを実行すると結果が異なります。

USE DB1; 
SELECT FirstName, LastName FROM Person.Person;

クエリは DB1 のコンテキストで実行されているため、DB1 内のプラン ガイドのみがこのクエリの照合対象となります。

SQL ベースのプラン ガイドまたは TEMPLATE ベースのプラン ガイドでは、SQL Server により、引数 @module_or_batch と引数 @params の２つの値が文字単位で比較されてクエリと照合されます。 つまり、SQL Server で受け取られる実際のバッチ テキストと厳密に同じテキストを指定する必要があります。

@type = 'SQL' で、@module_or_batch が NULL に設定されている場合、@module_or_batch の値は @stmt の値に設定されます。これは、statement_text の値を、SQL Server に送信されるものと文字単位で完全に同一の形式で指定する必要があることを意味します。 この適合を容易にするために内部変換は実行されません。

通常の (SQL または OBJECT) プラン ガイドと TEMPLATE プラン ガイドの両方をステートメントに適用可能な場合、通常のプラン ガイドのみが使用されます。

プランキャッシュに対するプランガイドの効果

モジュールにプラン ガイドを作成すると、そのモジュールのクエリ プランがプラン キャッシュから削除されます。 バッチに OBJECT 型または SQL 型のプラン ガイドを作成すると、同じハッシュ値を持つバッチのクエリ プランが削除されます。 TEMPLATE 型のプラン ガイドを作成すると、単一ステートメントのバッチがデータベース内のプラン キャッシュからすべて削除されます。

関連タスク

参照

sp_create_plan_guide(Transact-SQL)
sp_create_plan_guide_from_handle(Transact-SQL)
sp_control_plan_guide(Transact-SQL)
sys.plan_guides(Transact-SQL)
sys.fn_validate_plan_guide (Transact-SQL)