Skip to content

MS_SQLProfiler

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

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

概要

SQLトレース

  • トレース定義に一覧表示されているイベント クラスのインスタンスである
    イベントが収集する。
  • Transact-SQL のインスタンスでトレースを作成するための
    ストアド プロシージャが用意されている。

SQL Server Profiler

SQL トレースを作成および管理し、トレースの結果を分析および再生するために
使用するインターフェイス。

移行メモ（最新化：現在は拡張イベントを使う）: SQL トレースと
SQL Server Profiler は非推奨であり、
現在は拡張イベント（Extended Events / XEvent） を使用する
（SQL Server のログを参照）。

本ページの価値は「どのイベント クラスとデータ列を取れば
何が分かるか」という採取設計にあり、
これは拡張イベントにもそのまま読み替えられる。

本ページの SQL トレース 拡張イベントの相当
SQL:BatchCompleted sql_batch_completed
RPC:Completed rpc_completed
Lock:Timeout lock_timeout
Lock:Deadlock Chain / Deadlock Graph xml_deadlock_report（system_health に既定で入る）
Scan:Stopped （実行プランの演算子で判断する）
「データ列」 アクション（sqlserver.sql_text 等）

利用シーン

この機能は、次のような多くの場面で活用できる。

  • 稼動監視、監査証跡
  • パフォーマンス チューニング
  • アプリケーション開発時のデバッグ
  • GUI ツールや、ウィザードにより生成されたタスクの内部動作の調査

ここでは、SQL プロファイラ（SQL トレース）で取得する

  • 「イベント クラス」
  • 「データ列」
  • クエリやイベントの検索方法

のベター ユースを示す。
無駄なデータが混入する場合は、個別にフィルタ設定を検討する。

SQL Serverの利用状況の監視

取得するイベント クラス

  • Security Audit:Audit Login
  • Security Audit:Audit Logout

取得するデータ列

  • EventClass（既定）
  • EventSubClass
  • LoginSID
  • LoginName
  • Success

問題となるクエリやイベントの検索方法

ユーザが、ユーザのアカウントで DB に直接ログインする場合、
サーバにログインしたユーザと、サーバからログオフしたユーザを、
次のデータ列から確認できる。

データ列 内容
LoginSID ユーザのセキュリティ識別番号（SID）
LoginName ユーザのログイン名
Success イベントが正常に終了したかどうか

性能が悪いクエリの検索

取得するイベント クラス

  • SQL:BatchCompleted
  • SQL:StmtCompleted
  • SP:Completed
  • SP:StmtCompleted
  • RPC:Completed

取得するデータ列

  • EventClass / TextData / Duration / CPU / Reads / Writes
  • ObjectID / DatabaseID / EndTime

問題となるクエリやイベントの検索方法

Duration データ列は、クエリ全体が完了するまでにかかった時間を示す。

  • Duration でグループ化（ソート）すれば、
    問題のあるクエリ、問題のないクエリを分けることができる。
  • また、フィルタの設定として Duration の最小値を指定すれば、
    短い実行時間のイベントをフィルタできる。

補足（Completed 系を取る理由）: Starting 系のイベントには
Duration / CPU / Reads が入らない（まだ実行していないため）。
性能を見るときは必ず Completed 系を取る。

逆に、ハングやタイムアウトで完了しなかったクエリは
Completed に出ない。そのため後述のロック調査では
Starting 系も併せて取るという構成になっている。

また Duration の単位はマイクロ秒（GUI 表示はミリ秒）である点に注意する。

スキャンを発生させるクエリの検索

  • テーブル スキャン
  • インデックス スキャン

が検出された場合は、インデックスが正しく利用されていない可能性があり、
性能的に問題になることが多い。
また、テーブルやインデックスをロックするため、
ロック タイムアウト、デッドロックの原因にもなる。

取得するイベント クラス

  • SQL:BatchCompleted / SQL:StmtCompleted
  • SP:Completed / SP:StmtCompleted / RPC:Completed
  • Scan:Stopped
  • Performance:Execution Plan

取得するデータ列

  • EventClass（既定）/ TextData / Duration / CPU
  • ObjectID / IndexID / DatabaseID / EndTime

問題となるクエリやイベントの検索方法

  • 「テーブル スキャン」「インデックス スキャン」の発生は、
    • Scan:Stopped イベントで検出する。
    • システム データベースの Scan:Stopped イベントが大量に混入する場合は
      DatabaseID でフィルタを設定する。
  • 実行されたクエリ
    • クエリ完了のイベントの TextData データ列で確認する。
    • その際、Duration データ列で、
      クエリ全体が完了するまでにかかった時間を確認できる。
  • クエリの「実行プラン」の詳細
    • Execution Plan イベントの TextData データ列で確認する。
    • 「テーブル スキャン」「インデックス スキャン」を発生させたクエリの
      「実行プラン」には、Table Scan、Index Scan などの文字列が
      入っているので、ここから検索することもできる。

補足（スキャンが常に悪いわけではない）: 件数が少ないテーブルや、
表の大半を読む集計クエリでは、スキャンのほうが速い。
問題になるのは「少数の行を取りたいのにスキャンしている」場合である。

兆候 疑うもの
主キー条件なのにスキャン 暗黙の型変換（後述）、関数の適用（WHERE UPPER(col)=）
直前まで速かった 統計情報の陳腐化（SQL Server のオプティマイザ）
特定パラメタだけ遅い パラメータ スニッフィング

現在は実行プランのグラフィカル表示や
クエリ ストアで同じ判断ができる。

ロックを発生させるクエリ、ロックによりブロッキングされるクエリの検索

SQL Server は、Oracle と比べた場合、
**トランザクションの分離戦略**の違いから、
ブロッキングによる問題が発生することが多く、対策の際に

  • 「ロックを発生させるクエリ」
  • 「ロックによりブロッキングされるクエリ」

の確認が必要になることが多い。

取得するイベント クラス

分類 イベント クラス
クエリ開始 SQL:BatchStarting / SQL:StmtStarting / SP:Starting / SP:StmtStarting / RPC:Starting
クエリ完了 SQL:BatchCompleted / SQL:StmtCompleted / SP:Completed / SP:StmtCompleted / RPC:Completed
ロック Lock:Timeout / Lock:DeadLock / Lock:DeadLock Chain / Lock:Escalation
トランザクション SQLTransaction / DTCTransaction

取得するデータ列

  • EventClass（既定）/ EventSubClass / TextData
  • SPID / TransactionID / ObjectID / IndexID / DatabaseID / EndTime

問題となるクエリやイベントの検索方法

ロックタイムアウトの検索

  • Lock:Timeout イベント クラスは、
    クエリがロック タイムアウトにより終了した場合に発生する。
  • 実行したクエリは、クエリ開始のイベントの TextData データ列で確認する。

デッドロックの検索

新しい情報を見ると、Deadlock Graph というイベント クラスがサポートされ、
それをトレースに追加することで、デッドロックが分析できるようになっている模様。

以下、古い情報。

  • デッドロックのイベント
    • Lock:Deadlock Chain イベントは、
      デッドロックの対象の、2 つのクライアントのイベントとして別々に記録され、
    • Lock:Deadlock イベントは、
      「デッドロックの犠牲者」となったクライアントのイベントとして
      トレースに記録される。
  • トランザクションのイベント
    • デッドロックのイベントの後、
      • 「デッドロックの犠牲者」のトランザクションはロールバックされ、
      • 「他方」のトランザクションはコミットされる。
    • これは、SQLTransaction イベントとして記録される。
    • この時、EventSubClass データ列に Commit、Rollback が記録される。
  • SPID の確認
    • Lock:Deadlock Chain イベント、Lock:Deadlock イベントを発生させた
      SPID を確認し、必要に応じて SPID でフィルタをかけ、
      トレースを上に遡る。
    • 同じ SPID の、クエリ開始のイベントの TextData データ列に
      デッドロックの原因となるクエリがないか確認する。
    • ただし、DTC を使用している場合、
      クライアント処理が 1 つの SPID に対応付けられない。

DTC を使用している場合の SPID の紐付け

  • DTCTransaction イベントの EventSubClass データ列が
    「トランザクションの反映」となっているレコードを検索し、
    TextData データ列に記されている GUID をメモする。
  • 次に、TextData データ列に、上でメモしたものと同じ GUID が
    設定されているレコードの SPID をメモする。
  • これを繰り返すことで、クライアント処理を構成する複数の SPID が
    紐付けられる。
  • Lock:Deadlock Chain イベント、Lock:Deadlock イベントを発生させた
    一連の SPID を確認し、必要に応じて SPID でフィルタをかけ、
    トレースを上に遡る。
  • 同一トランザクションの、クエリ開始のイベントの TextData データ列に
    デッドロックの原因となるクエリがないか確認する。

補足（現在はここまでやらなくてよい）: 現在は
system_health 拡張イベント セッションが既定で常時動いており、
デッドロック グラフ（xml_deadlock_report）が自動的に記録されている。
事前にトレースを仕掛けていなくても、発生後に次で取り出せる。

SELECT CAST(target_data AS xml)
FROM sys.dm_xe_session_targets t
JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address
WHERE s.name = 'system_health' AND t.target_name = 'ring_buffer';

デッドロック グラフには両側の SPID・クエリ・ロック資源・犠牲者が
1 つの XML に入っているため、本項のような
「SPID を頼りにトレースを遡る」作業は不要になる。
ただし ring_buffer は上書きされるので、
恒久的に残すなら専用の XEvent セッションをファイル ターゲットで作る。

「ロックのエスカレーション」の検索

  • Lock:Escalation イベントは、
    「ロックのエスカレーション」が
    発生した場合に記録される。

  • テーブルなどの大きな粒度のオブジェクトをロックするため、

    • ロック タイムアウト
    • デッドロック

    の原因にもなる。

DBに保存する

SQL Server トレース・ログ（バイナリ）を DB にインポートできる。

準備

ログ・テーブル作成

トレース ファイルの全データ列に対応した、以下のような構造のテーブルを作成する
（TextData ntext / BinaryData image / DatabaseID int / TransactionID bigint /
LineNumber int / NTUserName nvarchar(256) / …… /
SessionLoginName nvarchar(256) / PlanHandle image まで、
トレースが出力する全列を並べる）。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[テーブル名](
 [TextData] [ntext] COLLATE Japanese_CI_AS NULL,
 [BinaryData] [image] NULL,
 [DatabaseID] [int] NULL,
 [TransactionID] [bigint] NULL,
 [LineNumber] [int] NULL,
 [NTUserName] [nvarchar](256) COLLATE Japanese_CI_AS NULL,
 [NTDomainName] [nvarchar](256) COLLATE Japanese_CI_AS NULL,
 [HostName] [nvarchar](256) COLLATE Japanese_CI_AS NULL,
 [ClientProcessID] [int] NULL,
 [ApplicationName] [nvarchar](256) COLLATE Japanese_CI_AS NULL,
 [LoginName] [nvarchar](256) COLLATE Japanese_CI_AS NULL,
 [SPID] [int] NULL,
 [Duration] [bigint] NULL,
 [StartTime] [datetime] NULL,
 [EndTime] [datetime] NULL,
 [Reads] [bigint] NULL,
 [Writes] [bigint] NULL,
 [CPU] [int] NULL,
 -- …（以下、Permissions, Severity, EventSubClass, ObjectID, Success,
 --     IndexID, IntegerData, ServerName, EventClass, ObjectType, …
 --     SqlHandle, SessionLoginName, PlanHandle まで全列を定義する）
 [PlanHandle] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

移行メモ（型について）: ntext / image は
非推奨の型であり、現在は nvarchar(max) / varbinary(max) を使う。
上記は元ページ執筆当時のスクリプトをそのまま示している。

ストアド登録

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ImportTraceFile]
  @Param1 nvarchar(256),
  @Param2 nvarchar(30)
AS
BEGIN
 SET NOCOUNT ON;
 exec(
 'INSERT INTO ' + @Param2 +
 ' SELECT * FROM fn_trace_gettable(''' + @Param1 + ''', default)')
END
GO

※ ストアドは必須でない（fn_trace_gettable を直接実行可能）。

補足（この形は SQL インジェクションの見本でもある）: 上記は
文字列連結による動的 SQL であり、
@Param2（テーブル名）に細工された値が渡ると任意の SQL が実行される。
管理者が手元で使う限りは問題ないが、
アプリケーションのコードでこの書き方を真似てはならない
（オブジェクト名は QUOTENAME() で括り、値は必ずパラメータ化する）。
元ページも「ストアドは必須でない」と記しているとおり、
fn_trace_gettable を直接使うほうが安全である。

トレースのインポート・エクスポート

インポート

上記ストアド（ImportTraceFile）を実行

  • @Param1: トレース ファイル名
  • @Param2: テーブル名

エクスポート（bcp）

cmd.exe /c bcp
 "SELECT
   EventClass, Success, ApplicationName, HostName, LoginName, SPID,
   Duration, StartTime, EndTime, ObjectName, DatabaseName, ServerName,
   EventSubClass, TargetLoginName, RoleName, TargetUserName, TextData
  FROM テーブル名"
 queryout C:\Trace.txt -c -t , -r ,\n -T

参考

参考

関連

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

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

NetDevInfraWiki

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

（未着手）

開発基盤部会 Wiki

移行管理: DONE ／ TODO

Clone this wiki locally