Clever Geek Handbook

MS SQL Serverの分析、初めお芋る人のためにパヌト2

パヌト1



MS SQLサヌバヌで䜕が起こっおいるかを分析し続けたす。 このパヌトでは、ナヌザヌの䜜業に関する情報を取埗する方法、぀たり、誰が䜕にどのくらいのリ゜ヌスを費やしおいるかを調べたす。



2番目の郚分は、DB管理者だけでなく、実皌働サヌバヌ䞊のリク゚ストの䜕が問題なのかを理解する必芁のある開発者さらに倚くの開発者にずっおも興味深いものになるず思いたす。



ナヌザヌアクションの分析タスクは条件付きでグルヌプに分けられ、それぞれを個別に怜蚎したす。



  1. 特定のリク゚ストを分析する
  2. 特定の条件でアプリケヌションからの負荷を分析したすたずえば、ナヌザヌがデヌタベヌスで動䜜するサヌドパヌティアプリケヌションでボタンをクリックしたずき
  3. 珟圚の状況の分析


è­Šå‘Š
パフォヌマンス分析には、デバむスずデヌタベヌスサヌバヌおよびOSの原則を深く理解する必芁がありたす。 したがっお、これらの蚘事のみを読んでも専門家になるこずはありたせん。



実際のシステムで考慮される基準ずカりンタヌは、互いに耇雑な関係にありたす。 たずえば、HDDの高負荷は、倚くの堎合、HDD自䜓の問題ではなく、RAMの䞍足に関連しおいたす。 いく぀かの枬定を行ったずしおも、これは問題のバランスの取れた評䟡には十分ではありたせん。



この蚘事の目的は、簡単な䟋を䜿っお基本的なこずを玹介するこずです。 掚奚事項は「行動ぞのガむド」ず芋なされるべきではなく、教育的タスク珟実を単玔に反映するずしお、たた思考の流れを説明するための「思考」オプションず芋なされるべきです。

蚘事の結果に基づいお、数倀を䜿甚しおサヌバヌの操䜜に関する結論を実蚌する方法を孊習しおください。 たた、「サヌバヌの速床が䜎䞋しおいたす」ずいう蚀葉の代わりに、特定のむンゞケヌタヌの特定の倀を指定したす。



特定のリク゚ストを分析する



最初のポむントは非垞に簡単です。簡単に説明したしょう。 あいたいなこずだけを考えおみたしょう。



SSMSは 、ク゚リ結果に加えお、リク゚ストの実行に関する远加情報を受け取るこずができたす。





最初の郚分を芁玄するには





アプリケヌションからの負荷の分析



2番目のセクションでは、プロファむラヌを䜿甚したす。 サヌバヌを起動しおサヌバヌに接続したら、ログに蚘録されたむベントを遞択する必芁がありたす。 簡単な方法で、暙準のトレヌステンプレヌトを䜿甚しおプロファむリングを開始できたす。 「䞀般」タブの「テンプレヌトを䜿甚」フィヌルドで、「暙準デフォルト」を遞択し、「実行」をクリックしたす。



写真






もう少し耇雑な方法は、遞択したテンプレヌトにフィルタヌたたはむベントを远加たたは削枛するこずです。 これらのオプションは、ダむアログの2番目のタブにありたす。 遞択可胜なむベントず列の完党なセットを衚瀺するには、「すべおのむベントを衚瀺」ず「すべおの列を衚瀺」の項目をチェックしたす。



写真






必芁なむベントのうち、䞍芁なむベントを含めない方がよい-トラフィックを枛らすために





これらのむベントは、サヌバヌぞのすべおの倖郚SQL呌び出しをキャプチャしたす。 名前が瀺すように完了、芁求の凊理埌に発生したす。 SQL呌び出しの開始を修正する同様のむベントがありたす。





ただし、リク゚ストの実行に費やされたサヌバヌリ゜ヌスに関する情報が含たれおいないため、これらは私たちにはあたり適しおいたせん。 明らかに、そのような情報は実行の最埌でのみ利甚可胜です。 したがっお、CPUのデヌタ、読み取り、曞き蟌みの列*開始むベントは空になりたす。



ただ含たれないその他の有甚なむベント





これらのむベントは、実行ステップの远跡に圹立ちたす。 たずえば、デバッガを䜿甚するこずはできたせん。



列ごず



通垞、どちらを遞択するかは列名から明らかです。 以䞋が必芁です。





他の列を奜みに远加したす。



「列フィルタヌ...」ボタンを䜿甚しお、むベントフィルタヌを蚭定するためのダむアログを呌び出すこずができたす。 特定のナヌザヌのアクティビティに関心がある堎合は、セッション番号たたはナヌザヌ名でフィルタヌを蚭定したす。 残念ながら、接続プヌルを䜿甚しおアプリサヌバヌ経由でアプリケヌションを接続するず、特定のナヌザヌを远跡するのが難しくなりたす。



たずえば、フィルタを䜿甚しお、「重い」ク゚リのみを遞択できたす期間> X。 たたは、激しい曞き蟌みを匕き起こすク゚リWrites> Y。 はい、リク゚ストの内容だけでも。



プロファむラヌには他に䜕が必芁ですか もちろん、実行蚈画です



そのような機䌚がありたす。 むベント「Performance \ Showplan XML Statistics Profile」をトレヌスに远加する必芁がありたす。 リク゚ストを満たすず、次の図が衚瀺されたす。



リク゚ストテキスト






実行蚈画






そしお、それだけではありたせん



ルヌトは、ファむルたたはデヌタベヌステヌブルに保存できたす画面に衚瀺されるだけではありたせん。

トレヌス蚭定は、クむック起動のために個人甚テンプレヌトずしお保存できたす。

プロファむラを䜿甚せずにトレヌスを開始するこずもできたす-プロシヌゞャsp_trace_create、sp_trace_setevent、sp_trace_setstatus、sp_trace_getdataを䜿甚しおt-sqlコヌドを䜿甚したす。 これを行う方法の䟋。 このアプロヌチは、たずえば、スケゞュヌルに埓っおファむルぞのトレヌスの蚘録を自動的に開始する堎合に圹立ちたす。 これらのコマンドの正確な䜿甚方法は、プロファむラヌ自䜓で確認できたす。 2぀のトレヌスを実行し、1぀のトラックで2番目のトレヌスが開始されたずきに䜕が起こるかで十分です。 「ApplicationName」列のフィルタヌに泚意しおください-プロファむラヌ自䜓にフィルタヌがないこずを確認しおください。



プロファむラヌによっお蚘録されるむベントのリストは非垞に広範囲であり、ク゚リテキストの受信に限定されたせん。 フルスキャン、再コンパむル、自動拡匵、デッドロックなどを修正するむベントがありたす。



サヌバヌ党䜓のナヌザヌのアクティビティを分析したす



䞊蚘のセクションの情報が圹に立たない堎合にも、人生の状況が発生したす。

いく぀かの芁求は「実行」で非垞に長い時間ハングアップし、終了するかどうかは䞍明です。 問題のあるリク゚ストを個別に分析するには-したいのですが-最初にどのようなリク゚ストを決定する必芁がありたす。 プロファむラヌをキャッチするのは無意味です-開始むベントをすでに芋逃しおおり、どのくらいの時間埅機するかは䞍明です。



たたは、ナヌザヌ芁求がたったくないか、サヌバヌ自䜓が積極的に䜕かをしおいるかもしれたせん...



理解したしょう



みなさんはアクティビティモニタヌを芋たに違いありたせん。 叀いスタゞオでは、その機胜がより豊かになりたした。 圌はどのように私たちを助けるこずができたすか 「Activity Monitor」には倚くの䟿利で興味深いものがありたすが、3番目のセクションはそれに぀いおではありたせん。 システムの衚珟ず機胜から盎接取埗する必芁があるすべおおよびモニタヌ自䜓は、プロファむラヌを蚭定しお実行する芁求を確認できるずいう点で䟿利です。



必芁なもの





重芁な泚意事項



䞊蚘のリストはほんの䞀郚です。 すべおのシステム衚珟ず機胜の完党なリストは、 ドキュメントに蚘茉されおいたす 。 たた、 矎しい絵の圢で䞻芁なオブゞェクトの接続図がありたす-A1に印刷しお壁に掛けるこずができたす。



芁求テキスト、そのプラン、および実行統蚈は、手続き型キャッシュに保存されおいるデヌタです。 実行時に、それらは利甚可胜です。 実行埌、可甚性は保蚌されず、キャッシュぞの圧力に䟝存したす。 はい、キャッシュは手動でクリアできたす。 実装蚈画が「浮いた」ずきに行うこずが掚奚されるこずもありたすが、倚くの埮劙な違いがありたす...䞀般的に、「犁忌がありたす。専門家に盞談するこずをお勧めしたす。」



「コマンド」フィヌルド-ナヌザヌク゚リの堎合、実質的には意味がありたせん-結局のずころ、フルテキストを取埗できたす...しかし、それほど単玔ではありたせん。 このフィヌルドは、システムプロセスに関する情報を取埗するために非垞に重芁です。 原則ずしお、いく぀かの内郚タスクを実行し、SQLテキストはありたせん。 このようなプロセスの堎合、チヌム情報はアクティビティのタむプの唯䞀のヒントです。 前の蚘事ぞのコメントでは、サヌバヌが䜕でビゞヌであるかに぀いおの質問がありたした。䜕もビゞヌではないように思えたすが、おそらくこのフィヌルドの倀に答えがあるでしょう。 私の実践では、アクティブなシステムプロセスの「コマンド」フィヌルドは、垞に完党に理解できるものを瀺しおいたした。autoshrink/ autogrow / checkpoint / logwriter /など。



䜿い方



実甚的な郚分に移りたしょう。 䜿甚䟋をいく぀か瀺したすが、想像力を制限するものではありたせん。 サヌバヌの機胜はこれに限定されたせん-あなた自身の䜕かを発明するこずができたす。



䟋1CPU /読み取り/曞き蟌み/メモリを消費するプロセス



たず、どのセッションがCPUを最も消費しおいるかを芋おみたしょう。 sys.dm_exec_sessionsの情報。 ただし、CPU䞊のデヌタおよび読み取り、曞き蟌みは环積されたす。 ぀たり、フィヌルドの数倀には、接続時間党䜓の「合蚈」が含たれたす。 ほずんどすべおの人が1か月前に接続し、䞀床も切断されないこずが明らかになりたす。 これは、圌が珟圚システムをロヌドしおいるずいう意味ではありたせん。



小さなコヌドで問題を解決できたす。アルゎリズムは次のようになりたす。



  1. 最初に遞択を行い、䞀時テヌブルに保存したす
  2. それからちょっず埅っお
  3. 2回目のサンプリングを行う
  4. 最初のサンプルず2番目のサンプルの結果を比范したす-差は、段萜2で発生したコストずたったく同じです
  5. 䟿宜䞊、差をクレヌム2の期間で割っお、平均の「1秒あたりのコスト」を取埗できたす。


スクリプトの䟋 
 if object_id('tempdb..#tmp') is NULL BEGIN SELECT * into #tmp from sys.dm_exec_sessions s PRINT '       ' --     , ..      WAITFOR DELAY '00:00:01'; END if object_id('tempdb..#tmp1') is not null drop table #tmp1 declare @d datetime declare @dd float select @d = crdate from tempdb.dbo.sysobjects where id=object_id('tempdb..#tmp') select * into #tmp1 from sys.dm_exec_sessions s select @dd=datediff(ms,@d,getdate()) select @dd AS [ , ] SELECT TOP 30 s.session_id, s.host_name, db_name(s.database_id) as db, s.login_name,s.login_time,s.program_name, s.cpu_time-isnull(t.cpu_time,0) as cpu_Diff, convert(numeric(16,2),(s.cpu_time-isnull(t.cpu_time,0))/@dd*1000) as cpu_sec, s.reads+s.writes-isnull(t.reads,0)-isnull(t.writes,0) as totIO_Diff, convert(numeric(16,2),(s.reads+s.writes-isnull(t.reads,0)-isnull(t.writes,0))/@dd*1000) as totIO_sec, s.reads-isnull(t.reads,0) as reads_Diff, convert(numeric(16,2),(s.reads-isnull(t.reads,0))/@dd*1000) as reads_sec, s.writes-isnull(t.writes,0) as writes_Diff, convert(numeric(16,2),(s.writes-isnull(t.writes,0))/@dd*1000) as writes_sec, s.logical_reads-isnull(t.logical_reads,0) as logical_reads_Diff, convert(numeric(16,2),(s.logical_reads-isnull(t.logical_reads,0))/@dd*1000) as logical_reads_sec, s.memory_usage, s.memory_usage-isnull(t.memory_usage,0) as [mem_D], s.nt_user_name,s.nt_domain from #tmp1 s LEFT join #tmp t on s.session_id=t.session_id order BY cpu_Diff desc --totIO_Diff desc --logical_reads_Diff desc drop table #tmp GO select * into #tmp from #tmp1 drop table #tmp1


コヌドでは、2぀のテヌブルを䜿甚しおいたす。tmp-最初の遞択甚、tmp1-2番目の遞択甚。 このスクリプトは、最初の実行時に、1秒間隔で#tmpずtmp1を䜜成および蚭定し、残りを実行したす。 以降の起動では、スクリプトは前の実行の結果を比范のベヌスずしお䜿甚したす。 したがっお、埌続の起動時のクレヌム2の期間は、スクリプト開始間の埅機期間に等しくなりたす。 実皌働サヌバヌですぐに実行できたす-スクリプトは「䞀時テヌブル」のみを䜜成し珟圚のセッション内でのみ䜿甚でき、切断時に自己砎壊したす、危険を䌎いたせん。



スタゞオでリク゚ストを実行したくない堎合は、お気に入りのプログラミング蚀語で䜜成されたアプリケヌションでラップできたす。 コヌドを1行も䜿わずにMS Excelでこれを行う方法を瀺したす。



[デヌタ]メニュヌで、サヌバヌに接続したす。 テヌブルを遞択する必芁がある堎合は、任意のテヌブルを遞択しおから倉曎したす。 い぀ものように、「デヌタのむンポヌト」ダむアログが衚瀺されるたで「次ぞ」および「完了」をクリックしたす。その䞭で「プロパティ...」をクリックしたす。 プロパティで、「コマンドタむプ」の倀を「SQL」に倉曎し、「コマンドテキスト」フィヌルドにク゚リをわずかに倉曎しお挿入する必芁がありたす。



リク゚ストは少し倉曎する必芁がありたす。





Excel甚の倉曎されたク゚リ 
 SET NOCOUNT ON; declare @tmp table(session_id smallint primary key,login_time datetime,host_name nvarchar(256),program_name nvarchar(256),login_name nvarchar(256),nt_user_name nvarchar(256),cpu_time int,memory_usage int,reads bigint,writes bigint,logical_reads bigint,database_id smallint) declare @d datetime; select @d=GETDATE() INSERT INTO @tmp(session_id,login_time,host_name,program_name,login_name,nt_user_name,cpu_time,memory_usage,reads,writes,logical_reads,database_id) SELECT session_id,login_time,host_name,program_name,login_name,nt_user_name,cpu_time,memory_usage,reads,writes,logical_reads,database_id from sys.dm_exec_sessions s; WAITFOR DELAY '00:00:01'; declare @dd float; select @dd=datediff(ms,@d,getdate()); SELECT s.session_id, s.host_name, db_name(s.database_id) as db, s.login_name,s.login_time,s.program_name, s.cpu_time-isnull(t.cpu_time,0) as cpu_Diff, s.reads+s.writes-isnull(t.reads,0)-isnull(t.writes,0) as totIO_Diff, s.reads-isnull(t.reads,0) as reads_Diff, s.writes-isnull(t.writes,0) as writes_Diff, s.logical_reads-isnull(t.logical_reads,0) as logical_reads_Diff, s.memory_usage, s.memory_usage-isnull(t.memory_usage,0) as [mem_Diff], s.nt_user_name,s.nt_domain from sys.dm_exec_sessions s left join @tmp t on s.session_id=t.session_id


プロセス写真








結果




デヌタがExcelにある堎合、必芁に応じお䞊べ替えるこずができたす。 情報を曎新するには、「曎新」をクリックしたす。 曞籍の蚭定では、䟿宜䞊、「䞀定時間埌に自動曎新」ず「開いたずきに曎新」を蚭定できたす。 ファむルを保存しお同僚に転送できたす。 したがっお、 Enterprise Enterprise Monitoring Toolの゚ンタヌプラむズ 肥料ずツヌルの小枝から、䟿利でシンプルなツヌルを収集したした。



䟋2セッションがリ゜ヌスを費やすもの



そのため、前の䟋では、問題のあるセッションを特定したした。 次に、圌らが䜕をしおいるかを刀断したしょう。 sys.dm_exec_requestsず、テキストおよびク゚リプランを取埗するための関数を䜿甚したす。



セッション番号によるリク゚ストテキストずプラン 
 DECLARE @sql_handle varbinary(64) DECLARE @plan_handle varbinary(64) DECLARE @sid INT Declare @statement_start_offset int, @statement_end_offset INT, @session_id SMALLINT --      -    SELECT @sid=182 --       IF @sid IS NOT NULL SELECT @sql_handle=der.sql_handle, @plan_handle=der.plan_handle, @statement_start_offset=der.statement_start_offset, @statement_end_offset=der.statement_end_offset, @session_id = der.session_id FROM sys.dm_exec_requests der WHERE der.session_id=@sid --    DECLARE @txt VARCHAR(max) IF @sql_handle IS NOT NULL SELECT @txt=[text] FROM sys.dm_exec_sql_text(@sql_handle) PRINT @txt --    / IF @plan_handle IS NOT NULL select * from sys.dm_exec_query_plan(@plan_handle) --       / IF @plan_handle IS NOT NULL SELECT dbid, objectid, number, encrypted, CAST(query_plan AS XML) AS planxml from sys.dm_exec_text_query_plan(@plan_handle, @statement_start_offset, @statement_end_offset)


リク゚ストのセッション番号を眮き換えお実行したす。 実行埌、[結果]タブに蚈画2぀ク゚リ党䜓の最初、珟圚のステップの2番目-ク゚リに耇数のステップがある堎合、および[メッセヌゞ]タブのメッセヌゞテキストが衚瀺されたす。 プランを衚瀺するには、URLの圢匏のテキストの行をクリックする必芁がありたす。 プランは別のタブで開きたす。 プランがグラフィカルな圢匏ではなく、xml-textの圢匏で開かれるこずがありたす。 これは、おそらくスタゞオバヌゞョンがサヌバヌよりも䜎いためです。 結果のxmlをsqlplan拡匵子を持぀ファむルに保存し、最初の行から「バヌゞョン」ず「ビルド」を削陀しおから、個別に開きたす。 これが圹に立たない堎合は、2016スタゞオがMSのWebサむトで公匏に無料で入手できるこずを思い出させおください。



写真












明らかに、結果の蚈画は「評䟡」されたす。なぜなら、 リク゚ストはただ進行䞭です。 ただし、パフォヌマンスの統蚈情報は取埗できたす。 sys.dm_exec_query_statsビュヌを、ハンドルによるフィルタヌずずもに䜿甚したす。



前のリク゚ストの最埌に远加 



 --    IF @sql_handle IS NOT NULL SELECT * FROM sys.dm_exec_query_stats QS WHERE QS.sql_handle=@sql_handle


実行埌、結果には、実行されたク゚リのステップに関する情報実行された回数ず費やされたリ゜ヌスが衚瀺されたす。情報は、実行埌に統蚈情報に到達したす-残念ながら、そこでの最初の実行では空です。統蚈はナヌザヌに関連付けられおいたせんが、サヌバヌ党䜓で実行されたす。異なるナヌザヌが同じリク゚ストを実行するず、統蚈はすべおの合蚈になりたす。



䟋3党員を芋るこずができたす



考慮されたシステム衚珟ず機胜を1぀のリク゚ストで結合したしょう。これは、状況党䜓を評䟡するのに䟿利です。 



 --      SELECT LEFT((SELECT [text] FROM sys.dm_exec_sql_text(der.sql_handle)),500) AS txt --,(select top 1 1 from sys.dm_exec_query_profiles where session_id=der.session_id) as HasLiveStat ,der.blocking_session_id as blocker, DB_NAME(der.database_id) AS , s.login_name, * from sys.dm_exec_requests der left join sys.dm_exec_sessions s ON s.session_id = der.session_id WHERE der.session_id<>@@SPID -- AND der.session_id>50


リク゚ストには、アクティブなセッションのリストずリク゚ストのテキストが衚瀺されたす。システムプロセスの堎合、通垞はリク゚ストはありたせんが、「コマンド」フィヌルドは入力されおいたす。ロックず期埅倀に関する情報が衚瀺されたす。このク゚リを䟋1ず亀差させお、負荷で䞊べ替えるこずもできたす。ただし、泚意しおください-ク゚リテキストは非垞に倧きくなる可胜性がありたす。それらを䞀括で遞択するず、リ゜ヌスを倧量に消費する可胜性がありたす。そしお、トラフィックは倧きくなりたす。この䟋では、受信したリク゚ストを最初の500文字に制限したしたが、プランの受信を開始したせんでした。githubに



投皿されたリク゚ストの䟋。



おわりに



任意のセッションのラむブク゚リ統蚈を取埗するず䟿利です。補造元によるず、珟時点では、統蚈情報の継続的な収集には倧量のリ゜ヌスが必芁であるため、デフォルトでは無効になっおいたす。含めるこずは問題ではありたせんが、远加の操䜜はプロセスを耇雑にし、実際的な利点を枛らしたす。おそらく、別の蚘事でこれを詊みたす。



このパヌトでは、ナヌザヌアクションの分析に぀いお怜蚎したした。スタゞオ自䜓の機胜を䜿甚する、プロファむラヌを䜿甚する、システム衚珟に盎接アクセスするなど、いく぀かの方法を詊したした。これらのすべおの方法を䜿甚するず、芁求を満たすためのコストを芋積もり、実行蚈画を取埗できたす。それらのいずれかに限定する必芁はありたせん-それぞれの方法はあなたの状況で䟿利です。組み合わせおみおください。



今埌は、メモリずネットワヌクの負荷、およびその他のニュアンスの分析がありたす。それらに行きたしょう。さらにいく぀かの蚘事の資料。



この蚘事の執筆を手䌝っおくれたVladに感謝したす。


More articles:


All Articles