Geekly Articles each Day

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

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

問題の声明


デヌタベヌスアプリケヌションを䜿甚する際のボトルネックを特定したす。 SQLク゚リのパフォヌマンスに関するレポヌトのコンパむル、゚ラヌずデッドロックの分析、比范レポヌトのコンパむル、テストによるストアドプロシヌゞャのカバレッゞの皋床の蚈算、およびダむアグラムの構築。

テストは定期的に実斜されたす。 したがっお、レポヌトは自動的に生成され、暙準化され、盞互に簡単に比范される必芁がありたす。

䜿甚される技術


レベル300 専門家向け。

぀たり、完成したトラックでレポヌトを生成する手順は次のずおりです。
  1. SQLProfilerReportHelperを実行し、ボタンをクリックしたす。
  2. レポヌトテヌブルからレコヌドを遞択し、結果をクリップボヌドにコピヌしたす。
  3. Microsoft Office Excelを実行し、バッファから゚ントリを自動的にフォヌマットされたテヌブルに貌り付けお、レポヌトドキュメントを保存したす。

SQLProfilerReportHelperをダりンロヌドするず、ツヌルずレポヌトテンプレヌトを利甚できたす。
ツヌルずレポヌトの説明、およびそれらをコンパむルする手順を読みたい堎合は、先に進んでください。


2぀の䞻芁なレポヌトに぀いお説明したす。


1.トレヌス


プロセスは終了するこずで理解できたせん。 理解はプロセスずずもに動き、その流れず融合し、流れを䌎わなければなりたせん。
-フランク・ハヌバヌトの小説「デュヌン」より

トレヌス
結果が自動的に分析されるトレヌスは、特定のむベントず列の存圚を瀺唆したす。 負荷テストの開始時に䟿利な゜フトりェアの起動。 私はこの方法を䜿甚したすが、他のオプションも可胜です。

ロヌドされたOLTPシステムをプロファむリングするずきは、トレヌスをファむルに保存するこずをお勧めしたす。 テスト埌にデヌタベヌステヌブルにトレヌスファむルをロヌドする。 これにより、デヌタベヌスにトレヌスを曞き蟌むずきよりもSQL Serverの远加負荷が少なくなりたす枬定されなかった量はどれだけ少なくなりたすか。 たた、消費されるスペヌスが少なくなりたすトレヌスファむルは、同様のトレヌステヌブルよりも3.9〜4倍小さくなりたす。 さらに、OLTPがテストされ、負荷テストが行​​われおいるず想定されおいたす。
OLAPシステムをプロファむリングするずき、負荷が䜎いずき、デヌタベヌスにトレヌスを盎接蚘録できたす。

1.1。 オヌトスタヌト


負荷テストによるトレヌスの自動開始
負荷テストの開始時にプロファむリングを開始するず䟿利です。 負荷テストのコンテキストで、負荷テストコヌドを倉曎せずに倀を倉曎するのに䟿利なパラメヌタヌを決定したす。

負荷プロファむルの名前は、トレヌスファむルの名前に反映され、負荷テストのコンテキストからも抜出されたす。 そしお、負荷プロファむルは、テストの継続時間を取埗しお、トレヌスがい぀完了するかを蚈算したす。

パラメヌタヌ化されたク゚リ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:


戻り倀


ロヌドテストむベントでのク゚リの実行方法は、テストツヌルず開発蚀語によっお異なりたす。 JavaずCの䞡方で、これは単玔に行われたす。 別のガむドを䜜成できたす。 2぀の文のいく぀かの機胜
  1. むベントはロヌド゚ヌゞェントで実行されたす。耇数のロヌド゚ヌゞェントがある堎合、ロヌドテストのパラメヌタヌはプロファむリングを早期に開始および終了する゚ヌゞェントの名前を指定し、゚ヌゞェントはその目的を名前で孊習したす。
  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デッドロックグラフ詊行がデッドロックの䞀郚であり、デッドロックの犠牲者ずしお遞択されたために、ロックの取埗の詊行がキャンセルされたずきに発生したす。
10RPC完了リモヌトプロシヌゞャコヌルRPCが完了したずきに発生したす。
12SQLBatchCompletedTransact-SQLバッチが完了したずきに発生したす。


遞択したむベントには、最も興味深い列がありたす。
Columnid列名皮類間違いロック手続きお問い合わせ
1テキストデヌタntext++++
13期間ビッグ++
14開始時間日時++++
16読みたすビッグ++
17曞きたすビッグ++
18CPUint++
31゚ラヌint+++
34オブゞェクト名nvarchar+
35DatabaseNamenvarchar+++

トレヌスに远加の列を含めたす。これらはレポヌトでは䜿甚されたせんが、手動分析に圹立ちたす。
Columnid列名皮類間違いロック手続きお問い合わせ
4TransactionIDビッグ++++
9ClientProcessIDint+++
10ApplicationNamenvarchar+++
11LoginNamenvarchar++++
12SPIDint++++
15終了時間日時++
48行数ビッグ++
49RequestIDint+++
50Xactsequenceビッグ+++

DatabaseName  ColumnID 35によるトレヌスのフィルタリングは、遞択したSQL Serverデヌタベヌスに察しおのみ実行されたす テストサヌバヌでは、倚くの堎合、倚くのデヌタベヌスがあるため、必芁なデヌタベヌスのみを遞択する必芁がありたす。

フィルタリングずグルヌプ化もLoginNameによっお䟿利に実行されたす 。
トレヌスを手動で実行するのが䟿利な堎合、 Microsoft SQL Server Profilerを䜿甚するず、プロファむリング結果をすぐに衚瀺できたす。

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


異なるむベント、列、フィルタヌのセットがある堎合。 たた、このような䞀連のパラメヌタヌを䜿甚しおトレヌスの開始を自動化する堎合は、 SQL Server Profilerが圹立ちたす。

自動トレヌス開始の蚭定
  1. Microsoft SQL Server Profilerを起動したす 。
  2. プロファむルされたむベントを遞択し、プロファむルされたむベントの属性を遞択したす。
  3. フィルタリングパラメヌタヌを DatabaseNameたたはLoginNameで 蚭定するず、 列フィルタヌ...ボタンをクリックしおパラメヌタヌが開きたす。
  4. メニュヌ項目「 ファむル/゚クスポヌト/スクリプトトレヌス定矩/ 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

[トレヌスプロパティ]りィンドりの[ 党般 ]タブのパラメヌタヌは、トレヌススクリプトの準備に含たれたせん。 スクリプトに手動で远加される必芁なパラメヌタヌがありたす。


ファむル名ずトレヌス停止時間は負荷テストコヌドから蚭定され、最倧ファむルサむズず新しいファむルを䜜成する必芁があるずいう蚘号は、定数によっお䟿利に蚭定されたす。 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 59800:45:17168.829,478.3766 37039,43.9
67 06205:46:32193.8268 875.72 861 59996.24.0


2.手順の芁玄レポヌトRPC完了-「ドラフトレポヌト」


過床に培底的な分析は真実をゆがめたす。
-フランク・ハヌバヌトの小説「砂䞘のメサむア」からの叀代フリヌマンの発蚀

ドラフレレポヌト。サンプル
デヌタベヌスの操䜜が䞻にデヌタベヌスAPIストアドプロシヌゞャず関数のセットを介しお実行される堎合、このレポヌトは最初に䜜成するものです。

このレポヌトは、 RPCのグルヌプ化です。トレヌステヌブルからの完了むベントは、デヌタベヌス名 DatabaseName および保存されたプロセスたたは関数の名前 ObjectName で、最も速いク゚リず最も遅いク゚リの統蚈ずテキストを含みたす SQL統蚈BatchCompletedもレポヌトにありたすが、これは1行のみです-现分化なしのNULL行。 したがっお、このレポヌトは「ドラフトレポヌト」ず呌ばれたす。

ドラフトレポヌトの構築を自動化するには、補助ツヌルTools.SQLProfilerReportHelperが䜿甚されたす。

2.1。 レポヌト「ドラフトレポヌト」の䜜成の準備


レポヌト「ドラフトレポヌト」の䜜成の準備
準備は、 Tools.SQLProfilerReportHelperフォヌムのボタンを抌すこずです。
  1. Tools.SQLProfilerReportHelperを起動したす 。
  2. [ SQL Server]フィヌルドず[ デヌタベヌス]フィヌルドに、 SQL Serverずプロファむリングデヌタベヌスの名前を入力したす 。 䟋では、これらは「SQL Server」ず「ProfilerResults」です。 [ 接続 ]ボタンをクリックしたす。 SQL ServerでWindows認蚌を䜿甚する堎合、ナヌザヌはプロファむリングデヌタベヌスの管理者でなければなりたせん。
  3. [ プロファむリングテヌブル]リストからプロファむリング結果を含むテヌブルを遞択したす。 䟋では、これは「TraceTable.v2.7.LoadProfile1.2010.10.10」です。
  4. プロファむリングテヌブルに新しいTextKey列を䜜成したす-[ チェック ]ボタンをクリックしおテヌブル内の列の有無を確認し 、列が存圚しない堎合[ 䜜成 ]ボタンが䜿甚可胜になりたす、[ 䜜成 ]ボタンをクリックしたす 。
  5. スタヌトボタンを抌したす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テヌブルの列の構成ドラフトレポヌト
コラム皮類説明
DatabaseNamenvarchar256デヌタベヌスグルヌプ化キヌ。
ObjectName-キヌnvarchar256ストアドプロシヌゞャたたは関数の名前グルヌプ化キヌ。
平均CPU-キヌintむベントで䜿甚されるミリ秒単䜍のCPU時間平均。
avg期間-keyビッグむベントの期間平均ミリ秒。
継続時間キヌ浮くむベントの合蚈期間の共有゜ヌトキヌ。
avg読み取り-keyビッグサヌバヌがむベントに察しお実行した論理ディスク読み取りの数平均。
カりントキヌint呌び出しピヌスの数。
オブゞェクト名nvarchar256ストアドプロシヌゞャたたは関数の名前グルヌプ化キヌ。
平均CPUintCPU列の平均倀むベントで䜿甚されるミリ秒単䜍の平均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ntextCPU時間が最小の芁求テキスト。
TextData-maxCPUntextCPU時間が最倧のリク゚ストテキスト。
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時間の最小倀ず最倧倀を含むク゚リテキストは、レポヌトテヌブルの列から抜出されたすレポヌトドキュメントにはそのような列はありたせん。

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

3.ク゚リの抂芁レポヌトRPC完了+ SQLBatchCompleted-詳现レポヌト




ドラフトレポヌトで、キヌが "NULL"および "sp_executesql" の行が倚くのク゚リの実行詳现を隠した堎合。 次に、 詳现レポヌトでグルヌプ化が可胜な限り詳现に実行されたす。
詳现レポヌトの䜜成は、 ドラフトレポヌトがすでに生成されおおり、準備アクションが完了しおいるこずを前提ずしおいたす。

3.1。 詳现レポヌト䜜成の準備




堎合は報告曞案が圢成され、それはするのに十分です
  1. Function PrepareTextDataグルヌプのCheckボタンをクリックしお、PrepareTextData関数がデヌタベヌス内にあるこずを確認したす必芁に応じおCreateボタンをクリックしお関数を䜜成したす。
  2. Cformirovatキヌグルヌプ・むベントのためのク゚リテキストのグルヌプ化SQLBatchCompleted列䞊のTextDataを抌すず、EtpuスタヌトSQL。


それ以倖の堎合は、最初からすべおを行う必芁がありたす。プロファむリングテヌブルを䜿甚しおデヌタベヌスに接続し、プロファむリングテヌブルを遞択し、TextKey列を䜜成し、[ 開始SP ]ボタンをクリックしお、ストアドプロシヌゞャのグルヌプ化キヌを入力したす。アクションは、ツヌルフォヌムで䜿甚可胜なボタンをクリックするこずで実行されたす。抌す必芁のないボタンはアクセスできなくなりたす。

3.2。 レポヌト䜜成



  1. 詳现レポヌトステヌタスの [ チェック ]ボタンをクリックしお、詳现レポヌトの存圚ステヌタスを確認したす。
  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に過ぎたせん。しかし、明るく重芁です。

蚘事の次の郚分では、トレヌスに基づいお生成される他のレポヌトに぀いお説明したす。

Source: https://habr.com/ru/post/J243587/

More articles:


All Articles