SQL Server: プランガイドを設定する際の流れをまとめてみた

特定のクエリの実行プランを固定化する際、ただ固定化するだけでなく、固定前後のクエリ実行時間のチェックなども大事だと思います。
そのあたりも踏まえて、プランガイドを設定する際の手順についてまとめてみました。

参考：sp_create_plan_guide_from_handle
参考：sp_control_plan_guide

1. クエリのplan_handle / start_offsetを取得

今回はストアドプロシージャの特定のステートメントの実行プランを固定化するシナリオを想定します。

実行プランの固定化にはplan_handleがあると便利なので、sys.dm_exec_query_statsを使ってplan_handleを取得します。また、ストアド全体のプランを固定する場合はこれだけで大丈夫ですが、特定のステートメントだけ固定したい場合はstart_offsetも取得します。

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 100
DB_NAME(qt.dbid) as database_name
,sql_handle
,plan_handle
,qs.statement_start_offset
--query text
,qt.TEXT as parent_query
,SUBSTRING(qt.TEXT, qs.statement_start_offset / 2, (
CASE
WHEN qs.statement_end_offset = - 1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset
) / 2) as statement
-- average
,total_worker_time / qs.execution_count / 1000 as average_CPU_time_ms
,total_elapsed_time / qs.execution_count / 1000 as average_duration_ms
,total_physical_reads * 8 / qs.execution_count / 1024 as average_physical_reads_mb
-- execution count
,qs.execution_count as execution_count
-- creation / execution time
,last_execution_time
,creation_time
-- total
,total_worker_time / 1000 as total_CPU_time_ms
,total_elapsed_time / 1000 as total_duration_ms
,total_physical_reads * 8 / 1024 as total_physical_reads_mb
-- query plan
,qp.query_plan -- プランもみたいときはコメント外す
FROM sys.dm_exec_query_stats qs
OUTER APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt -- クエリテキスト用
OUTER apply sys.dm_exec_query_plan(plan_handle) as qp -- プランプラン用
WHERE
qt.TEXT like '%ストアドプロシージャ名をここに%'

↑のようにplan_handleとstart_offsetが取得できました。
あわせて、ガイド固定後との比較用に以下のカラムの値も記録しておきます。

• average_CPU_time_ms
• average_duration_ms
• execution_count

2. 固定化

「sp_create_plan_guide_from_handle」を使ってプランを固定化します。

sp_create_plan_guide_from_handle @name = N'プランガイド名'
, @plan_handle = 0x05000000011231231000000001312312312000000
, @statement_start_offset = 1972

3. 確認

こちらを参考に、プランガイドの確認クエリを実行します。

SELECT plan_guide_id, msgnum, severity, state, message FROM sys.plan_guides OUTER APPLY fn_validate_plan_guide(plan_guide_id);

成功した場合は、↓のようにmessage is nullなレコードがINSERTされています。エラーとなった場合は何らかのエラーメッセージがmessageに出力されているので確認が必要です。

SSMSからもプランガイドが生成されていることが分かります。プロパティを確認すると、さらに詳細な情報が確認できます。

「ヒント」にXMLがかかれていますが、設定時に指定したplan_handleのプランで実行するように、クエリ実行時にヒント句でXMLを付与して実行する挙動のようです。

↓プランガイドをスクリプト化すると、ヒント句として付与されていることが確認できます。

再びsys.dm_exec_query_statsを使ってキャッシュされている実行プランを確認すると、プロパティを見た時にプランガイドが設定されていることを確認することができます。ここまで確認できれば、問題なくプランガイドが設定され、設定されたプランで実行されていると安心して判断することができます。

設定後に再度以下の3項目を取得し、大きく変化していないことも確認しておくと安心です。

• average_CPU_time_ms
• average_duration_ms
• execution_count

4. 戻したいとき

プランガイドを設定したDBをSSMSで展開して「プログラミング」→「プランガイド」の配下に作成されるプランガイドを削除すればOKです。

とにかく急いで無効化したい、といった場合は

EXEC sp_control_plan_guide N'DISABLE ALL';

を実行すればOKです。

5. 注意点

ストアドプロシージャの一部またはすべてのプランを固定した場合、そのストアドプロシージャをALTERするためには一度プランガイドを削除する必要があります。