• [アーティクル]

sp_create_plan_guide_from_handle (Transact-SQL)

プラン キャッシュ内のクエリ プランから 1 つ以上のプラン ガイドを作成します。このストアド プロシージャを使用すると、クエリ オプティマイザが指定のクエリに対して常に特定のクエリ プランを使用するように設定できます。プラン ガイドの詳細については、「プラン ガイドについて」を参照してください。

トピック リンク アイコンTransact-SQL 構文表記規則

構文

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

引数

  • [ @name = ] N'plan_guide_name'
    プラン ガイドの名前を指定します。プラン ガイド名は現在のデータベースに対して有効です。plan_guide_name は識別子の規則に従う必要があり、番号記号 (#) で始めることはできません。plan_guide_name の最大長は 124 文字です。

  • [ @plan_handle = ] plan_handle
    プラン キャッシュ内のバッチを指定します。plan_handle のデータ型は varbinary(64) です。plan_handle は、sys.dm_exec_query_stats 動的管理ビューから取得できます。

  • [ @statement_start_offset = ] { statement_start_offset | NULL } ]
    指定した plan_handle のバッチ内のステートメントの開始位置を指定します。statement_start_offset のデータ型は int で、既定値は NULL です。

    ステートメント オフセットは、sys.dm_exec_query_stats 動的管理ビューの statement_start_offset 列に対応します。

    NULL を指定した場合や、ステートメント オフセットを指定しない場合は、指定したプラン ハンドルのクエリ プランを使用してバッチ内の各ステートメントに対してプラン ガイドが作成されます。結果のプラン ガイドは、USE PLAN クエリ ヒントを使用して特定のプランを強制的に使用させるプラン ガイドと同等になります。

説明

すべての種類のステートメントに対してプラン ガイドを作成できるわけではありません。プラン ガイドを作成できないステートメントがバッチ内にあった場合、そのステートメントは無視されて、バッチ内の次のステートメントが処理されます。同じバッチ内に複数回出現するステートメントがあった場合は、最後に出現した箇所のプランが有効になり、それより前のプランは無効になります。プラン ガイドで使用できるステートメントがバッチ内になかった場合は、エラー 10532 が発生してステートメントが失敗します。このエラーを回避するため、常に sys.dm_exec_query_stats 動的管理ビューからプラン ハンドルを取得することをお勧めします。

セキュリティに関する注意セキュリティに関する注意

sp_create_plan_guide_from_handle では、プラン キャッシュに含まれているとおりのプランに基づいてプラン ガイドが作成されます。したがって、バッチ テキスト、Transact-SQL ステートメント、および XML プラン表示が、プラン キャッシュから結果のプラン ガイドに文字単位で (クエリに渡されたリテラル値も含め) 取り込まれます。これらのテキスト文字列に機密情報が含まれていた場合、それらはデータベースのメタデータに格納されます。ユーザーに適切な権限があれば、sys.plan_guides カタログ ビューや SQL Server Management Studio の [プラン ガイド プロパティ] ダイアログ ボックスを使用してこの情報を表示できます。プラン ガイドから機密情報が漏洩しないように、プラン キャッシュから作成されたプラン ガイドを確認することをお勧めします。

クエリ プラン内の複数のステートメントに対するプラン ガイドの作成

sp_create_plan_guide_from_handle では、sp_create_plan_guide と同様に、対象となるバッチやモジュールのクエリ プランがプラン キャッシュから削除されます。これは、新しいプラン ガイドがすべてのユーザーによって使用されるようにするための措置です。1 つのクエリ プラン内の複数のステートメントに対してプラン ガイドを作成する場合は、すべてのプラン ガイドを明示的なトランザクションの中で作成することによって、キャッシュからプランが削除されるのを遅らせることができます。これにより、そのトランザクションが完了して、指定した各ステートメントのプラン ガイドが作成されるまで、プランがキャッシュに保持されます。例 B を参照してください。

権限

VIEW_SERVER_STATE 権限が必要です。その他、sp_create_plan_guide_from_handle を使用して作成するプラン ガイドごとに必要な個々の権限があります。OBJECT 型のプラン ガイドを作成するには、参照先オブジェクトに対する ALTER 権限が必要です。SQL または TEMPLATE 型のプラン ガイドを作成するには、現在のデータベースに対する ALTER 権限が必要です。作成されるプラン ガイドの型を特定するには、次のクエリを実行します。

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 列を調べます。値が Proc の場合、そのプラン ガイドは OBJECT 型です。その他の値 (AdHoc や Prepared など) の場合は、SQL 型です。

例

A. プラン キャッシュ内のクエリ プランからプラン ガイドを作成する

次の例では、プラン キャッシュ内のクエリ プランを指定して 1 つの SELECT ステートメントのプラン ガイドを作成します。まず、プラン ガイドを作成する単純な SELECT ステートメントを実行します。次に、動的管理ビューの sys.dm_exec_sql_text および sys.dm_exec_text_query_plan を使用してこのクエリのプランを調べます。その後、クエリに関連付けられているプラン キャッシュ内のクエリ プランを指定して、クエリのプラン ガイドを作成します。この例の最後のステートメントは、プラン ガイドが存在することを確認します。

USE AdventureWorks2008R2;
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. 複数のステートメントで構成されるバッチに対して複数のプラン ガイドを作成する

次の例では、複数のステートメントで構成されるバッチ内の 2 つのステートメントに対してプラン ガイドを作成します。最初のプラン ガイドの作成後にバッチのクエリ プランがプラン キャッシュから削除されないように、明示的なトランザクションの中でプラン ガイドを作成します。まず、複数のステートメントで構成されるバッチを実行します。次に、動的管理ビューを使用してバッチのプランを調べます。バッチ内の各ステートメントに対して行が返されていることに注目してください。その後、@statement_start_offset パラメータを指定して、バッチ内の 1 番目と 3 番目のステートメントに対してプラン ガイドを作成します。この例の最後のステートメントは、プラン ガイドが存在することを確認します。

USE AdventureWorks2008R2;
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