プラン ガイド

適用対象: SQL Server Azure SQL Database Azure SQL Managed Instance

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 パラメーターを受け取る次のストアド プロシージャが、AdventureWorks2022 データベースに対して配置されたデータベース アプリケーションに存在するとします。

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

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)';

PARAMETERIZATION データベース オプションを FORCED に設定するか、またはクエリのクラスをパラメーター化するように指定して TEMPLATE プラン ガイドを作成すると、同じ形式にパラメーター化されるクエリに SQL プラン ガイドを作成することもできます。

TEMPLATE プラン ガイド

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

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

• PARAMETERIZATION データベース オプションを FORCED に設定したが、簡易パラメーター化のルールに従ってコンパイルするクエリがある場合。

• PARAMETERIZATION データベース オプションを SIMPLE (既定値) に設定したが、特定のクラスのクエリについて強制パラメーター化が必要である場合。

プラン ガイドの照合要件

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

SELECT FirstName, LastName FROM Person.Person;

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

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 型のプラン ガイドを作成すると、単一ステートメントのバッチがデータベース内のプラン キャッシュからすべて削除されます。

Related Tasks

参照

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)