SQL Serverのパフォーマンストラブル調査手段集

SQL Serverのパフォーマンス調査をした際に使用したことのあるツールや個人的なノウハウなどのまとめです。

DMV(動的管理ビュー)

動的管理ビューについての詳細はこちら
DMVを利用すればキャッシュに残っている情報を基にしてパフォーマンス情報を取得できます。
例えば次のクエリを実行すれば最大実行時間が長いストアドプロシージャが確認できます。

最大実行時間の長いストアドプロシージャを取得するクエリ

SELECT TOP(100)
    o.[name] AS [ストアドプロシージャ名]
    , p.[bucketid]
    , ps.[execution_count] AS [実行回数]
    , ps.[max_elapsed_time] AS [最大実行時間]
    , ps.[total_elapsed_time] AS [合計実行時間]
    , ps.[max_physical_reads] AS [最大物理読み込み]
    , ps.[max_logical_reads] AS [最大論理読み込み]
    -- , 他色々
    , qp.[query_plan] AS [実行計画]
FROM
    sys.dm_exec_cached_plans AS p
    INNER JOIN sys.dm_exec_procedure_stats AS ps
        ON p.[plan_handle] = ps.[plan_handle]
    CROSS APPLY sys.dm_exec_query_plan(p.[plan_handle]) AS qp
    LEFT OUTER JOIN sys.objects AS o
        ON o.[object_id] = qp.[objectid]
ORDER BY
    ps.[max_elapsed_time] DESC

取れる情報はCPU処理時間や物理読み込み量など様々であり、バージョンで取れる情報も変わるので以下リンクを確認すると良いでしょう。
sys.dm_exec_procedure_stats (Transact-SQL)

ストアドプロシージャではなく通常のクエリの場合は sys.dm_exec_procedure_stats ではなく sys.dm_exec_query_stats で取得できます。
複数ステートメントで構成されているストアドプロシージャも下記のようにstatement_start_offset及びstatement_end_offsetを使うことでどのステートメントのパフォーマンスが悪いか確認できます。

最大実行時間の長いクエリを取得するクエリ

SELECT TOP(100)
    o.[name] AS [オブジェクト名]
    , p.[bucketid]
    , qs.[execution_count] AS [実行回数]
    , qs.[max_elapsed_time] AS [最大実行時間]
    , qs.[total_elapsed_time] AS [合計実行時間]
    , qs.[max_physical_reads] AS [最大物理読み込み]
    , qs.[max_logical_reads] AS [最大論理読み込み]
    -- , 他色々
    , SUBSTRING(st.[text], (qs.[statement_start_offset] / 2) + 1,
    ((CASE qs.[statement_end_offset]
            WHEN -1 THEN DATALENGTH(st.[text])
            ELSE qs.[statement_end_offset]
        END - qs.[statement_start_offset]) / 2) + 1)  AS [クエリ]
    , qp.[query_plan] AS [実行計画]
FROM
    sys.dm_exec_cached_plans AS p
    INNER JOIN sys.dm_exec_query_stats AS qs
        ON p.[plan_handle] = qs.[plan_handle]
    CROSS APPLY sys.dm_exec_sql_text(p.[plan_handle]) AS st
    CROSS APPLY sys.dm_exec_query_plan(p.[plan_handle]) AS qp
    LEFT OUTER JOIN sys.objects AS o
        ON o.[object_id] = qp.[objectid]
ORDER BY
    qs.[max_elapsed_time] DESC

上記以外にも、インデックス系のDMV( sys.dm_db_index_usage_stats など)やwaitの調査に役立つDMV(sys.dm_os_wait_stats)などがあるので、
適したものを使って調査するのが良いでしょう。

SQL Server Profiler

SQL Server Profilerについての詳細はこちら
こちらのツールを使うことでクエリの動作をリアルタイムで正確にトレースできます。
取得する情報が多いとディスクへの負荷が高まるため、正確なパフォーマンス測定には不向きですが、
各クエリ1回1回の具体的な実行時間の取得や、推定ではない実際の実行計画の取得が可能です。

取得した情報はProfiler上では使いづらいため、システム関数fn_trace_gettableを使用して一度テーブルに取り込むのが良いでしょう。
例えば次のクエリの実行で、SQL:BartchCompletedイベントを実行時間順で出すことが可能です。

トレースファイルからBatchCompletedイベントの処理を実行時間順に出力するクエリ

SELECT
    t.[DatabaseName]
    , [TextData]
    , [Duration]
    , [StartTime]
    , [EndTime]
    , [EventClass]
    , [SPID]
FROM
    fn_trace_gettable( N'D:\temp\sample.trc', DEFAULT ) AS t
WHERE
    t.[DatabaseName] = 'WideWorldImporters'
    AND t.[EventClass] = 12 --SQL:BatchCompletedイベント
ORDER BY
    [Duration] DESC;

拡張イベント

拡張イベントについての詳細はこちら
簡単に言うとProfilerの軽量版という認識です。

SystemHealthイベント

SystemHealthイベントについての詳細はこちら

SystemHealthイベントはデフォルトで自動的に動作している拡張イベントです。
SQL Serverのログフォルダに結果が出力されているので、問題が発生した際やパフォーマンステスト後の確認対象に含めておくとよいでしょう。

出力されるイベントの詳細は上記リンクに書いてあります。
デッドロックが発生した場合は、下図のように出力され、XML形式で見ることもできますし、タブを切り替えてデッドロックグラフとしても見ることが簡単にできるので、まずこちらから確認していくのがお勧めです。

クエリストア

クエリストアについての詳細はこちら
SQL Server2016から使えるようになった機能です、
有効化しておけば、動的管理ビューを使って自分でクエリを作らなくても自動的に情報が収集されます。
また、情報はディスクに保存されるため、動的管理ビューとは異なり、サーバーを停止しても一定期間残り続けます。
SSMSからGUIで簡単に確認でき、結果をグラフで見れるので問題の把握がしやすく、実行計画が途中で変化した場合でも確認することが可能です。

システムモニター(パフォーマンスモニター)

システムモニターについての詳細はこちら
CPU使用率やメモリの使用率、ディスク読み込み/書き込み速度などのリソース使用率を記録可能な、Windowsに標準搭載されているツールです。
SQL Serverの利用メモリ量やロック数、インデックスのフルスキャン数なども確認できます。

例えば、Memory Grants Pending が0でない場合は割り当てメモリ不量が不足していたり、
想定外の実行計画が使われたことによるメモリの消費過多が発生しているかもしれません。
コードの変更前後で同一のパフォーマンステストを行い、システムモニターの結果を比較すると
問題の発生有無がわかりやすいです。

無料のツール

PlanExplorer

公式ページ

SentryOne製の実行計画を見るためのツールです。
SSMSより見やすく、インストール時にアドインを入れておけば、下記画像ハイライト部分のようにSSMSから直接呼び出せるので大体これを使ってました。

• 見た目比較
  • SSMS
  • PlanExplorer

SQL Server First Responder Kit

公式ページ (github)
無料で使えるヘルスチェックツールです。(個人的にはあまり使っていませんでしたが参考までに)
日本語で紹介しているページはこちら

sp_BlitzCache.sql を実行してストアドを登録することでキャッシュからパフォーマンス問題が検出可能です。
例えば、コンパイルがタイムアウトしていることや、インデックスが欠けていること、インデックスが多すぎることなど様々な問題が検出可能です。