Skip to content

MS_SQLServerProblemAnalysis

Jump to bottom
nishi_74322014 edited this page Aug 13, 2026 · 2 revisions

SQL Server 問題の分析方法

概要

SQL Serverにおいて、

等の問題発生時の一般的な問題分析方法について説明します。

ツール

実行プランのグラフィカル表示

SQLプロファイラ（SQLトレース）

T-SQLステートメント / システムテーブル

様々な T-SQL ステートメント / システムテーブルが用意されている。

データベース エンジン チューニング アドバイザ

旧称「インデックス チューニング ウィザード」。
ワークロードを与えると、インデックスや統計情報の追加を提案する。

利用状況モニタ、動的管理ビュー（DMV）、クエリストア

ツール 導入
利用状況モニタ SQL Server 2000 の頃から存在
動的管理ビュー（DMV） SQL Server 2005 から導入
クエリ ストア SQL Server 2016 から導入

補足（現在の第一選択はクエリ ストア）: 3 つのうち、
クエリ ストアが最も有用である。

SQL プロファイラ DMV クエリ ストア
期間 取得中のみ 再起動で消える 永続化される
負荷 重い 軽い 軽い
実行プランの履歴 都度取得 現在のみ 世代管理される
状態 非推奨 現役 推奨

クエリ ストアの最大の利点は、
「昨日まで速かったクエリが今日から遅い」を後から追えることである。
実行プランが変わった（プラン リグレッション）ことを検出し、
以前のプランに強制的に戻す（Plan Forcing）こともできる。

ALTER DATABASE [MyDb] SET QUERY_STORE = ON;

SQL Server 2022 以降は新規 DB で既定で有効である。

移行メモ（SQL プロファイラは非推奨）: SQL Serverでも
述べたとおり、SQL Server Profiler / SQL トレースは非推奨であり、
後継は 拡張イベント (Extended Events, XEvent) である。
本番環境でプロファイラを回すと、それ自体が性能問題を引き起こす。

各種レポート

SSMS の標準レポート、Performance Dashboard Reports など。

各種ログ

SQL トレース、エラー ログ、クエリ ストア、パフォーマンス ログ
（SQL Server のログ）。

事象別の分析方法

長時間実行クエリ、ストアド情報

sys.dm_exec_query_stats、sys.dm_exec_sql_text 動的管理ビュー。

SELECT TOP 20
       qs.total_elapsed_time / qs.execution_count AS avg_elapsed,
       qs.execution_count,
       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 stmt
FROM   sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY avg_elapsed DESC;

ブロッキング情報

sys.dm_exec_requests 動的管理ビュー、sp_blocker_pss80 ストアド。

SELECT r.session_id, r.status, r.wait_type, r.wait_time,
       r.blocking_session_id, t.text
FROM   sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE  r.blocking_session_id <> 0;

補足（切り分けは「待ち」から入る）: 性能問題の分析では、
何を待っているか（待機統計） から入るのが最も速い。

SELECT TOP 10 wait_type, wait_time_ms, waiting_tasks_count
FROM   sys.dm_os_wait_stats
WHERE  wait_type NOT LIKE '%SLEEP%' AND wait_type NOT LIKE 'XE%'
ORDER BY wait_time_ms DESC;
主な待機タイプ 意味 疑い
LCK_M_* ロック待ち ブロッキング。トランザクションが長い
PAGEIOLATCH_* ディスク I/O 待ち インデックス不足、メモリ不足
CXPACKET / CXCONSUMER 並列処理 MAXDOP / コスト閾値の調整
SOS_SCHEDULER_YIELD CPU 待ち CPU 不足、非効率なクエリ
WRITELOG ログ書き込み待ち ログ ディスクが遅い
RESOURCE_SEMAPHORE メモリ許可待ち メモリ不足

「遅い」という報告に対して、CPU / I/O / ロック / メモリの
どれで待っているかが分かれば、対処はほぼ決まる。
IISのパフォーマンス カウンタと同じく、
層を分けて特定するのが要点である。

分析の進め方

補足（順序）: 実務では次の順で見ていくと迷いにくい。

  1. 待機統計（sys.dm_os_wait_stats）で何を待っているかを掴む。
  2. ブロッキング（sys.dm_exec_requests）で誰が待たせているかを特定。
  3. 重いクエリ（クエリ ストア / dm_exec_query_stats）を上位から見る。
  4. 実行プランを見て、スキャン / 見積もり行数の乖離を確認。
  5. インデックスの追加・修正、またはクエリの書き換え。

「まずインデックスを追加する」から入ると、
不要なインデックスが増えて更新性能が落ちるという別の問題を生む。
1〜4 で原因を確定させてから手を打ちたい。

参考

Microsoft Learn

＠IT

Tags: 移行, データアクセス, SQL Server, 障害対応, 性能, デバッグ

このWikiは「Open棟梁Project」,「OSSコンソーシアム 開発基盤部会」によって運営されています。

NetDevInfraWiki

マイクロソフト系技術情報 Wiki
Open 棟梁 Wiki

（未着手）

開発基盤部会 Wiki

移行管理: DONE ／ TODO

Clone this wiki locally