[[Open棟梁Project>http://opentouryo.osscons.jp/]] - [[マイクロソフト系技術情報 Wiki>http://techinfoofmicrosofttech.osscons.jp/]]

-[[戻る>SQL Server 問題の分析方法]]

* 目次 [#m74b5e3e]
#contents

*概要 [#ied2299f]
「SQLプロファイラ」は、SQL Serverインスタンス上で実行中のクエリを収集(トレース)できる。

この機能は、次のような多くの場面で活用できる。
-稼動監視、監査証跡
-パフォーマンス チューニング
-アプリケーション開発時のデバッグ
-GUIツールや、ウィザードにより生成されたタスクの内部動作の調査

ここでは、SQLプロファイラ(SQLトレース)で取得する
-「イベントクラス」
-「データ列」
-クエリやイベントの検索方法

のベターユースを示す。

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

*SQL Serverの利用状況の監視 [#u955adc7]
**取得するイベント クラス [#r2d7b886]
-「Security Audit:Audit Login」
-「Security Audit:Audit Logout」

**取得するデータ列 [#f5d96e07]
-「EventClass(既定)」
-「EventSubClass」
-「LoginSID」
-「LoginName」
-「Success」

**問題となるクエリやイベントの検索方法 [#w9d0f97b]
ユーザが、ユーザのアカウントでDBに直接ログインする場合、~
サーバにログインしたユーザと、サーバからログオフしたユーザを、~
次のデータ列から確認できる。

-「LoginSID」データ列~
ユーザのセキュリティ識別番号(SID)

-「LoginName」データ列~
ユーザのログイン名

-「Success」データ列~
イベントが正常に終了したかどうか。

*性能が悪いクエリの検索 [#w7bc4519]
**取得するイベント クラス [#j7736ee4]
-「SQL:BatchCompleted」
-「SQL:StmtCompleted」
-「SP:Completed」
-「SP:StmtCompleted」
-「RPC:Completed」

**取得するデータ列 [#b3545c2f]
-「EventClass」
-「TextData」
-「Duration」
-「CPU」
-「Reads」
-「Writes」
-「ObjectID」
-「DatabaseID」
-「EndTime」

**問題となるクエリやイベントの検索方法 [#ue44a74d]
「Duration」データ列は、クエリ全体が完了するまでにかかった時間を示す。

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

*スキャンを発生させるクエリの検索 [#tcfd4aef]
-「テーブル スキャン」
-「インデックス スキャン」

が検出された場合は、
インデックスが正しく利用されていない可能性があり、~
性能的に問題になることが多い。

-「テーブル スキャン」
-「インデックス スキャン」

は、テーブルやインデックスをロックするため、~
ロックタイムアウト、デッドロックの原因にもなる。

**取得するイベント クラス [#oacd84a9]
-「SQL:BatchCompleted」
-「SQL:StmtCompleted」
-「SP:Completed」
-「SP:StmtCompleted」
-「RPC:Completed」
-「Scan:Stopped」
-「Performance:Execution Plan」

**取得するデータ列 [#x4af68cf]
-「EventClass(既定)」
-「TextData」
-「Duration」
-「CPU」
-「ObjectID」
-「IndexID」
-「DatabaseID」
-「EndTime」

**問題となるクエリやイベントの検索方法 [#rc4e5da1]
-「テーブル スキャン」、「インデックス スキャン」の発生は、
--「Scan:Stopped」イベントで検出する。
--システムデータベースの「Scan:Stopped」イベントが~
大量に混入する場合は「DatabaseID」でフィルタを設定する。

-実行されたクエリ
--クエリ完了のイベントの「TextData」データ列で確認する。
--その際、「Duration」データ列で、クエリ全体が完了するまでにかかった時間を確認できる。

-クエリの「実行プラン」の詳細
--「Execution Plan」イベントの「TextData」データ列で確認する。
--「テーブル スキャン」、「インデックス スキャン」を発生させたクエリの「実行プラン」には、~
「Table Scan」、「Index Scan」などの文字列が入っているので、ここから検索することもできる。

*ロックを発生させるクエリ、ロックによりブロッキングされるクエリの検索 [#v26b9c95]
SQL Serverは、Oracleと比べた場合、~
[[トランザクションの分離戦略>DBMSのロック・分離戦略と同時実行制御]]の違いから、~
ブロッキングによる問題が発生することが多く、

対策の際に
-「ロックを発生させるクエリ」
-「ロックによりブロッキングされるクエリ」

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

**取得するイベント クラス [#db8e39fc]
***クエリ開始のイベントクラス [#u9944d0c]
-「SQL:BatchStarting」
-「SQL:StmtStarting」
-「SP:Starting」
-「SP:StmtStarting」、
-「RPC:Starting」

***クエリ完了のイベントクラス [#f2f792fa]
-「SQL:BatchCompleted」
-「SQL:StmtCompleted」、
-「SP:Completed」
-「SP:StmtCompleted」
-「RPC:Completed」

***ロックのイベントクラス [#l3ab1685]
-「Lock:Timeout」
-「Lock:DeadLock」
-「Lock:DeadLock Chain」
-「Lock:Escalation」

***トランザクションのイベントクラス [#i78cbd06]
-「SQLTransaction」
-「DTCTransaction」

**取得するデータ列 [#ybbf1c71]
-「EventClass(既定)」
-「EventSubClass」
-「TextData」
-「SPID」
-「TransactionID」
-「ObjectID」
-「IndexID」
-「DatabaseID」
-「EndTime」

**問題となるクエリやイベントの検索方法 [#wb78fd27]

***ロックタイムアウトの検索 [#je211111]
-「Lock:Timeout」イベント クラスは、~
クエリがロックタイムアウトにより終了した場合に発生する。

-実行したクエリは、~
クエリ開始のイベントの「TextData」データ列で確認する。

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

-SQL Server Profiler を使用したデッドロックの分析~
https://msdn.microsoft.com/ja-jp/library/ms188246(v=sql.90).aspx

-松本崇博 Blog (SQL Server Tips)

--SQL Server でデッドロックを監視するには~
http://d.hatena.ne.jp/matu_tak/20091027/1256569336

--SQL Server でプロファイラの~
Lock:DeadLock と Lock:DeadLock Chain~
イベントクラスでデッドロックを監視する~
http://d.hatena.ne.jp/matu_tak/20091030/1256861414

以下、古い情報。

-デッドロックのイベント
--「Lock:Deadlock Chain」イベントは、~
デッドロックの対象の、2つのクライアントのイベントとして別々に記録され、
--「Lock:Deadlock」イベントは、~
「デッドロックの犠牲者 」となったクライアントのイベントとしてトレースに記録される。

-トランザクションのイベント
--デッドロックのイベントの後、
---「デッドロックの犠牲者」のトランザクションはロールバックされ、
---「他方」のトランザクションはコミットされる。
--これは、「SQLTransaction」イベントとして記録される。
--この時、の「EventSubClass」データ列に「Commit」、「Rollback」が記録される。

-「SPID」の確認
--「Lock:Deadlock Chain」イベント、「Lock:Deadlock」イベントを発生させた~
「SPID」を確認し、必要に応じて、「SPID」でフィルタをかけ、トレースを上に遡る。
--同じ「SPID」の、クエリ開始のイベントの「TextData」データ列に
デッドロックの原因となるクエリがないか確認する。

--ただし、「DTC」を使用している場合、
---クライアント処理が1つの「SPID」に対応付けられない。
---この場合、以下の手順で、クライアント処理を構成する複数の「SPID」を紐付ける。

-「DTC」を使用している場合の「SPID」の紐付

--「DTCTransaction」イベントの「EventSubClass」データ列が、~
「トランザクションの反映」となっているレコードを検索し、~
「TextData」データ列に記されているGUIDをメモする。

--次に、「TextData」データ列に、上でメモしたものと同じ~
GUIDが設定されているレコードの「SPID」をメモする。

--これを繰り返すことで、クライアント処理を構成する複数の「SPID」が紐付けられる。

--「Lock:Deadlock Chain」イベント、「Lock:Deadlock」イベントを発生させた一連の~
「SPID」を確認し、必要に応じて、「SPID」でフィルタをかけ、トレースを上に遡る。

--同一トランザクションの、クエリ開始のイベントの「TextData」データ列に~
デッドロックの原因となるクエリがないか確認する。

***「[[ロックのエスカレーション>SQL Server のロックのエスカレーション]]」の検索 [#y0fbf8e9]
-「Lock:Escalation」イベントは、~
「[[ロックのエスカレーション>SQL Server のロックのエスカレーション]]」が発生した場合に記録される。

-テーブルなどの大きな粒度のオブジェクトをロックするため、
--ロックタイムアウト
--デッドロック

>の原因にもなる。


*参考 [#jb91cf3f]
-SQL トレーススクリプトの作成、実行 (SQL Server 2005 ~ 2014)~
Microsoft SQL Server Japan Support Team Blog~
https://blogs.msdn.microsoft.com/jpsql/2011/01/25/sql-sql-server-2005-2014/

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

トップ   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS