計画ガイド

プラン ガイドを使用すると、SQL Server 2014 で実際のクエリのテキストを直接変更できない場合や変更したくない場合に、クエリのパフォーマンスを最適化できます。 プラン ガイドは、クエリ ヒントまたは固定クエリ プランをアタッチすることで、クエリの最適化に影響を与えます。 プラン ガイドは、サード パーティベンダーが提供するデータベース アプリケーションのクエリの小さなサブセットが期待どおりに動作しない場合に役立ちます。 プラン ガイドでは、最適化する Transact-SQL ステートメントと、使用するクエリ ヒントを含む OPTION 句、またはクエリを最適化するために使用する特定のクエリ プランを指定します。 クエリが実行されると、SQL Server はプラン ガイドに Transact-SQL ステートメントを照合し、実行時に OPTION 句をクエリにアタッチするか、指定されたクエリ プランを使用します。

作成できるプラン ガイドの合計数は、使用可能なシステム リソースによってのみ制限されます。 ただし、プラン ガイドは、パフォーマンスの向上または安定化を目的とするミッション クリティカルなクエリに限定する必要があります。 プラン ガイドは、デプロイされたアプリケーションのクエリ負荷の大部分に影響を与えるために使用しないでください。

プラン ガイドの種類

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

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

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

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

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

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

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

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

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

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

プランガイドの適合要件

プラン ガイドは、作成されたデータベースにスコープが限定されます。 そのため、クエリの実行時に最新のデータベースにあるプラン ガイドのみをクエリと照合できます。 たとえば、 AdventureWorks2012 が現在のデータベースであり、次のクエリが実行される場合です。

SELECT FirstName, LastName FROM Person.Person;

AdventureWorks2012 データベース内のプラン ガイドのみが、このクエリと一致する資格があります。 ただし、 AdventureWorks2012 が現在のデータベースであり、次のステートメントが実行される場合は、次のステートメントが実行されます。

USE DB1;

SELECT FirstName, LastName FROM Person.Person;

クエリはDB1のコンテキストで実行されるため、クエリと照合できるのは、DB1のプラン ガイドだけです。

SQL ベースまたは TEMPLATE ベースのプラン ガイドの場合、SQL Server は、2 つの値を文字ごとに比較することで、 @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)
ハンドルから計画ガイドを作成する (Transact-SQL)
sp_control_plan_guide(Transact-SQL)
sys.plan_guides (Transact-SQL)
sys.fn_validate_plan_guide (Transact-SQL)