マイクロソフト系技術情報 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」イベントは、
      デッドロックの対象の、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?」データ列に
    デッドロックの原因となるクエリがないか確認する。

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

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

の原因にもなる。

DBに保存する

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

準備

ログ・テーブル作成

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]

ストアド登録

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を直接実行可能)。

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

インポート

上記ストアド(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

参考

参考

実行プランのグラフィカル表示

実行プランをログをグラフィカルに表示する。

SQL Server のオプティマイザ

オプティマイザが実行プランを決定する。


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


トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2018-11-06 (火) 10:47:45 (11d)