![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
[[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」イベントは、~ デッドロックの対象の、２つのクライアントのイベントとして別々に記録され、 --「Lock:Deadlock」イベントは、~ 「デッドロックの犠牲者 」となったクライアントのイベントとしてトレースに記録される。 -トランザクションのイベント --デッドロックのイベントの後、 ---「デッドロックの犠牲者」のトランザクションはロールバックされ、 ---「他方」のトランザクションはコミットされる。 --これは、「SQLTransaction」イベントとして記録される。 --この時、の「EventSubClass」データ列に「Commit」、「Rollback」が記録される。 -「SPID」の確認 --「Lock:Deadlock Chain」イベント、「Lock:Deadlock」イベントを発生させた~ 「SPID」を確認し、必要に応じて、「SPID」でフィルタをかけ、トレースを上に遡る。 --同じ「SPID」の、クエリ開始のイベントの「TextData」データ列に デッドロックの原因となるクエリがないか確認する。 --ただし、「DTC」を使用している場合、 ---クライアント処理が１つの「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/
