sp_create_plan_guide_from_handle (Transact-SQL)

[アーティクル]
05/23/2023

適用対象:SQL Server

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

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 型であることを示します。またはなどのAdHocPreparedその他の値は、プランガイドが SQL 型であることを示します。

例

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

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

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)

sp_create_plan_guide_from_handle (Transact-SQL)

構文

引数

解説

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

アクセス許可

例

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

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

参照

フィードバック

フィードバック

その他のリソース

sp_create_plan_guide_from_handle (Transact-SQL)

構文

引数

解説

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

アクセス許可

例

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

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

参照

フィードバック

フィードバック

その他のリソース

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

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

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