SQLプロファイラ（SQLトレース） の変更点

https://techinfoofmicrosofttech.osscons.jp:443/index.php?SQL%E3%83%97%E3%83%AD%E3%83%95%E3%82%A1%E3%82%A4%E3%83%A9%EF%BC%88SQL%E3%83%88%E3%83%AC%E3%83%BC%E3%82%B9%EF%BC%89

[ トップ ]   [ 編集 | 差分 | バックアップ | 添付 | リロード ]   [ 新規 | 一覧 | 単語検索 | 最終更新 | ヘルプ ]

• 追加された行はこの色です。
• 削除された行はこの色です。
• SQLプロファイラ（SQLトレース） へ行く。
• SQLプロファイラ（SQLトレース） の差分を削除

---

「[[マイクロソフト系技術情報 Wiki>http://techinfoofmicrosofttech.osscons.jp/]]」は、「[[Open棟梁Project>https://github.com/OpenTouryoProject/]]」,「[[OSSコンソーシアム .NET開発基盤部会>https://www.osscons.jp/dotNetDevelopmentInfrastructure/]]」によって運営されています。

-戻る
--[[SQL Server のログ]]
--[[SQL Server 問題の分析方法]]
--[[SQL Server アドホック クエリ問題の監視]]

* 目次 [#m74b5e3e]
#contents

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

-SQL トレースの概要~
https://technet.microsoft.com/ja-jp/library/ms191006.aspx

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

-SQL Server Profiler~
https://technet.microsoft.com/ja-jp/library/ms181091.aspx

**利用シーン [#m3164d3e]
この機能は、次のような多くの場面で活用できる。
-稼動監視、監査証跡
-パフォーマンス チューニング
-アプリケーション開発時のデバッグ
-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 のロックのエスカレーション]]」が発生した場合に記録される。

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

>の原因にもなる。

*DBに保存する [#bc13e19b]
SQL Server トレース・ログ（バイナリ）をDBにインポートできる。

**準備 [#ce1c45bc]

***ログ・テーブル作成 [#w59c0c4a]
 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] [bigint] NULL,
  [Severity] [int] NULL,
  [EventSubClass] [int] NULL,
  [ObjectID] [int] NULL,
  [Success] [int] NULL,
  [IndexID] [int] NULL,
  [IntegerData] [int] NULL,
  [ServerName] [nvarchar](256) COLLATE Japanese_CI_AS NULL,
  [EventClass] [int] NULL,
  [ObjectType] [int] NULL,
  [NestLevel] [int] NULL,
  [State] [int] NULL,
  [Error] [int] NULL,
  [Mode] [int] NULL,
  [Handle] [int] NULL,
  [ObjectName] [nvarchar](256) COLLATE Japanese_CI_AS NULL,
  [DatabaseName] [nvarchar](256) COLLATE Japanese_CI_AS NULL,
  [FileName] [nvarchar](256) COLLATE Japanese_CI_AS NULL,
  [OwnerName] [nvarchar](256) COLLATE Japanese_CI_AS NULL,
  [RoleName] [nvarchar](256) COLLATE Japanese_CI_AS NULL,
  [TargetUserName] [nvarchar](256) COLLATE Japanese_CI_AS NULL,
  [DBUserName] [nvarchar](256) COLLATE Japanese_CI_AS NULL,
  [LoginSid] [image] NULL,
  [TargetLoginName] [nvarchar](256) COLLATE Japanese_CI_AS NULL,
  [TargetLoginSid] [image] NULL,
  [ColumnPermissions] [int] NULL,
  [LinkedServerName] [nvarchar](256) COLLATE Japanese_CI_AS NULL,
  [ProviderName] [nvarchar](256) COLLATE Japanese_CI_AS NULL,
  [MethodName] [nvarchar](256) COLLATE Japanese_CI_AS NULL,
  [RowCounts] [bigint] NULL,
  [RequestID] [int] NULL,
  [XactSequence] [bigint] NULL,
  [EventSequence] [int] NULL,
  [BigintData1] [bigint] NULL,
  [BigintData2] [bigint] NULL,
  [GUID] [uniqueidentifier] NULL,
  [IntegerData2] [int] NULL,
  [ObjectID2] [bigint] NULL,
  [Type] [int] NULL,
  [OwnerID] [int] NULL,
  [ParentName] [nvarchar](256) COLLATE Japanese_CI_AS NULL,
  [IsSystem] [int] NULL,
  [Offset] [int] NULL,
  [SourceDatabaseID] [int] NULL,
  [SqlHandle] [image] NULL,
  [SessionLoginName] [nvarchar](256) COLLATE Japanese_CI_AS NULL,
  [PlanHandle] [image] NULL
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

***ストアド登録 [#r4575ebf]
 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を直接実行可能）。

**トレースのインポート・エクスポート [#n6808d00]

***インポート [#f87b16da]
上記ストアド（ImportTraceFile）を実行
-@Param1：トレースファイル名
-@Param2：テーブル名

***エクスポート（bcp） [#mbe8b6b5]
 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


**参考 [#o0015924]
-In SQL Server, how to move-import a multiple~
.trc files to a trace table - Stack Overflow~
http://stackoverflow.com/questions/2784714/in-sql-server-how-to-move-import-a-multiple-trc-files-to-a-trace-table

-bcp ユーティリティ~
http://msdn.microsoft.com/ja-jp/library/ms162802.aspx

*参考 [#jb91cf3f]
-SQL トレースの概要~
https://technet.microsoft.com/ja-jp/library/ms191006.aspx

-SQL Server Profiler の概要~
https://technet.microsoft.com/ja-jp/library/ms181091.aspx
>トレースを作成および管理し、トレースの結果を分析および再生するために使用するGUI

-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/

**[[実行プランのグラフィカル表示]] [#s2f3be34]
実行プランをログをグラフィカルに表示する。

**[[SQL Server のオプティマイザ]] [#a9025ea4]
オプティマイザが実行プランを決定する。

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

       

Site admin: dotNetDevelopmentInfrastructure

PukiWiki 1.4.7 Copyright © 2001-2006 PukiWiki Developers Team. License is GPL.
Based on "PukiWiki" 1.3 by yu-ji. Powered by PHP 5.3.3. HTML convert time: 0.002 sec.