sp_create_plan_guide_from_handle (TRANSACT-SQL)sp_create_plan_guide_from_handle (Transact-SQL)

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

プラン キャッシュ内のクエリ プランから 1 つ以上のプラン ガイドを作成します。Creates one or more plan guides from a query plan in the plan cache. このストアド プロシージャを使用するには、クエリ オプティマイザーは常に指定されたクエリの特定のクエリ プランを使用するようにします。You can use this stored procedure to ensure the query optimizer always uses a specific query plan for a specified query. プラン ガイドの詳細については、「 Plan Guides」を参照してください。For more information about plan guides, see Plan Guides.

トピック リンク アイコン Transact-SQL 構文表記規則Topic link icon Transact-SQL Syntax Conventions

構文Syntax

  
sp_create_plan_guide_from_handle [ @name = ] N'plan_guide_name'  
    , [ @plan_handle = ] plan_handle  
    , [ [ @statement_start_offset = ] { statement_start_offset | NULL } ]  

引数Arguments

[ @name =] N'plan_guide_name'[ @name = ] N'plan_guide_name'
プラン ガイドの名前を指定します。Is the name of the plan guide. プラン ガイド名は現在のデータベースに対して有効です。Plan guide names are scoped to the current database. plan_guide_nameの規則に従っている必要があります識別子番号記号で始めることはできません (#)。plan_guide_name must comply with the rules for identifiers and cannot start with the number sign (#). 最大長plan_guide_nameは 124 文字です。The maximum length of plan_guide_name is 124 characters.

[ @plan_handle = ] plan_handle[ @plan_handle = ] plan_handle
プラン キャッシュのバッチを識別します。Identifies a batch in the plan cache. plan_handlevarbinary (64) します。plan_handle is varbinary(64). plan_handleから取得できます、 sys.dm_exec_query_stats動的管理ビュー。plan_handle can be obtained from the sys.dm_exec_query_stats dynamic management view.

[ @statement_start_offset = ] { statement_start_offset | NULL } ][ @statement_start_offset = ] { statement_start_offset | NULL } ]
指定したバッチ内のステートメントの開始位置を識別するplan_handleします。Identifies the starting position of the statement within the batch of the specified plan_handle. statement_start_offsetint、既定値は NULL です。statement_start_offset is int, with a default of NULL.

ステートメント オフセットは statement_start_offset 列に対応する、 sys.dm_exec_query_stats動的管理ビュー。The statement offset corresponds to the statement_start_offset column in the sys.dm_exec_query_stats dynamic management view.

NULL を指定した場合や、ステートメント オフセットを指定しない場合は、指定したプラン ハンドルのクエリ プランを使用してバッチ内の各ステートメントに対してプラン ガイドが作成されます。When NULL is specified or a statement offset is not specified, a plan guide is created for each statement in the batch using the query plan for the specified plan handle. 結果のプラン ガイドでは、プラン ガイドの USE PLAN クエリ ヒントを使用して、特定のプランを強制的に使用すると同じです。The resulting plan guides are equivalent to plan guides that use the USE PLAN query hint to force the use of a specific plan.

コメントRemarks

すべての種類のステートメントに対してプラン ガイドを作成できるわけではありません。A plan guide cannot be created for all statement types. プラン ガイドを作成できないステートメントがバッチ内にあった場合、そのステートメントは無視されて、バッチ内の次のステートメントが処理されます。If a plan guide cannot be created for a statement in the batch, the stored procedure ignores the statement and continues to the next statement in the batch. ステートメントでは、同じバッチで複数回が発生する場合は、最後に出現する位置のプランが有効になり、ステートメントより前のプランが無効になっています。If a statement occurs multiple times in the same batch, the plan for the last occurrence is enabled and previous plans for the statement are disabled. プラン ガイドで、バッチ内のステートメントを使用されず、エラー 10532 が発生し、ステートメントは失敗します。If no statements in the batch can be used in a plan guide, error 10532 is raised and the statement fails. このエラーを回避するため、常に sys.dm_exec_query_stats 動的管理ビューからプラン ハンドルを取得することをお勧めします。We recommend that you always obtain the plan handle from the sys.dm_exec_query_stats dynamic management view to help avoid the possibility of this error.

重要

sp_create_plan_guide_from_handle では、プラン キャッシュに含まれているとおりのプランに基づいてプラン ガイドが作成されます。sp_create_plan_guide_from_handle creates plan guides based on plans as they appear in the plan cache. したがって、バッチ テキスト、Transact-SQLTransact-SQL ステートメント、および XML プラン表示が、プラン キャッシュから結果のプラン ガイドに文字単位で (クエリに渡されたリテラル値も含め) 取り込まれます。This means that the batch text, Transact-SQLTransact-SQL statements, and XML Showplan are taken character-by-character (including any literal values passed to the query) from the plan cache into the resulting plan guide. これらのテキスト文字列には、データベースのメタデータに格納し、機密情報を含めることができます。These text strings may contain sensitive information that is then stored in the metadata of the database. 適切なアクセス許可を持つユーザーは、sys.plan_guides カタログ ビューを使用してこの情報を表示することができます、プラン ガイド プロパティ ダイアログ ボックスでSQL Server Management StudioSQL Server Management Studioします。Users with appropriate permissions can view this information by using the sys.plan_guides catalog view and the Plan Guide Properties dialog box in SQL Server Management StudioSQL Server Management Studio. プラン ガイドから機密情報が公開されないことを確認するには、プラン キャッシュから作成されたプラン ガイドのレビューをお勧めします。To ensure that sensitive information is not disclosed through a plan guide, we recommend reviewing the plan guides created from the plan cache.

クエリ プラン内の複数のステートメントに対してプラン ガイドを作成します。Creating Plan Guides for Multiple Statements Within a Query Plan

sp_create_plan_guide_from_handle では、sp_create_plan_guide と同様に、対象となるバッチやモジュールのクエリ プランがプラン キャッシュから削除されます。Like sp_create_plan_guide, sp_create_plan_guide_from_handle removes the query plan for the targeted batch or module from the plan cache. これは、新しいプラン ガイドがすべてのユーザーによって使用されるようにするための措置です。This is done to ensure that all users begin using the new plan guide. 1 つのクエリ プラン内で複数のステートメントに対してプラン ガイドを作成するときに、明示的なトランザクションですべてのプラン ガイドを作成して、キャッシュからプランの削除を延期することができます。When creating a plan guide for multiple statements within a single query plan, you can postpone the removal of the plan from the cache by creating all the plan guides in an explicit transaction. これにより、そのトランザクションが完了して、指定した各ステートメントのプラン ガイドが作成されるまで、プランがキャッシュに保持されます。This method allows the plan to remain in the cache until the transaction is complete and a plan guide for each specified statement is created. 例 B を参照してください。See Example B.

アクセス許可Permissions

VIEW SERVER STATE 権限が必要です。Requires VIEW SERVER STATE permission. その他、sp_create_plan_guide_from_handle を使用して作成するプラン ガイドごとに必要な個々の権限があります。In addition, individual permissions are required for each plan guide that is created by using sp_create_plan_guide_from_handle. 型のプラン ガイドを作成するには、オブジェクトには、参照先オブジェクトに対する ALTER 権限が必要です。To create a plan guide of type OBJECT requires ALTER permission on the referenced object. SQL または TEMPLATE 型のプラン ガイドを作成するには、現在のデータベースに対する ALTER 権限が必要です。To create a plan guide of type SQL or TEMPLATE requires ALTER permission on the current database. 作成されるプラン ガイドの種類を確認するのには、次のクエリを実行します。To determine the plan guide type that will be created, run the following query:

SELECT cp.plan_handle, sql_handle, st.text, objtype   
FROM sys.dm_exec_cached_plans AS cp  
JOIN sys.dm_exec_query_stats AS qs ON cp.plan_handle = qs.plan_handle  
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st;  

プラン ガイドを作成するステートメントを含む行を調べて、objtype結果セット内の列。In the row that contains the statement for which you are creating the plan guide, examine the objtype column in the result set. Procプラン ガイドは OBJECT 型を示します。A value of Proc indicates the plan guide is of type OBJECT. その他の値などAdHocまたはPreparedSQL 型のプラン ガイドを示します。Other values such as AdHoc or Prepared indicate the plan guide is of type SQL.

使用例Examples

A.A. プラン キャッシュ内のクエリ プランからプラン ガイドを作成するCreating a plan guide from a query plan in the plan cache

次の例では、プラン キャッシュからクエリ プランを指定して単一の SELECT ステートメントのプラン ガイドを作成します。The following example creates a plan guide for a single SELECT statement by specifying a query plan from the plan cache. まず、プラン ガイドを作成する単純な SELECT ステートメントを実行します。The example begins by executing a simple SELECT statement for which the plan guide will be created. 次に、動的管理ビューの sys.dm_exec_sql_text および sys.dm_exec_text_query_plan を使用してこのクエリのプランを調べます。The plan for this query is examined by using the sys.dm_exec_sql_text and sys.dm_exec_text_query_plan dynamic management views. プラン ガイドは、クエリに関連付けられているプラン キャッシュ内のクエリ プランを指定することで、クエリの作成されます。The plan guide is then created for the query by specifying the query plan in the plan cache that is associated with the query. この例の最後のステートメントは、プラン ガイドが存在することを確認します。The final statement in the example verifies that the plan guide exists.

USE AdventureWorks2012;  
GO  
SELECT WorkOrderID, p.Name, OrderQty, DueDate  
FROM Production.WorkOrder AS w   
JOIN Production.Product AS p ON w.ProductID = p.ProductID  
WHERE p.ProductSubcategoryID > 4  
ORDER BY p.Name, DueDate;  
GO  
-- Inspect the query plan by using dynamic management views.  
SELECT * FROM sys.dm_exec_query_stats AS qs  
CROSS APPLY sys.dm_exec_sql_text(sql_handle)  
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp  
WHERE text LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%';  
GO  
-- Create a plan guide for the query by specifying the query plan in the plan cache.  
DECLARE @plan_handle varbinary(64);  
DECLARE @offset int;  
SELECT @plan_handle = plan_handle, @offset = qs.statement_start_offset  
FROM sys.dm_exec_query_stats AS qs  
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st  
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp  
WHERE text LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%';  
  
EXECUTE sp_create_plan_guide_from_handle   
    @name =  N'Guide1',  
    @plan_handle = @plan_handle,  
    @statement_start_offset = @offset;  
GO  
-- Verify that the plan guide is created.  
SELECT * FROM sys.plan_guides  
WHERE scope_batch LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%';  
GO  

B.B. 複数のステートメントで構成されるバッチに対して複数のプラン ガイドを作成するCreating multiple plan guides for a multistatement batch

次の例では、複数ステートメントのバッチ内の 2 つのステートメントに対してプラン ガイドを作成します。The following example creates a plan guide for two statements within a multistatement batch. プラン ガイドが、明示的なトランザクション内で作成されるため、最初のプラン ガイドを作成した後、バッチのクエリ プランはプラン キャッシュから削除されません。The plan guides are created within an explicit transaction so that the query plan for the batch is not removed from the plan cache after the first plan guide is created. まず、複数のステートメントで構成されるバッチを実行します。The example begins by executing a multistatement batch. バッチのプランは動的管理ビューを使用して調べます。The plan for the batch is examined by using dynamic management views. バッチ内の各ステートメントの行が返されることに注意してください。Notice that a row for each statement in the batch is returned. 指定して、バッチ内の最初と 3 番目のステートメントのプラン ガイドが作成し、@statement_start_offsetパラメーター。A plan guide is then created for the first and third statements in the batch by specifying the @statement_start_offset parameter. 最後のステートメントの例では、プラン ガイドが存在することを確認します。The final statement in the example verifies that the plan guides exist.

USE AdventureWorks2012;
GO
SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4;
SELECT * FROM Person.Address;
SELECT * FROM Production.Product WHERE ProductSubcategoryID > 10;
GO

-- Examine the query plans for this batch
SELECT * FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
WHERE text LIKE N'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4%';
GO

-- Create plan guides for the first and third statements in the batch by specifying the statement offsets.
BEGIN TRANSACTION

DECLARE @plan_handle varbinary(64);
DECLARE @offset int;

SELECT @plan_handle = plan_handle, @offset = qs.statement_start_offset
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
WHERE text LIKE N'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4%'
AND SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
                        ((CASE statement_end_offset 
                              WHEN -1 THEN DATALENGTH(st.text)
                              ELSE qs.statement_end_offset END 
                              - qs.statement_start_offset)/2) + 1)  like 'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4%'

EXECUTE sp_create_plan_guide_from_handle 
    @name =  N'Guide_Statement1_only',
    @plan_handle = @plan_handle,
    @statement_start_offset = @offset;

SELECT @plan_handle = plan_handle, @offset = qs.statement_start_offset
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
WHERE text LIKE N'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4%'
AND SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
                        ((CASE statement_end_offset 
                              WHEN -1 THEN DATALENGTH(st.text)
                              ELSE qs.statement_end_offset END 
                              - qs.statement_start_offset)/2) + 1)  like 'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 10%'

EXECUTE sp_create_plan_guide_from_handle 
    @name =  N'Guide_Statement3_only',
    @plan_handle = @plan_handle,
    @statement_start_offset = @offset;

COMMIT TRANSACTION
GO

-- Verify the plan guides are created.
SELECT * FROM sys.plan_guides;
GO

関連項目See Also

データベース エンジン ストアド プロシージャ(TRANSACT-SQL) Database Engine Stored Procedures (Transact-SQL)
sys.dm_exec_query_stats (TRANSACT-SQL) sys.dm_exec_query_stats (Transact-SQL)
プラン ガイド Plan Guides
sp_create_plan_guide (Transact-SQL) sp_create_plan_guide (Transact-SQL)
sys.dm_exec_sql_text (TRANSACT-SQL) sys.dm_exec_sql_text (Transact-SQL)
sys.dm_exec_text_query_plan (TRANSACT-SQL) sys.dm_exec_text_query_plan (Transact-SQL)
sp_control_plan_guide (Transact-SQL)sp_control_plan_guide (Transact-SQL)