プラン ガイドPlan Guides

適用対象: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

SQL ServerSQL Serverの実際のクエリのテキストを直接変更することが不可能な場合や望ましくない場合に、プラン ガイドを使用してクエリのパフォーマンスを最適化することができます。Plan guides let you optimize the performance of queries when you cannot or do not want to directly change the text of the actual query in SQL ServerSQL Server. プラン ガイドは、クエリ ヒントまたは固定クエリ プランをクエリにアタッチすることにより、クエリの最適化を促します。Plan guides influence the optimization of queries by attaching query hints or a fixed query plan to them. プラン ガイドは、サード パーティ ベンダーが提供するデータベース アプリケーションのクエリの小さなサブセットで、期待どおりのパフォーマンスが得られない場合に役に立ちます。Plan guides can be useful when a small subset of queries in a database application provided by a third-party vendor are not performing as expected. プラン ガイドでは、最適化する Transact-SQL ステートメントのほか、使用するクエリ ヒントを含む OPTION 句またはクエリの最適化に使用する特定のクエリ プランのいずれかを指定します。In the plan guide, you specify the Transact-SQL statement that you want optimized and either an OPTION clause that contains the query hints you want to use or a specific query plan you want to use to optimize the query. クエリが実行されると、 SQL ServerSQL Server により Transact-SQL ステートメントがプラン ガイドと照合され、実行時にクエリに OPTION 句がアタッチされるか、指定されたクエリ プランが使用されます。When the query executes, SQL ServerSQL Server matches the Transact-SQL statement to the plan guide and attaches the OPTION clause to the query at run time or uses the specified query plan.

作成できるプラン ガイドの総数の上限は、使用可能なシステム リソースによって決まります。The total number of plan guides you can create is limited only by available system resources. ただし、プラン ガイドは、ミッションクリティカルなクエリのパフォーマンスの向上と安定化を図る目的にのみ使用する必要があります。Nevertheless, plan guides should be limited to mission-critical queries that are targeted for improved or stabilized performance. プラン ガイドの使用により配置済みのアプリケーションのクエリ負荷の多くが影響を受けることがないようにしてください。Plan guides should not be used to influence most of the query load of a deployed application.

注意

プラン ガイドは、 MicrosoftMicrosoftSQL ServerSQL Serverのすべてのエディションで使用できるわけではありません。Plan guides cannot be used in every edition of MicrosoftMicrosoftSQL ServerSQL Server. SQL ServerSQL Serverの各エディションでサポートされる機能の一覧については、「 SQL Server 2016 の各エディションでサポートされる機能」を参照してください。For a list of features that are supported by the editions of SQL ServerSQL Server, see Features Supported by the Editions of SQL Server 2016. プラン ガイドはどのエディションでも表示できます。Plan guides are visible in any edition. また、プラン ガイドを含むデータベースは、どのエディションに対してもアタッチできます。You can also attach a database that contains plan guides to any edition. アップグレード済みのバージョンの SQL ServerSQL Serverにデータベースを復元またはアタッチした場合、プラン ガイドはまったく影響を受けません。Plan guides remain intact when you restore or attach a database to an upgraded version of SQL ServerSQL Server.

プラン ガイドの種類Types of Plan Guides

次の種類のプラン ガイドを作成できます。The following types of plan guides can be created.

OBJECT プラン ガイドOBJECT plan guide

OBJECT プラン ガイドでは、 Transact-SQLTransact-SQL ストアド プロシージャ、スカラー ユーザー定義関数、複数ステートメント テーブル値ユーザー定義関数、および DML トリガーのコンテキストで実行されるクエリが照合されます。An OBJECT plan guide matches queries that execute in the context of Transact-SQLTransact-SQL stored procedures, scalar user-defined functions, multi-statement table-valued user-defined functions, and DML triggers.

@Country_region パラメーターを受け取る次のストアド プロシージャが、AdventureWorks2012AdventureWorks2012 データベースに対して配置されたデータベース アプリケーションに存在するとします。Suppose the following stored procedure, which takes the @Country_region parameter, is in a database application that is deployed against the AdventureWorks2012AdventureWorks2012 database:

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' (オーストラリア) 用にコンパイルおよび最適化されているものとします。Assume that this stored procedure has been compiled and optimized for @Country_region = N'AU' (Australia). ただし、オーストラリアでは比較的少数の販売注文しか発生していないため、多くの販売注文が発生している国のパラメーター値を使用してクエリを実行するとパフォーマンスが低下します。However, because there are relatively few sales orders that originate from Australia, performance decreases when the query executes using parameter values of countries with more sales orders. 販売注文数が最も多いのは米国なので、 @Country_region = N'US' 用に生成されたクエリ プランのパフォーマンスは、 @Country_region パラメーターにどの値を使用しても低下しません。Because the most sales orders originate in the United States, a query plan that is generated for @Country_region = N'US' would likely perform better for all possible values of the @Country_region parameter.

ストアド プロシージャを変更して OPTIMIZE FOR クエリ ヒントをクエリに追加することで、この問題に対処できます。You could address this problem by modifying the stored procedure to add the OPTIMIZE FOR query hint to the query. ただし、ストアド プロシージャは配置済みアプリケーション内にあるので、アプリケーション コードを直接変更することはできません。However, because the stored procedure is in a deployed application, you cannot directly modify the application code. 代わりに、 AdventureWorks2012AdventureWorks2012 データベースに次のプラン ガイドを作成できます。Instead, you can create the following plan guide in the AdventureWorks2012AdventureWorks2012 database.

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'') 句が含められます。When the query specified in the sp_create_plan_guide statement executes, the query is modified before optimization to include the OPTIMIZE FOR (@Country = N''US'') clause.

SQL プラン ガイドSQL plan guide

SQL プラン ガイドでは、データベース オブジェクトの一部ではないスタンドアロン Transact-SQLTransact-SQL ステートメントとスタンドアロン バッチのコンテキストで実行されるクエリが照合されます。An SQL plan guide matches queries that execute in the context of stand-alone Transact-SQLTransact-SQL statements and batches that are not part of a database object. また、SQL ベースのプラン ガイドを使用して、指定した形式にパラメーター化されたクエリを照合することもできます。SQL-based plan guides can also be used to match queries that parameterize to a specified form. SQL プラン ガイドは、スタンドアロン Transact-SQLTransact-SQL ステートメントとスタンドアロン バッチに適用されます。SQL plan guides apply to stand-alone Transact-SQLTransact-SQL statements and batches. これらのステートメントは、よく sp_executesql システム ストアド プロシージャを使用してアプリケーションから送信されます。Frequently, these statements are submitted by an application by using the sp_executesql system stored procedure. たとえば、次のスタンドアロン バッチについて考えてみましょう。For example, consider the following stand-alone batch:

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

このクエリに並列実行プランが生成されないようにするには、次のプラン ガイドを作成し、 MAXDOP パラメーターで 1 クエリ ヒントを @hints に設定します。To prevent a parallel execution plan from being generated on this query, create the following plan guide and set the MAXDOP query hint to 1 in the @hints parameter.

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 ステートメントについて考えます。As another example, consider the following SQL statement submitted using sp_executesql.

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

このクエリの毎回の実行について一意のプランを作成するには、次のプラン ガイドを作成し、OPTION (RECOMPILE) クエリ ヒントを @hints パラメーターで使用します。To create a unique plan for every execution of this query, create the following plan guide and use the OPTION (RECOMPILE) query hint in the @hints parameter.

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

重要

@module_or_batch ステートメントの @params 引数と sp_create_plan guide 引数に指定する値は、実際のクエリで送信される、対応するテキストと一致している必要があります。The values that are supplied for the @module_or_batch and @params arguments of the sp_create_plan guide statement must match the corresponding text submitted in the actual query. 詳細については、「 sp_create_plan_guide (Transact-SQL) ステートメントの SQL Server Profiler を使用したプラン ガイドの作成とテストの実際のクエリのテキストを直接変更することが不可能な場合や望ましくない場合に、プラン ガイドを使用してクエリのパフォーマンスを最適化することができます。For more information, see sp_create_plan_guide (Transact-SQL) and Use SQL Server Profiler to Create and Test Plan Guides.

PARAMETERIZATION データベース オプションを FORCED に設定するか、またはクエリのクラスをパラメーター化するように指定して TEMPLATE プラン ガイドを作成すると、同じ形式にパラメーター化されるクエリに SQL プラン ガイドを作成することもできます。SQL plan guides can also be created on queries that parameterize to the same form when the PARAMETERIZATION database option is SET to FORCED, or when a TEMPLATE plan guide is created specifying that a parameterized class of queries.

TEMPLATE プラン ガイドTEMPLATE plan guide

TEMPLATE プラン ガイドでは、指定した形式にパラメーター化されたスタンドアロン クエリが照合されます。A TEMPLATE plan guide matches stand-alone queries that parameterize to a specified form. これらのプラン ガイドは、クエリのクラスのデータベースの現在の PARAMETERIZATION データベース SET オプションをオーバーライドするために使用されます。These plan guides are used to override the current PARAMETERIZATION database SET option of a database for a class of queries.

TEMPLATE プラン ガイドは、次のいずれかの状況で作成できます。You can create a TEMPLATE plan guide in either of the following situations:

  • PARAMETERIZATION データベース オプションを FORCED に設定したが、簡易パラメーター化のルールに従ってコンパイルするクエリがある場合。The PARAMETERIZATION database option is SET to FORCED, but there are queries you want compiled according to the rules of Simple Parameterization.

  • PARAMETERIZATION データベース オプションを SIMPLE (既定値) に設定したが、特定のクラスのクエリについて強制パラメーター化が必要である場合。The PARAMETERIZATION database option is SET to SIMPLE (the default setting), but you want Forced Parameterization to be tried on a class of queries.

プラン ガイドの照合要件Plan Guide Matching Requirements

プラン ガイドの範囲は、そのガイドが作成されているデータベースです。Plan guides are scoped to the database in which they are created. したがって、クエリの実行時に使用されているデータベース内に存在するプラン ガイドだけをクエリと照合できます。Therefore, only plan guides that are in the database that is current when a query executes can be matched to the query. たとえば、 AdventureWorks2012AdventureWorks2012 が現在のデータベースの場合に次のクエリを実行するとします。For example, if AdventureWorks2012AdventureWorks2012 is the current database and the following query executes:

SELECT FirstName, LastName FROM Person.Person;

この場合、 AdventureWorks2012AdventureWorks2012 データベース内のプラン ガイドだけがこのクエリと照合されます。Only plan guides in the AdventureWorks2012AdventureWorks2012 database are eligible to be matched to this query. ただし、 AdventureWorks2012AdventureWorks2012 が現在のデータベースの場合に、次のステートメントを実行すると結果が異なります。However, if AdventureWorks2012AdventureWorks2012 is the current database and the following statements are run:

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

この場合、 DB1 のコンテキストでこのクエリが実行されているので、 DB1内のプラン ガイドがこのクエリと照合されます。Only plan guides in DB1 are eligible to be matched to the query because the query is executing in the context of DB1.

SQL ベースのプラン ガイドまたは TEMPLATE ベースのプラン ガイドでは、SQL ServerSQL Server により、引数 @module_or_batch と引数 @params の値が文字単位で比較されてクエリと照合されます。For SQL- or TEMPLATE-based plan guides, SQL ServerSQL Server matches the values for the @module_or_batch and @params arguments to a query by comparing the two values character by character. つまり、 SQL ServerSQL Server で受け取られる実際のバッチ テキストと厳密に同じテキストを指定する必要があります。This means you must provide the text exactly as SQL ServerSQL Server receives it in the actual batch.

@type = 'SQL' で、@module_or_batch が NULL に設定されている場合、@module_or_batch の値は @stmt の値に設定されます。つまり、 statement_text の値は、 SQL ServerSQL Serverに送信するときと同一の形式で、同じ文字で指定する必要があります。When @type = 'SQL' and @module_or_batch is set to NULL, the value of @module_or_batch is set to the value of @stmt. This means that the value for statement_text must be provided in the identical format, character-for-character, as it is submitted to SQL ServerSQL Server. この適合を容易にするために内部変換は実行されません。No internal conversion is performed to facilitate this match.

通常の (SQL または OBJECT) プラン ガイドと TEMPLATE プラン ガイドの両方をステートメントに適用可能な場合、通常のプラン ガイドのみが使用されます。When both a regular (SQL or OBJECT) plan guide and a TEMPLATE plan guide can apply to a statement, only the regular plan guide will be used.

注意

プラン ガイドの作成対象のステートメントを含むバッチには、USE database ステートメントを含めることはできません。The batch that contains the statement on which you want to create a plan guide cannot contain a USE database statement.

プラン キャッシュに対するプラン ガイドの効果Plan Guide Effect on the Plan Cache

モジュールにプラン ガイドを作成すると、そのモジュールのクエリ プランがプラン キャッシュから削除されます。Creating a plan guide on a module removes the query plan for that module from the plan cache. バッチに OBJECT 型または SQL 型のプラン ガイドを作成すると、同じハッシュ値を持つバッチのクエリ プランが削除されます。Creating a plan guide of type OBJECT or SQL on a batch removes the query plan for a batch that has the same hash value. TEMPLATE 型のプラン ガイドを作成すると、単一ステートメントのバッチがデータベース内のプラン キャッシュからすべて削除されます。Creating a plan guide of type TEMPLATE removes all single-statement batches from the plan cache within that database.

タスクTask トピックTopic
プラン ガイドを作成する方法について説明します。Describes how to create a plan guide. 新しいプラン ガイドの作成Create a New Plan Guide
パラメーター化クエリ用のプラン ガイドを作成する方法について説明します。Describes how to create a plan guide for parameterized queries. パラメーター化クエリのプラン ガイドの作成Create a Plan Guide for Parameterized Queries
プラン ガイドを使用してクエリのパラメーター化動作を制御する方法について説明します。Describes how to control query parameterization behavior by using plan guides. プラン ガイドを使用したクエリのパラメーター化動作の指定Specify Query Parameterization Behavior by Using Plan Guides
プラン ガイドに固定クエリ プランを含める方法について説明します。Describes how to include a fixed query plan in a plan guide. プラン ガイドへの固定クエリ プランの適用Apply a Fixed Query Plan to a Plan Guide
プラン ガイドにクエリ ヒントを指定する方法について説明します。Describes how to specify query hints in a plan guide. プラン ガイドへのクエリ ヒントのアタッチAttach Query Hints to a Plan Guide
プラン ガイドのプロパティを表示する方法について説明します。Describes how to view plan guide properties. プラン ガイド プロパティの表示View Plan Guide Properties
プラン ガイドを作成およびテストするために SQL Server Profiler を使用する方法について説明します。Describes how to use SQL Server Profiler to create and test plan guides. SQL Server Profiler を使用したプラン ガイドの作成とテストUse SQL Server Profiler to Create and Test Plan Guides
プラン ガイドを検証する方法について説明します。Describes how to validate plan guides. アップグレード後のプラン ガイドの検証Validate Plan Guides After Upgrade

参照See Also

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