「マイクロソフト系技術情報 Wiki」は、「Open棟梁Project」,「OSSコンソーシアム .NET開発基盤部会」によって運営されています。
目次 †
概要 †
SQLトレース †
- トレース定義に一覧表示されているイベント クラスのインスタンスであるイベントが収集する。
- Transact-SQLのインスタンスでトレースを作成するためのストアド プロシージャが用意されている。
SQL Server Profiler †
SQL トレースを作成および管理し、トレースの結果を分析および再生するために使用するインターフェイス
利用シーン †
この機能は、次のような多くの場面で活用できる。
- 稼動監視、監査証跡
- パフォーマンス チューニング
- アプリケーション開発時のデバッグ
- 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」の最小値を指定すれば、短い実行時間のイベントをフィルタできる。
スキャンを発生させるクエリの検索 †
- 「テーブル スキャン」
- 「インデックス スキャン」
が検出された場合は、
インデックスが正しく利用されていない可能性があり、
性能的に問題になることが多い。
- 「テーブル スキャン」
- 「インデックス スキャン」
は、テーブルやインデックスをロックするため、
ロックタイムアウト、デッドロックの原因にもなる。
取得するイベント クラス †
- 「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」などの文字列が入っているので、ここから検索することもできる。
ロックを発生させるクエリ、ロックによりブロッキングされるクエリの検索 †
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というイベント クラスがサポートされ、
それをトレースに追加することで、デッドロックが分析できるようになっている模様。
- 松本崇博 Blog （SQL Server Tips）
以下、古い情報。
- デッドロックのイベント
- 「Lock:Deadlock Chain」イベントは、
デッドロックの対象の、２つのクライアントのイベントとして別々に記録され、
- 「Lock:Deadlock」イベントは、
「デッドロックの犠牲者 」となったクライアントのイベントとしてトレースに記録される。
- トランザクションのイベント
- デッドロックのイベントの後、
- 「デッドロックの犠牲者」のトランザクションはロールバックされ、
- 「他方」のトランザクションはコミットされる。
- これは、「SQLTransaction」イベントとして記録される。
- この時、の「EventSubClass?」データ列に「Commit」、「Rollback」が記録される。
- 「SPID」の確認
- 「Lock:Deadlock Chain」イベント、「Lock:Deadlock」イベントを発生させた
「SPID」を確認し、必要に応じて、「SPID」でフィルタをかけ、トレースを上に遡る。
- 同じ「SPID」の、クエリ開始のイベントの「TextData?」データ列に
デッドロックの原因となるクエリがないか確認する。
- ただし、「DTC」を使用している場合、
- クライアント処理が１つの「SPID」に対応付けられない。
- この場合、以下の手順で、クライアント処理を構成する複数の「SPID」を紐付ける。
- 「DTCTransaction」イベントの「EventSubClass?」データ列が、
「トランザクションの反映」となっているレコードを検索し、
「TextData?」データ列に記されているGUIDをメモする。
- 次に、「TextData?」データ列に、上でメモしたものと同じ
GUIDが設定されているレコードの「SPID」をメモする。
- これを繰り返すことで、クライアント処理を構成する複数の「SPID」が紐付けられる。
- 「Lock:Deadlock Chain」イベント、「Lock:Deadlock」イベントを発生させた一連の
「SPID」を確認し、必要に応じて、「SPID」でフィルタをかけ、トレースを上に遡る。
- 同一トランザクションの、クエリ開始のイベントの「TextData?」データ列に
デッドロックの原因となるクエリがないか確認する。
- テーブルなどの大きな粒度のオブジェクトをロックするため、
の原因にもなる。
参考 †
Tags: :データアクセス, :SQL Server, :障害対応, :性能, :デバッグ
![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
