SQL Serverで過去に実行したクエリを表示する

9310 ワード
SQLServer Database MSSQL SQLServer テキストリンク

システム動的管理ビューを使って過去に実行したクエリを表示できます。

sys.dm_exec_query_stats (Transact-sql SQL) - SQL Server | Microsoft Docs

キャッシュされたクエリ プランの集計パフォーマンス統計を SQL Serverに返します。 このビューには、キャッシュされたプラン内のクエリ ステートメントごとに 1 行が含まれており、その行の有効期間はプラン自体に関連付けられています。 つまり、プランがキャッシュから削除されると、対応する行もこのビューから削除されます。

sys.dm_exec_sql_text (Transact-sql SQL) - SQL Server | Microsoft Docs

指定された sql_handle によって識別される SQL バッチのテキストを返します。 このテーブル値関数は、システム関数 fn_get_sql に代わるものです。

sys.dm_exec_query_plan (Transact-SQL) - SQL Server | Microsoft Docs

プラン ハンドルで指定されたバッチのプラン表示を XML 形式で返します。 プラン ハンドルで指定するプランは、キャッシュ内のもの、または現在実行中のものを指定できます。

プラン表示の XML スキーマは公開され、この Microsoft Web サイト で使用できます。 また、SQL Server がインストールされているディレクトリからも入手できます。

現在のキャッシュ内のクエリプランの集計情報を返すため、メモリが多くなれば過去のクエリはキャッシュから削除されます。

過去に実行したクエリの直近100件を表示する

SELECT TOP 100
     qs.last_execution_time
    ,st.text as query_text
    ,qp.query_plan
FROM
    sys.dm_exec_query_stats as qs
CROSS APPLY
    sys.dm_exec_sql_text(qs.sql_handle) as st
CROSS APPLY
    sys.dm_exec_query_plan(qs.plan_handle) as qp
ORDER BY
    qs.last_execution_time desc
  • last_execution_time … 前回プランの実行が開始された時刻
  • query_text … SQLクエリのテキスト
  • query_plan … コンパイル時のクエリ実行プラン（プラン表示はXML形式）

query_planをクリックするとクエリ実行プランを表示できます。

平均CPUコストの高いクエリの上位100件を表示する

SELECT TOP 100
     qs.last_execution_time
    ,st.text as query_text
    ,qs.total_worker_time
    ,qs.execution_count
    ,qs.total_worker_time / qs.execution_count as avg_cpu_cost
    ,qp.query_plan
FROM
    sys.dm_exec_query_stats as qs
CROSS APPLY
    sys.dm_exec_sql_text(qs.sql_handle) as st
CROSS APPLY
    sys.dm_exec_query_plan(qs.plan_handle) as qp
ORDER BY
    avg_cpu_cost desc
  • total_worker_time … コンパイル後にプランの実行で使用されたCPU時間の合計（マイクロ秒単位）
  • execution_count … 前回のコンパイル時以降に、プランが実行された回数
  • avg_cpu_cost … CPU時間の合計をプランが実行された回数で割った、平均のCPUコスト

I/Oの合計数が多いクエリの上位100件を表示する

SELECT TOP 100
     qs.last_execution_time
    ,st.text as query_text
    ,qs.total_logical_reads / qs.execution_count as avg_logical_reads
    ,qs.total_logical_writes / qs.execution_count as avg_logical_writes
    ,qs.total_logical_reads + qs.total_logical_writes as total_io
    ,qs.execution_count
    ,qp.query_plan
FROM
    sys.dm_exec_query_stats as qs
CROSS APPLY
    sys.dm_exec_sql_text(qs.sql_handle) as st
CROSS APPLY
    sys.dm_exec_query_plan(qs.plan_handle) as qp
ORDER BY
    qs.total_logical_reads + qs.total_logical_writes desc
  • total_logical_reads … コンパイル後にこのプランの実行で行われた論理読み取りの合計数
  • total_logical_writes … コンパイル後にプランの実行で行われた論理書き込みの合計数