⭕️ 🍷 🌈 Microsoft SQL Serverトレース分析レポート 🈁 👨‍🎨 😾

問題の声明

データベースアプリケーションを使用する際のボトルネックを特定します。 SQLクエリのパフォーマンスに関するレポートのコンパイル、エラーとデッドロックの分析、比較レポートのコンパイル、テストによるストアドプロシージャのカバレッジの程度の計算、およびダイアグラムの構築。

テストは定期的に実施されます。したがって、レポートは自動的に生成され、標準化され、相互に簡単に比較される必要があります。

使用される技術：

Microsoft SQL Server
Microsoft Office Excel
SQLProfilerReportHelperプロジェクトで編成された一連のSQLクエリ。
SQLクエリを実行する機能を備えたロードテストツール（ JMeter 、 Visual Studio Ultimate 、...）;

レベル300 （専門家向け）。

つまり、完成したトラックでレポートを生成する手順は次のとおりです。

SQLProfilerReportHelperを実行し、ボタンをクリックします。
レポートテーブルからレコードを選択し、結果をクリップボードにコピーします。
Microsoft Office Excelを実行し、バッファからエントリを自動的にフォーマットされたテーブルに貼り付けて、レポートドキュメントを保存します。

SQLProfilerReportHelperをダウンロードすると、ツールとレポートテンプレートを利用できます。

ツールとレポートの説明、およびそれらをコンパイルする手順を読みたい場合は、先に進んでください。

2つの主要なレポートについて説明します。

ストアドプロシージャのパフォーマンスに関する統計（ドラフトレポート）;
すべてのリクエストの統計（詳細レポート）。

1.トレース

プロセスは終了することで理解できません。理解はプロセスとともに動き、その流れと融合し、流れを伴わなければなりません。

-フランク・ハーバートの小説「デューン」より

結果が自動的に分析されるトレースは、特定のイベントと列の存在を示唆します。負荷テストの開始時に便利なソフトウェアの起動。私はこの方法を使用しますが、他のオプションも可能です。

ロードされたOLTPシステムをプロファイリングするときは、トレースをファイルに保存することをお勧めします。テスト後にデータベーステーブルにトレースファイルをロードする。これにより、データベースにトレースを書き込むときよりもSQL Serverの追加負荷が少なくなります（測定されなかった量はどれだけ少なくなりますか）。また、消費されるスペースが少なくなります（トレースファイルは、同様のトレーステーブルよりも3.9〜4倍小さくなります）。さらに、OLTPがテストされ、負荷テストが行われていると想定されています。

OLAPシステムをプロファイリングするとき、負荷が低いとき、データベースにトレースを直接記録できます。

1.1。オートスタート

負荷テストの開始時にプロファイリングを開始すると便利です。負荷テストのコンテキストで、負荷テストコードを変更せずに値を変更するのに便利なパラメーターを決定します。

useSQLProfiling （true / false）-テストの開始時にトレースを使用します（たとえば、true）
pathSQLPrifiling-トレースファイルを保存するためのMicrosoft SQL Serverを備えたサーバー上のディレクトリへのパス（たとえば、D：\ Traces \ Synerdocs \）
データベース名（4つのパラメーター）-プロファイルされたイベント（ tempdbおよびテストされたシステムの3つのデータベース）のフィルターに使用します

負荷プロファイルの名前は、トレースファイルの名前に反映され、負荷テストのコンテキストからも抽出されます。そして、負荷プロファイルは、テストの継続時間を取得して、トレースがいつ完了するかを計算します。

パラメーター化されたクエリScript.01.Start trace.sqlが実行されます （プロジェクトフォルダーまたは下のボックスにあるscript \ start trace \ディレクトリを参照）。

Script.01.trace.sqlを開始します

-- Script.01.Start trace.sql -- : -- @traceDuration, Int, " ( )" --      ,   . -- @fileName, nvarchar(256), "   " --      ,      . -- D:\Traces\Synerdocs\ServiceLoadTestBigFiles.2014.01.21 08.30.10 -- @db1, nvarchar(256),      -- @db2, nvarchar(256),      -- @db3, nvarchar(256),      -- @db4, nvarchar(256),      -- Create a Queue declare @rc int declare @TraceID int declare @maxfilesize bigint declare @stopTraceTime datetime declare @traceFileName nvarchar(256) declare @traceOptions int -- Create 100 MBytes files set @maxfilesize = 100 -- Duration of trace set @stopTraceTime = DATEADD(second, @traceDuration, SYSDATETIME()) -- Trace filename set @traceFileName = @fileName -- TRACE_FILE_ROLLOVER set @traceOptions = 2 exec @rc = sp_trace_create @TraceID output, @traceOptions, @traceFileName, @maxfilesize, @stopTraceTime if (@rc != 0) goto error -- Set the events declare @on bit set @on = 1 -- 162. User Error Message. Displays error messages that users see in the case of an error or exception. exec sp_trace_setevent @TraceID, 162, 1, @on -- TextData. ntext. exec sp_trace_setevent @TraceID, 162, 4, @on -- TransactionID. bigint. exec sp_trace_setevent @TraceID, 162, 9, @on -- ClientProcessID. int. exec sp_trace_setevent @TraceID, 162, 10, @on -- ApplicationName. nvarchar. exec sp_trace_setevent @TraceID, 162, 11, @on -- LoginName. nvarchar. exec sp_trace_setevent @TraceID, 162, 12, @on -- SPID. int. exec sp_trace_setevent @TraceID, 162, 20, @on -- Severity. exec sp_trace_setevent @TraceID, 162, 14, @on -- StartTime. datetime. exec sp_trace_setevent @TraceID, 162, 31, @on -- Error. int. exec sp_trace_setevent @TraceID, 162, 35, @on -- DatabaseName. nvarchar. exec sp_trace_setevent @TraceID, 162, 49, @on -- RequestID. int. exec sp_trace_setevent @TraceID, 162, 50, @on -- XactSequence. bigint. -- 148. Deadlock Graph. Occurs when an attempt to acquire a lock is canceled because the attempt was part of a deadlock and was chosen as the deadlock victim. Provides an XML description of a deadlock. exec sp_trace_setevent @TraceID, 148, 1, @on -- TextData. ntext. exec sp_trace_setevent @TraceID, 148, 4, @on -- TransactionID. bigint. Not used. exec sp_trace_setevent @TraceID, 148, 11, @on -- LoginName. nvarchar. exec sp_trace_setevent @TraceID, 148, 12, @on -- SPID. int. exec sp_trace_setevent @TraceID, 148, 14, @on -- StartTime. datetime. -- 10. RPC:Completed. Occurs when a remote procedure call (RPC) has completed. exec sp_trace_setevent @TraceID, 10, 1, @on -- TextData. ntext. exec sp_trace_setevent @TraceID, 10, 4, @on -- TransactionID. bigint. exec sp_trace_setevent @TraceID, 10, 9, @on -- ClientProcessID. int. exec sp_trace_setevent @TraceID, 10, 10, @on -- ApplicationName. nvarchar. exec sp_trace_setevent @TraceID, 10, 11, @on -- LoginName. nvarchar. exec sp_trace_setevent @TraceID, 10, 12, @on -- SPID. int. exec sp_trace_setevent @TraceID, 10, 13, @on -- Duration. bigint. exec sp_trace_setevent @TraceID, 10, 14, @on -- StartTime. datetime. exec sp_trace_setevent @TraceID, 10, 15, @on -- EndTime. datetime. exec sp_trace_setevent @TraceID, 10, 16, @on -- Reads. bigint. exec sp_trace_setevent @TraceID, 10, 17, @on -- Writes. bigint. exec sp_trace_setevent @TraceID, 10, 18, @on -- CPU. int. exec sp_trace_setevent @TraceID, 10, 31, @on -- Error. int. exec sp_trace_setevent @TraceID, 10, 34, @on -- ObjectName. nvarchar. exec sp_trace_setevent @TraceID, 10, 35, @on -- DatabaseName. nvarchar. exec sp_trace_setevent @TraceID, 10, 48, @on -- RowCounts. bigint. exec sp_trace_setevent @TraceID, 10, 49, @on -- RequestID. int. exec sp_trace_setevent @TraceID, 10, 50, @on -- XactSequence. bigint. -- 12. SQL:BatchCompleted. Occurs when a Transact-SQL batch has completed. exec sp_trace_setevent @TraceID, 12, 1, @on -- TextData. ntext. exec sp_trace_setevent @TraceID, 12, 4, @on -- TransactionID. bigint. exec sp_trace_setevent @TraceID, 12, 9, @on -- ClientProcessID. int. exec sp_trace_setevent @TraceID, 12, 11, @on -- LoginName. nvarchar. exec sp_trace_setevent @TraceID, 12, 10, @on -- ApplicationName. nvarchar. exec sp_trace_setevent @TraceID, 12, 12, @on -- SPID. int. exec sp_trace_setevent @TraceID, 12, 13, @on -- Duration. bigint. exec sp_trace_setevent @TraceID, 12, 14, @on -- StartTime. datetime. exec sp_trace_setevent @TraceID, 12, 15, @on -- EndTime. datetime. exec sp_trace_setevent @TraceID, 12, 16, @on -- Reads. bigint. exec sp_trace_setevent @TraceID, 12, 17, @on -- Writes. bigint. exec sp_trace_setevent @TraceID, 12, 18, @on -- CPU. int. exec sp_trace_setevent @TraceID, 12, 31, @on -- Error. int. exec sp_trace_setevent @TraceID, 12, 35, @on -- DatabaseName. nvarchar. exec sp_trace_setevent @TraceID, 12, 48, @on -- RowCounts. bigint. exec sp_trace_setevent @TraceID, 12, 49, @on -- RequestID. int. exec sp_trace_setevent @TraceID, 12, 50, @on -- XactSequence. bigint. -- Set the Filters exec sp_trace_setfilter @TraceID, 35, 0, 6, @db1 exec sp_trace_setfilter @TraceID, 35, 1, 6, @db2 exec sp_trace_setfilter @TraceID, 35, 1, 6, @db3 exec sp_trace_setfilter @TraceID, 35, 1, 6, @db4 -- Set the trace status to start exec sp_trace_setstatus @TraceID, 1 -- display trace id for future references select TraceID=@TraceID goto finish error: select ErrorCode=@rc finish:

戻り値：

TraceID、int、トレースセッションID。その後のトレースの停止に役立ちます。
ErrorCode、int、トレースが開始されなかった場合のエラーコード。sp_trace_createの戻りコード値を参照してください。

ロードテストイベントでのクエリの実行方法は、テストツールと開発言語によって異なります。 JavaとC＃の両方で、これは単純に行われます。別のガイドを作成できます。 2つの文のいくつかの機能：

イベントはロードエージェントで実行されます。複数のロードエージェントがある場合、ロードテストのパラメーターはプロファイリングを早期に開始および終了するエージェントの名前を指定し、エージェントはその目的を名前で学習します。
SQL ServerがDMZの背後にある場合、 SQL Serverで動作するエージェントはエッジサーバーにインストールされ、エージェントにはゼロ負荷プロファイルが割り当てられますが、ロードベンチでDMZを使用しない方が簡単です。

ストレステストに精通した専門家は、前の2つの文を理解します。理解できない場合は、エージェント、DMZがないか、半自動および手動モードでトレースを開始する方が簡単です。

1.1.1。トレースを停止

トレースは時間通りに実行されます。トレースの完了の瞬間は、テストの期間（ traceDurationパラメーター）によって決定され、 Script.01.Start trace.sqlスクリプトで計算されます。ストレステストも時間通りに実行され、プログラムで取得できる期間があると想定されます。

1.1.2。早期終了

Script.01.Start trace.sqlが実行されると、 TraceIdが返されます。テストが早期に終了した場合、 TraceId値はテスト終了イベントでスクリプトに渡されます。

  -- Stops the specified trace. EXEC sp_trace_setstatus @traceid = @traceid , @status = 0 -- Closes the specified trace and deletes its definition from the server. EXEC sp_trace_setstatus @traceid = @traceid , @status = 2

1.2。半自動スタート

テストの開始時にsqlクエリを自動的に実行できない場合は、 Script.01.Start trace.sqlクエリをSQL Management Studioから手動で実行して、スクリプトパラメータを事前に決定しておくことができます。

トレースの開始時に停止時間を計算できない場合、 stopTraceTimeパラメーターをsp_trace_createプロシージャに渡す必要はありません（Script.01.Start trace.sql：29を参照）。

1.2.1。トレースを停止

スクリプト（たとえば、ディレクトリ（D：\ Traces \ Synerdocs \））から停止するトレースセッションを選択して、プロファイルを停止することもできます。

 -- Script.02.Stop trace.sql DECLARE @traceid INT SET @traceid = (SELECT TOP 1 [id] FROM sys.traces WHERE [path] LIKE 'D:\Traces\Synerdocs\%') IF @traceid IS NOT NULL BEGIN -- Stops the specified trace. EXEC sp_trace_setstatus @traceid = @traceid , @status = 0 -- Closes the specified trace and deletes its definition from the server. EXEC sp_trace_setstatus @traceid = @traceid , @status = 2 END SELECT * FROM sys.traces

1.3。手動スタート

Microsoft SQL Server Profilerを使用すると、多数のイベントを収集できます。

sp_trace_setevent （ http://msdn.microsoft.com/en-us/library/ms186265.aspx ）を参照してください。

最も興味深いイベントとして、エラー、ロック、プロシージャおよびクエリの実行を取り上げます。

ID（イベントID）	イベント名	説明（イベントの説明）
162	ユーザーエラーメッセージ	エラーまたは例外の場合にユーザーに表示されるエラーメッセージを表示します
148	デッドロックグラフ	試行がデッドロックの一部であり、デッドロックの犠牲者として選択されたために、ロックの取得の試行がキャンセルされたときに発生します。
10	RPC：完了	リモートプロシージャコール（RPC）が完了したときに発生します。
12	SQL：BatchCompleted	Transact-SQLバッチが完了したときに発生します。

選択したイベントには、最も興味深い列があります。

Columnid	列名	種類	間違い	ロック	手続き	お問い合わせ
1	テキストデータ	ntext	+	+	+	+
13	期間	ビッグ			+	+
14	開始時間	日時	+	+	+	+
16	読みます	ビッグ			+	+
17	書きます	ビッグ			+	+
18	CPU	int			+	+
31	エラー	int	+		+	+
34	オブジェクト名	nvarchar			+
35	DatabaseName	nvarchar	+		+	+

トレースに追加の列を含めます。これらはレポートでは使用されませんが、手動分析に役立ちます。

Columnid	列名	種類	間違い	ロック	手続き	お問い合わせ
4	TransactionID	ビッグ	+	+	+	+
9	ClientProcessID	int	+		+	+
10	ApplicationName	nvarchar	+		+	+
11	LoginName	nvarchar	+	+	+	+
12	SPID	int	+	+	+	+
15	終了時間	日時			+	+
48	行数	ビッグ			+	+
49	RequestID	int	+		+	+
50	Xactsequence	ビッグ	+		+	+

DatabaseName （ ColumnID 35）によるトレースのフィルタリングは、選択したSQL Serverデータベースに対してのみ実行されます（テストサーバーでは、多くの場合、多くのデータベースがあるため、必要なデータベースのみを選択する必要があります）。

tempdb ;
テスト対象のアプリケーションのデータベース。

フィルタリングとグループ化もLoginNameによって便利に実行されます。

トレースを手動で実行するのが便利な場合、 Microsoft SQL Server Profilerを使用すると、プロファイリング結果をすぐに表示できます。

1.3.1。構成済みパラメーターを自動化のためのスクリプトに変換する

異なるイベント、列、フィルターのセットがある場合。また、このような一連のパラメーターを使用してトレースの開始を自動化する場合は、 SQL Server Profilerが役立ちます。

自動トレース開始の設定：

Microsoft SQL Server Profilerを起動します。
プロファイルされたイベントを選択し、プロファイルされたイベントの属性を選択します。
フィルタリングパラメーターを（ DatabaseNameまたはLoginNameで ）設定すると、 列フィルター...ボタンをクリックしてパラメーターが開きます。
メニュー項目「 ファイル/エクスポート/スクリプトトレース定義/ For SQL Server 2005-2008 R2 ... 」を使用して、「トレースプロパティ」ウィンドウのイベント選択タブで指定されたトレースパラメーターを保存します。

これにより、スクリプトが準備されます。スクリプトテキストに類似：

プロファイリング設定で生成されたスクリプト

 /****************************************************/ /* Created by: SQL Server 2008 R2 Profiler */ /* Date: 21/01/2014 10:23:02 */ /****************************************************/ -- Create a Queue declare @rc int declare @TraceID int declare @maxfilesize bigint set @maxfilesize = 5 -- Please replace the text InsertFileNameHere, with an appropriate -- filename prefixed by a path, eg, c:\MyFolder\MyTrace. The .trc extension -- will be appended to the filename automatically. If you are writing from -- remote server to local drive, please use UNC path and make sure server has -- write access to your network share exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL if (@rc != 0) goto error -- Client side File and Table cannot be scripted -- Set the events declare @on bit set @on = 1 exec sp_trace_setevent @TraceID, 162, 31, @on exec sp_trace_setevent @TraceID, 162, 1, @on exec sp_trace_setevent @TraceID, 162, 9, @on exec sp_trace_setevent @TraceID, 162, 49, @on exec sp_trace_setevent @TraceID, 162, 10, @on exec sp_trace_setevent @TraceID, 162, 14, @on exec sp_trace_setevent @TraceID, 162, 50, @on exec sp_trace_setevent @TraceID, 162, 11, @on exec sp_trace_setevent @TraceID, 162, 35, @on exec sp_trace_setevent @TraceID, 162, 4, @on exec sp_trace_setevent @TraceID, 162, 12, @on exec sp_trace_setevent @TraceID, 162, 20, @on exec sp_trace_setevent @TraceID, 148, 11, @on exec sp_trace_setevent @TraceID, 148, 4, @on exec sp_trace_setevent @TraceID, 148, 12, @on exec sp_trace_setevent @TraceID, 148, 14, @on exec sp_trace_setevent @TraceID, 148, 1, @on exec sp_trace_setevent @TraceID, 10, 15, @on exec sp_trace_setevent @TraceID, 10, 31, @on exec sp_trace_setevent @TraceID, 10, 16, @on exec sp_trace_setevent @TraceID, 10, 48, @on exec sp_trace_setevent @TraceID, 10, 1, @on exec sp_trace_setevent @TraceID, 10, 9, @on exec sp_trace_setevent @TraceID, 10, 17, @on exec sp_trace_setevent @TraceID, 10, 49, @on exec sp_trace_setevent @TraceID, 10, 10, @on exec sp_trace_setevent @TraceID, 10, 18, @on exec sp_trace_setevent @TraceID, 10, 34, @on exec sp_trace_setevent @TraceID, 10, 50, @on exec sp_trace_setevent @TraceID, 10, 11, @on exec sp_trace_setevent @TraceID, 10, 35, @on exec sp_trace_setevent @TraceID, 10, 4, @on exec sp_trace_setevent @TraceID, 10, 12, @on exec sp_trace_setevent @TraceID, 10, 13, @on exec sp_trace_setevent @TraceID, 10, 14, @on exec sp_trace_setevent @TraceID, 12, 15, @on exec sp_trace_setevent @TraceID, 12, 31, @on exec sp_trace_setevent @TraceID, 12, 16, @on exec sp_trace_setevent @TraceID, 12, 48, @on exec sp_trace_setevent @TraceID, 12, 1, @on exec sp_trace_setevent @TraceID, 12, 9, @on exec sp_trace_setevent @TraceID, 12, 17, @on exec sp_trace_setevent @TraceID, 12, 49, @on exec sp_trace_setevent @TraceID, 12, 10, @on exec sp_trace_setevent @TraceID, 12, 14, @on exec sp_trace_setevent @TraceID, 12, 18, @on exec sp_trace_setevent @TraceID, 12, 50, @on exec sp_trace_setevent @TraceID, 12, 11, @on exec sp_trace_setevent @TraceID, 12, 35, @on exec sp_trace_setevent @TraceID, 12, 4, @on exec sp_trace_setevent @TraceID, 12, 12, @on exec sp_trace_setevent @TraceID, 12, 13, @on -- Set the Filters declare @intfilter int declare @bigintfilter bigint exec sp_trace_setfilter @TraceID, 35, 0, 6, N'ServiceDB' exec sp_trace_setfilter @TraceID, 35, 1, 6, N'ClientDB' exec sp_trace_setfilter @TraceID, 35, 1, 6, N'LogsDB' exec sp_trace_setfilter @TraceID, 35, 1, 6, N'Tempdb' -- Set the trace status to start exec sp_trace_setstatus @TraceID, 1 -- display trace id for future references select TraceID=@TraceID goto finish error: select ErrorCode=@rc finish: go

[トレースプロパティ]ウィンドウの[ 全般 ]タブのパラメーターは、トレーススクリプトの準備に含まれません。スクリプトに手動で追加される必要なパラメーターがあります。

ファイルに保存 ;
最大ファイルサイズ（MB）を設定します 。
ファイルのロールオーバーを有効にします 。
トレース停止時間を有効にします 。

ファイル名とトレース停止時間は負荷テストコードから設定され、最大ファイルサイズと新しいファイルを作成する必要があるという記号は、定数によって便利に設定されます。 Script.01.Start trace.sqlが上記で作成されたとおりです。

パラメーターの説明最大ファイルサイズ（MB）を設定し 、 ファイルロールオーバーを有効にします ： http : //msdn.microsoft.com/en-us/library/ms191206.aspx

トレースファイルをデータベースにアップロードする

レポートを生成するために、トレースファイルがデータベーステーブルにロードされます。

データベースが事前に作成され、トレースがこのデータベースのテーブルにロードされ、レポートを含むテーブルが作成されます。データベースにProfilerResultsという名前を付けることができます。データベース名には、プロジェクトの名前を反映すると便利です。プロジェクトのレポートは、プロジェクトに蓄積されます。私が取り組んでいるプロジェクトはかつてMidwayと呼ばれていたため、データベースはMidwayProfilerResultsと呼ばれていました 。データベースを作成するには、 SQL Management Studioを使用すると便利です。SQLManagement Studioでは、データベースを作成するためのウィザードがあり、数回クリックするだけで完了です。

システム関数fn_trace_gettableがロードに使用されます。

 use "ProfilerResults" SELECT "EventClass" , "TextData" , "Duration" , "StartTime" , "Reads" , "Writes" , "CPU" , "Error" , "ObjectName" , "DatabaseName" , "TransactionID" , "ClientProcessID" , "ApplicationName" , "LogiName" , "SPID" , "EndTime" , "RowCounts" , "RequestID" , "XactSequence" INTO "SOAP.v2.3.1.5577" FROM ::fn_trace_gettable(N'D:\Traces\Synerdocs\LoadTest.SOAP.Trace.StartOn 2013.01.21 08.30.00.trc', default)

fn_trace_gettableパラメーターは、最初のトレースファイルへのパスを指定し、2番目のデフォルトパラメーターは、同じトレースセッションからのすべてのファイルがテーブルにロードされることを意味します（サイズが100 MBの指定ファイルだけでなく）。

プロファイリングテーブルの名前は、テスト対象のシステムのバージョンと負荷プロファイルを反映するように設定されます（例：「SOAP.v2.3.1.5577」）。レポート付きで作成されたテーブルには、それらを形成するプロファイリングテーブルの名前が名前に含まれます。将来、多くのレポートの中で目的のテーブルを見つけるには、テーブルに意味のある名前を設定することをお勧めします。

ハードウェアによっては、ダウンロードに時間がかかる場合があります。使用するサーバーでは、ダウンロード速度は次のとおりです。160〜190 MB /分または1.5-1分あたり2つのトレースファイル（1つのファイル-100 MB）。トレースファイルをデータベースにロードする最近の2つの操作の測定（通常のロードプロファイルと大きなファイルのロードプロファイル）。

トレースファイルサイズ（MB）	ダウンロード時間（h：min：sec）	平均ダウンロード速度（MB /分）	トレーステーブルサイズ（MB）	行数	平均テーブル行サイズ（KB）	テーブルへのロード後のサイズ増加率
7 598	00:45:17	168.8	29,478.3	766 370	39,4	3.9
67 062	05:46:32	193.8	268 875.7	2 861 599	96.2	4.0

2.手順の要約レポート（RPC：完了）-「ドラフトレポート」

過度に徹底的な分析は真実をゆがめます。

-フランク・ハーバートの小説「砂丘のメサイア」からの古代フリーマンの発言

データベースの操作が主にデータベースAPI（ストアドプロシージャと関数のセット）を介して実行される場合、このレポートは最初に作成するものです。

このレポートは、 RPCのグループ化です。トレーステーブルからの完了イベントは、データベース名（ DatabaseName ）および保存されたプロセスまたは関数の名前（ ObjectName ）で、最も速いクエリと最も遅いクエリの統計とテキストを含みます SQL統計：BatchCompletedもレポートにありますが、これは1行のみです-細分化なしのNULL行。したがって、このレポートは「ドラフトレポート」と呼ばれます。

ドラフトレポートの構築を自動化するには、補助ツールTools.SQLProfilerReportHelperが使用されます。

2.1。レポート「ドラフトレポート」の作成の準備

準備は、 Tools.SQLProfilerReportHelperフォームのボタンを押すことです。

Tools.SQLProfilerReportHelperを起動します。
[ SQL Server]フィールドと[ データベース]フィールドに、 SQL Serverとプロファイリングデータベースの名前を入力します。例では、これらは「SQL Server」と「ProfilerResults」です。 [ 接続 ]ボタンをクリックします。 SQL ServerでWindows認証を使用する場合、ユーザーはプロファイリングデータベースの管理者でなければなりません。
[ プロファイリングテーブル]リストからプロファイリング結果を含むテーブルを選択します。例では、これは「TraceTable.v2.7.LoadProfile1.2010.10.10」です。
プロファイリングテーブルに新しいTextKey列を作成します-[ チェック ]ボタンをクリックしてテーブル内の列の有無を確認し 、列が存在しない場合（[ 作成 ]ボタンが使用可能になります）、[ 作成 ]ボタンをクリックします。
スタートボタンを押します：SP クリックすると、 ObjectName列からTextKey列に値がコピーされます。

ObjectNameをTextKey列にコピーする場合、処理するレコードの数。そして、残りの操作期間が計算されます。 Stop：SPボタンを押すと、プロセスを中断できます。

2.2。レポート「ドラフトレポート」の作成

コピーが完了したら、ドラフトレポートを作成します。これを行うには、[ チェック ]ボタンをクリックし（レポートのあるテーブルの有無を確認します）、レポートのあるテーブルがない場合は、[ 作成 ]をクリックします。結果として、名前に「DraftStat」の末尾が付いたテーブルが作成されます。この例では、 TraceTable.v2.7.LoadProfile1.2010.10.10.DraftStatです。テーブルのサイズは比較的小さい（10〜30 MB）。このテーブルは、ストアドプロシージャと関数のパフォーマンスに関する統計の長期保存を目的としています。

クエリによってテーブルの内容を取得します。

 USE "ProfilerResults" SELECT * FROM "dbo"."TraceTable.v2.7.LoadProfile1.2010.10.10.DraftStat"

クエリは、 SQL Management Studioで便利に実行されます。

クエリテキストTextData -....を含む列を除くすべての列をクリップボードにコピーします。

DtaftStatテーブルの列の構成（ドラフトレポート）

コラム	種類	説明
DatabaseName	nvarchar（256）	データベース（グループ化キー）。
ObjectName-キー	nvarchar（256）	ストアドプロシージャまたは関数の名前（グループ化キー）。
平均（CPU）-キー	int	イベントで使用されるミリ秒単位のCPU時間（平均）。
avg（期間）-key	ビッグ	イベントの期間（平均）（ミリ秒）。
％継続時間キー	浮く	イベントの合計期間の共有（ソートキー）。
avg（読み取り）-key	ビッグ	サーバーがイベントに対して実行した論理ディスク読み取りの数（平均）。
カウントキー	int	呼び出し（ピース）の数。
オブジェクト名	nvarchar（256）	ストアドプロシージャまたは関数の名前（グループ化キー）。
平均（CPU）	int	CPU列の平均値（イベントで使用されるミリ秒単位の平均CPU時間）。
最大（CPU）	int	イベントで使用されるミリ秒単位の最大CPU時間。
合計（CPU）	int	イベントで使用されるミリ秒単位の合計CPU時間。
％CPU	浮く	イベントの合計CPU時間のシェア。
分（期間）	ビッグ	イベントの期間（ミニ）ミリ秒。
平均（期間）	ビッグ	イベントの期間（平均）（ミリ秒）。
max（期間）	ビッグ	イベントの期間（最大）（ミリ秒）。
sum（期間）	ビッグ	イベントの期間（合計）（ミリ秒）。
継続時間の割合	浮く	イベントの合計期間のシェア。
min（読み取り）	ビッグ	イベントに対してサーバーが実行した論理ディスク読み取りの数（最小）。
平均（読み取り）	ビッグ	サーバーがイベントに対して実行した論理ディスク読み取りの数（平均）。
max（読み取り）	ビッグ	イベントに対してサーバーが実行した論理ディスク読み取りの数（最大）。
sum（読み取り）	ビッグ	サーバーがイベントに対して実行した論理ディスク読み取りの数（合計）。
読み取り率	浮く	サーバーがイベントに対して実行した論理ディスク読み取りの割合。
min（書き込み）	ビッグ	イベントに対してサーバーが実行したレコードあたりの物理ディスクアクセス数（ミニ）。
avg（書き込み）	ビッグ	イベントに対してサーバーが実行したレコードあたりの物理ディスクアクセス数（平均）。
max（書き込み）	ビッグ	イベントに対してサーバーが実行したレコードあたりの物理ディスクアクセス数（最大）。
sum（書き込み）	ビッグ	イベントに対してサーバーが実行したレコードごとの物理ディスクアクセス数（合計）。
書き込み率	浮く	イベントに対してサーバーによって行われた書き込みごとの物理ディスク書き込み数の割合。
カウント	int	イベントの数。
％カウント	浮く	イベントの数の割合。
TextData-min（期間）	ntext	実行時間が最小のクエリテキスト。
TextData-max（期間）	ntext	実行時間が最大のクエリテキスト。
TextData-min（読み取り）	ntext	サーバーが実行する論理ディスク読み取りの数が最小限の要求テキスト。
TextData-max（読み取り）	ntext	サーバーによって行われる論理読み取りの最大数が最大となるクエリテキスト。
TextData-min（CPU）	ntext	CPU時間が最小の要求テキスト。
TextData-max（CPU）	ntext	CPU時間が最大のリクエストテキスト。
TextData-min（書き込み）	ntext	サーバーによって行われる物理ディスク書き込みの数が最小となる要求テキスト。
TextData-max（書き込み）	ntext	サーバーによって行われた物理ディスク書き込みの最大数が最大値に達する要求テキスト。

Microsoft Office Excelでレポートテンプレートを開きます（自動セル書式設定付きのテーブル-悪い値から良い値への値の強調表示）。クリップボードの内容をドキュメントに貼り付けます。

ドキュメントは、 ％Duration列で降順でソートされた大きな色付きのテーブルのように見えます。

最初の6列は残りから分離されており（そして複製されます）、主な列です。これらの6つの主要な列は、 ExcelドキュメントからWordドキュメントに便利にコピーされます。

「NULL」キーと「sp_executesql」キーのある行は、クエリ呼び出し（ SQL：BatchCompleted ）およびストアドプロシージャsp_executesqlを呼び出して実行されたクエリの統計です。そのようなリクエストが少なく（ カウント列）、その実行期間の割合が小さい（ ％期間列）場合、詳細レポートを作成できず、ドラフトレポートの分析に限定されます。

2.3。ドラフトレポートの分析

最大値が％Durationおよびavg（Reads）のストアドプロシージャに注意してください。

多くの場合、これらの呼び出しは、欠落しているインデックスを追加することで高速化できます。 SQL Management Studioは、どのインデックスを追加するのが最適かを教えてくれます。アドバイスの正確性は絶対的なものではなく、高いものです。

クエリが最適でインデックスが適切である場合がありましたが、ストアドプロシージャのテキストでは、大きなテーブルのすべてのレコードが（デバッグ用に）選択されました-これにより、クエリは％Durationレポートで先頭に立ちました。 SQL Management Studioでクエリを実行すると、このような膨大な選択のケースが明らかになります。レポートによると、そのような瞬間を特定することはできません。レポートのRowCount列に統計を含めません（これを含める必要があります）。

％DurationとCountの値が大きい呼び出しをキャッシュできる場合があると思います（レコードを製品の "％Duration" x "Count"の降順に並べ替えることができます）。

おそらく、 ％Durationとavg（CPU）の値が大きい呼び出しでは、文字列、数学、再帰の処理が最適ではありません。

％Durationおよびavg（Write）の値が大きい呼び出しでは、一時テーブルを最適に使用できない場合があります。または、インデックスと複雑なトリガーが多すぎるテーブルへの挿入を指します。

期間、論理読み取り値、CPU時間の最小値と最大値を含むクエリテキストは、レポートテーブルの列から抽出されます（レポートドキュメントにはそのような列はありません）。

TextData-min（期間）;
TextData-max（期間）;
TextData-min（読み取り）;
TextData-max（読み取り）;
TextData-min（CPU）;
TextData-max（CPU）;
TextData-min（書き込み）;
TextData-max（書き込み）。

テキストはSQL Management Studioで分析されます。

3.クエリの概要レポート（RPC：完了+ SQL：BatchCompleted）-詳細レポート

ドラフトレポートで、キーが "NULL"および "sp_executesql" の行が多くのクエリの実行詳細を隠した場合。次に、 詳細レポートでグループ化が可能な限り詳細に実行されます。

詳細レポートの作成は、 ドラフトレポートがすでに生成されており、準備アクションが完了していることを前提としています。

3.1。詳細レポート作成の準備

場合は報告書案が形成され、それはするのに十分です：

Function PrepareTextDataグループのCheckボタンをクリックして、PrepareTextData関数がデータベース内にあることを確認します（必要に応じてCreateボタンをクリックして関数を作成します）。
Cformirovatキー（グループ・イベントのためのクエリテキストのグループ化SQL：BatchCompleted列上のTextData）を押すと、Etpuスタート：SQL。

それ以外の場合は、最初からすべてを行う必要があります。プロファイリングテーブルを使用してデータベースに接続し、プロファイリングテーブルを選択し、TextKey列を作成し、[ 開始：SP ]ボタンをクリックして、ストアドプロシージャのグループ化キーを入力します。アクションは、ツールフォームで使用可能なボタンをクリックすることで実行されます。押す必要のないボタンはアクセスできなくなります。

3.2。レポート作成

詳細レポートステータスの [ チェック ]ボタンをクリックして、詳細レポートの存在ステータスを確認します。
作成されていない場合は、レポートを作成します（[ 作成 ]ボタンをクリックできる場合は、クリックします）。

その結果、新しいレポートテーブルが作成され、その名前は「DetailStat」で終わります。

列の説明は、ドラフトレポートの列の説明と同じです。ただし、この表にはさらに行があります（レポートの詳細）。

これらのテーブルDetailStatはまた、クリップボードにコピーし、用としてレポートテンプレートに挿入された報告書案。

3.3。レポート分析

詳細レポート（詳細レポート）の分析は、ドラフトレポート（ドラフトレポート）の分析に似ています。ただし、クエリテキストを表示およびダウンロードする追加の機会があります。

[ 表示 ]ボタンをクリックすると、詳細レポートの内容を含むウィンドウが開きます。

。

ウィンドウ「ReportViewForm」で、レコードを表示してソートできます。クリックされたリクエストのテキストは、プログラムフォルダーにファイルとして保存されます。タブには、リクエストテキスト、リクエストテキストの最初の10,000文字が表示されます。

クエリテキストをダウンロードするこの方法は、大きなリクエストを分析できるようにするために作成されました。したがって、100〜700 MBのサイズのドキュメントを作成する操作がtresに含まれている場合、これらの操作のクエリテキストは膨大です。これらは非常に大きいため、RAMでさえ「select * from ... DetailStat」の要求には不十分な場合があります。DetailStatレポートテーブルを使用する場合そのような特別なビューアを介して、レコードは1つの一般的なリクエストではなく、個別のリクエストによって取得されます。これにより、大規模なクエリのテキストをアップロードできます。レポートに大きなクエリがない場合は、SQL Management Studioから作業する方が便利です。

クエリテキストをファイルに順次アンロードすると、大きなクエリテキスト（サイズが500 MB以上のアンロードされたクエリテキスト）を操作する際のメモリ不足の問題を解決できます。

産業開発

説明したMicrosoft SQL Serverトレースの収集および分析方法は、2つの製品をテストするときにテストされました。どちらの製品も会計に関連しています。

OLTPシステム（Synerdocs）

小さいデータの頻繁なサンプリング、個々のレコード（着信/発信、ドキュメント、署名）の頻繁な更新。多くのユーザー（1000〜2000人のテストユーザー）。ストアドプロシージャおよび関数（APIデータベース）への多くの呼び出し。ストアドプロシージャ呼び出しではないSQLクエリ呼び出しはほとんどありません。負荷テスト中のクエリは数十万件増加しています。要求は数百のグループに収集されます。

実行のコレクションは、負荷テストの期間（数時間）に起動されます。その後、数十ギガバイトのトレースが分析され、レポートに変換されます。

テストは定期的に実施されます。ある時点で、明らかなボトルネックを見つけることが困難になりました。詳細な分析には計器が必要でした。レポートを簡単に比較できるように（Excelの列に収まるようにグループ化キーは短くする必要があります）。そして、上記のレポートが登場しました。

OLAPシステム（Prestima）

大量のデータの選択とグループ化、個々のレコードの更新（予算、アカウント、投稿）。テスト中のユーザーは比較的少ない（負荷は、レポートの生成を開始する生きている人々によって作成されます）。動的に生成されるクエリの多く（長いクエリテキスト、2つのクエリの違いは、JOINを含むセクションでは200番目の文字、WHEREセクションでは250番目の文字のみ）。さまざまな条件を持つ多くのテーブルへの分析クエリ。期間に対するクエリ基準の明確な効果。最速からの最長リクエストは、リクエストの構造ではなく、パラメータ値が異なる場合があります。

実行のコレクションは、機能テストおよび体積テストの期間中（おそらく数日）開始されます。トレースは定期的に分析され、レポートに変換されます。

複雑なクエリの統計が1つのグループに分類されないようにするには、グループ化キーが正確でなければなりません。PrepareTextDataプロシージャでは、計算されたキーの長さを指定できます。また、トレースの量が少ない場合、長いグループ化キーの使用は非常に普通です（レポートの速度は高いままです）。

著者とリンク

この記事で紹介する分析レポートは、Mikhail Izvekov（Computer NPOの開発者）が作成したsqlクエリの子孫です。リクエストはシンプルで美しいです：

 --    REPLACE        X.  @Len   , --        .     , --       ,   ,        . --    26    IContents         "select distinct X as Ob", --      ,        , --      4000,        -- ( ,       ..). declare @Hours float; declare @CPUSumm int; declare @Len int = 26; select @CPUSumm = SUM(CPU) from Test -- ,    . select @Hours = CAST(MAX(StartTime) - MIN(StartTime) as float) * 24 from Test -- ,    . select SUBSTRING(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CAST(TextData as nvarchar(4000)),'0','X'),'1','X'),'2','X'),'3','X'),'4','X'),'5','X'),'6','X'),'7','X'),'8','X'),'9','X'),'XX','X'),'XX','X'),'XX','X'), 1, @Len) as [Query Pattern], sum(CPU) / 1000 as CPU_sec_SUM, avg(CPU) as CPU_msec_AVG, max(CPU) as CPU_msec_MAX, min(CPU) as CPU_msec_MIN, avg(Duration) / 1000 as [AVG_Duration (msec)], avg(Reads) as [Avg Reads], avg(Writes) as [Avg Writes], count(1) as TotalQnt, round(count(1) / @Hours, 2) as Count_per_hour, round(cast(SUM(CPU) as float) / @CPUSumm * 100, 3) as ImpactCPUPerf from Test -- ,    . where EventClass in (10, 12) and ISNULL(ApplicationName, 'Unknown') in ('SBRSE', 'IS-Builder', 'Unknown') group by SUBSTRING(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CAST(TextData as nvarchar(4000)),'0','X'),'1','X'),'2','X'),'3','X'),'4','X'),'5','X'),'6','X'),'7','X'),'8','X'),'9','X'),'XX','X'),'XX','X'),'XX','X'), 1, @Len)

リクエストマイケルは、元の形式では使用できませんでした。30〜40 GBのトレースの分析には時間がかかります。また、トレース内のリクエストのテキストは、グループ化をまったく望まないようなものでした。短いキー（10文字）を使用すると、すべての選択要求と更新要求が混在していました。また、長いキー（30文字以上）を使用すると、GUIDパラメーターを使用してストアドプロシージャを呼び出すと、同様のグループ化キーが何千も作成されます。

 exec [dbo].[getDoc] @id="128500FF-8B90-D060-B490-00CF4FC964FF" --exec [dbo].[getDoc] @id="XFF-X exec [dbo].[getDoc] @id="AABBCCDD-0E0E-1234-B491-0D43C5F6C0F6" --exec [dbo].[getDoc] @id="AABBC

グループ化キーを計算するためのさまざまなオプションを試し始めました。結果は、プロファイリングテーブルの新しい列に記録されました。そして、この列はすでに結果をグループ化しています。そして、それはすべて始まりました。

より最近では、金曜日に、彼らはミーシャと一緒に座って困難な人生について話しましたが、あごひげが濃い。そして、記事を書くことを彼に伝えるのを忘れました。

それから、マイケルのスクリプトのライセンスはまだわかりません。また、SQLProfilerReportHelperツールのライセンスはBSDであり、無料です。負荷テストにトレーススクリプトを埋め込み、トレースを分析し、レポートを生成します。

このツールはGithubで入手できます：https : //github.com/polarnik/SQLProfilerReportHelper。

この記事に掲載されているレポートの写真は古いため、あまり秘密ではありません。レポートの図と、作成中のツールのテーブルレポートの列の構成に矛盾があることに気付いた場合は、理解して扱ってください。

完了

このガイドでは、SQL Serverプロファイリングデータに基づいてグループ化されたレポートをコンパイルするプロセスについて説明します。これはパフォーマンステスト作業の10分の1に過ぎません。しかし、明るく重要です。

記事の次の部分では、トレースに基づいて生成される他のレポートについて説明します。

Microsoft SQL Serverトレース分析レポート

問題の声明

1.トレース

1.1。 オートスタート

1.1.1。 トレースを停止

1.1.2。 早期終了

1.2。 半自動スタート

1.2.1。 トレースを停止

1.3。 手動スタート

1.3.1。 構成済みパラメーターを自動化のためのスクリプトに変換する