sp_create_plan_guide_from_handle (Transact-SQL)

適用対象: SQL Server

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

Transact-SQL 構文表記規則

構文

sp_create_plan_guide_from_handle
    [ @name = ] N'name'
    , [ @plan_handle = ] plan_handle
    [ , [ @statement_start_offset = ] statement_start_offset ]
[ ; ]

引数

[ @name = ] N'name'

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

[ @plan_handle = ] plan_handle

プラン キャッシュのバッチを識別します。 @plan_handle は varbinary(64)で、既定値はありません。 @plan_handle は、 sys.dm_exec_query_stats 動的管理ビューから取得できます。

[ @statement_start_offset = ] statement_start_offset

指定した @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 プラン表示は、プラン キャッシュから結果のプラン ガイドに文字ごとに取得されます (クエリに渡されるリテラル値を含む)。 これらのテキスト文字列には、データベースのメタデータに格納される機密情報を含めることができます。 適切なアクセス許可を持つユーザーは、SQL Server Management Studio の sys.plan_guides カタログ ビューと Plan Guide Properties ダイアログ ボックスを使用して、この情報を表示できます。 機密情報がプラン ガイドを通じて開示されないようにするには、プラン キャッシュから作成されたプラン ガイドを確認することをお勧めします。

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

sp_create_plan_guideと同様に、sp_create_plan_guide_from_handleは、対象のバッチまたはモジュールのクエリ プランをプラン キャッシュから削除します。 これは、新しいプラン ガイドがすべてのユーザーによって使用されるようにするための措置です。 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
INNER 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 型であることを示します。 Ad hocやPreparedなどのその他の値は、プラン ガイドが SQL 型であることを示します。

例

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

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

USE AdventureWorks2022;
GO

SELECT WorkOrderID,
    p.Name,
    OrderQty,
    DueDate
FROM Production.WorkOrder AS w
INNER 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 パラメーターを指定して、バッチ内の最初と 3 番目のステートメントのプラン ガイドが作成されます。 この例の最後のステートメントは、プラン ガイドが存在することを確認します。

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

関連するコンテンツ

• データベース エンジン ストアド プロシージャ (Transact-SQL)
• sys.dm_exec_query_stats (Transact-SQL)
• プラン ガイド
• sp_create_plan_guide (Transact-SQL)
• sys.dm_exec_sql_text (Transact-SQL)
• sys.dm_exec_text_query_plan (Transact-SQL)
• sp_control_plan_guide (Transact-SQL)