sys.dm_exec_query_plan (Transact-SQL)

アドホック バッチ、ストアド プロシージャ、ユーザー定義関数などの各種 Transact-SQL バッチのクエリ プランは、プラン キャッシュと呼ばれるメモリ領域にキャッシュされます。キャッシュされたそれぞれのクエリ プランは、プラン ハンドルと呼ばれる一意識別子で識別されます。sys.dm_exec_query_plan 動的管理ビューでは、このプラン ハンドルを指定して、特定の Transact-SQL クエリまたはバッチの実行プランを取得できます。

Transact-SQL クエリまたは Transact-SQL バッチが、特定の SQL Server との接続において長時間実行されている場合は、このクエリやバッチの実行プランを取得して、遅延の原因を調べることができます。次の例では、実行速度の遅いクエリまたはバッチに対して XML プラン表示を取得する方法を示します。

注意
この例を実行するには、session_id と plan_handle の値を、使用しているサーバー固有の値に置き換えてください。

まず、sp_who ストアド プロシージャを使用して、クエリまたはバッチを実行しているプロセスのサーバー プロセス ID (SPID) を取得します。

USE master;
GO
exec sp_who;
GO

sp_who によって返される結果セットでは、SPID の値が 54 であることが示されます。sys.dm_exec_requests 動的管理ビューで、この SPID を使用して次のクエリを実行すると、プラン ハンドルを取得できます。

USE master;
GO
SELECT * FROM sys.dm_exec_requests
WHERE session_id = 54;
GO

sys.dm_exec_requests から返されるテーブルでは、実行速度の遅いクエリやバッチのプラン ハンドルが 0x06000100A27E7C1FA821B10600 であることが示されます。次のように sys.dm_exec_query_plan の plan_handle 引数にこの値を指定して実行すると、XML 形式の実行プランを取得できます。実行速度の遅いクエリまたはバッチの XML 形式の実行プランは、sys.dm_exec_query_plan によって返されるテーブルの query_plan 列に格納されます。

USE master;
GO
SELECT * FROM sys.dm_exec_query_plan (0x06000100A27E7C1FA821B10600);
GO

プラン キャッシュにあるすべてのクエリ プランのスナップショットを取得するには、sys.dm_exec_cached_plans 動的管理ビューに対してクエリを実行し、キャッシュにあるすべてのクエリ プランのプラン ハンドルを取得します。プラン ハンドルは、sys.dm_exec_cached_plans の plan_handle 列に格納されます。その後、次のように CROSS APPLY 演算子を使用して、プラン ハンドルを sys.dm_exec_query_plan に渡します。現在プラン キャッシュにある各プランの XML プラン表示の出力は、返されるテーブルの query_plan 列に格納されます。

USE master;
GO
SELECT * FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle);
GO

現在プラン キャッシュにあるクエリ プランのうち、サーバーで統計情報が収集されたすべてのクエリ プランのスナップショットを取得するには、sys.dm_exec_query_stats 動的管理ビューに対してクエリを実行し、キャッシュにあるこれらのプランのプラン ハンドルを取得します。プラン ハンドルは、sys.dm_exec_query_stats の plan_handle 列に格納されます。その後、次のように CROSS APPLY 演算子を使用して、プラン ハンドルを sys.dm_exec_query_plan に渡します。現在プラン キャッシュにある、収集された統計情報に関連する各プランの XML プラン表示の出力は、返されるテーブルの query_plan 列に格納されます。

USE master;
GO
SELECT * FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle);
GO

次の例では、上位 5 つのクエリにかかった平均 CPU 時間とプランを返します。

SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
Plan_handle, query_plan 
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)
ORDER BY total_worker_time/execution_count DESC;
GO