EnterpriseZine(エンタープライズジン)

EnterpriseZine(エンタープライズジン)

テーマ別に探す

第4回 sp_server_diagnosticsの情報からみる SQL Server のパフォーマンス

edited by DB Online   2012/10/12 00:00

 こんにちは。Premier Field Engineering 部の坂輪です。AlwaysOn フェールオーバー クラスター(以降 FCI)のアクティブ ノードや可用性グループのプライマリ レプリカの正常性チェックに使用される sp_server_diagnosticsは、正常性を判断するための5つのコンポーネント(system,resource,query processing,io subsystem,event)ごとに様々な情報を収集しています。これらの情報にはSQL Serverやシステムのパフォーマンスに関する情報も多く含まれています。今回はsp_server_diagnostics によって収集される情報(診断ログに出力される)から、SQL Serverのパフォーマンスについて見ていきたいと思います。 尚、sp_server_diagnostics の基本的な動作については前回の連載「あたらしいSQL Server/Denaliの世界」の「AlwaysOnフェールオーバークラスターインスタンス1」をぜひ見ていただければと思います。

SQL Serverの負荷

  systemコンポーネントの情報にはスピンロック(内部の短期的な同期にしようされます)、スケジューラーの状態やCPU 使用率などの情報が収取されます。次にsystemコンポーネントに出力される情報からdata列を抽出したものを記載しています。

 systemコンポーネントの情報のサンプル

<system
 spinlockBackoffs="0"
 sickSpinlockType="none"
 sickSpinlockTypeAfterAv="none"
 latchWarnings="0"
 isAccessViolationOccurred="0"
 writeAccessViolationCount="0"
 totalDumpRequests="0"
 intervalDumpRequests="0"
 nonYieldingTasksReported="0"
 pageFaults="2986"
 systemCpuUtilization="2"
 sqlCpuUtilization="0"
 BadPagesDetected="0"
 BadPagesFixed="0"
 LastBadPageAddress="0x0"
/>

 

 

 運用しているシステムでは、CPU使用率などのパフォーマンス カウンタなどでパフォーマンスの情報を採取しているシステムは多いと思います。それとともに個々のアプリケーションのCPU使用率を採取していますでしょうか。SQL Serverが稼働するシステムのCPU使用率が高い場合、SQL Serverによって消費されている場合がほとんどかと思います。しかし、Integration Servicesのパッケージを実行していたり、Analysis Services や Reporting Servicesが同じシステム上で動作していたり、サード ベンダーのアプリケーションが動作していたりと、必ずしもSQL Serverでない可能性もあります。それらを明確に切り分けるには、いままではSQL Server プロセスのパフォーマンス カウンタ % Process Timeを確認しますが、systemコンポーネントの情報の中にはSQL ServerプロセスのCPU使用率が含まれています。sqlCpuUtilizationの値がこれにあたります。また、システムのCPU使用率はsystemCpuUtilizationに出力されていて、sqlCpuUtilizationはsystemCpuUtilizationに対する割合となります。そのため、パフォーマンス カウンタが無い場合でもSQL ServerがCPUを消費しているのかを確認することができます。

 診断ログに出力されているCPU使用率を確認するには SQL Server Management Studio(以下SSMS) を使用するか、拡張イベントの非同期ファイル ターゲットの読み込み関数を使用します。診断ログにはsp_server_diagnosticsの収集した情報が定期的に出力されています(既定ではFCIは20秒、可用性グループは10秒間隔)。次のクエリは診断ログから時系列でシステムとSQL ServerのCPU使用率を抽出するものになります。

SELECT
x.value('(//event/data[@name=''creation_time'']/value)[1]','datetime') AS [creation_time],
x.value('(//event/data[@name=''data'']/value/system/@systemCpuUtilization)[1]','int') AS [systemCpuUtilization],
x.value('(//event/data[@name=''data'']/value/system/@sqlCpuUtilization)[1]','int') AS [sqlCpuUtilization],
x
FROM
(SELECT CAST(event_data as xml) FROM fn_xe_file_target_read_file('C:\temp\REPLICAA_MSSQLSERVER_SQLDIAG_0_129908253103510000.xel',null,null,null)) R(x)
WHERE x.value('(//event/@name)[1]','varchar(30)') = 'component_health_result'
AND x.value('(//event/data[@name=''component'']/value)[1]','sysname') = 'system'
ORDER BY creation_time

 継続的にSQL ServerのCPU使用率が高い状態が続く場合、拡張イベントやSQLトレースを使用してCPUを消費しているクエリを特定し、チューニングを行います。また動的管理ビューsys.dm_exec_query_statsは、キャッシュされているクエリプランごとのパフォーマンスに関する統計情報を確認できます。

 sys.dm_exec_query_stats のtotal_worker_timeなどからCPU消費の多いクエリを特定し、チューニングの要否を検討します。

 次にSQL Serverのスケジューリング関する項目についてご説明します。前回の古賀の記事「SQL Serverのスレッドスケジューリング」で紹介がありましたように、SQL Serverはユーザーのクエリなどのほとんどの処理をノンプリエンプティに処理しています。ユーザーのクエリは、タスクとしてワーカースレッドによって実行されます。タスクはCPUの使用権を放棄する場合、yield関数を呼び出します。もし、タスクがyield関数を呼び出さない場合はどうなるでしょうか。もうお分かりと思いますが、そのスケジューラーで次のタスクが実行されることはありません。そのため、SQL Serverはスケジューラーモニターでスケジューラーを監視しています。NonYieldingTasksReportedは、スケジューラーモニターによってyieldが行われていないスケジューラーを検出した回数を示します。Yield関数が呼び出されない原因は様々ですが、高負荷時に一時的に検出される場合もあります。

 少し話が変わりますがSQL ServerはWindowsによってメモリの問題が検出された際に通知を受け取ります。検出されたメモリがデータベースキャッシュに使用されている場合、そのページ数がBadPagesDetectedに出力されます。データベースキャッシュが変更されていない場合はそのメモリを解放し、データベースページの情報がBadPagesFixedに出力されます。BadPagesDetected が確認できる場合、Windowsのメモリ診断を実行するなどメモリモジュールの調査を検討してください。

※この続きは、会員の方のみお読みいただけます(登録無料)。


※この続きは、会員の方のみお読みいただけます(登録無料)。


著者プロフィール

  • 坂輪貴行(サカワ タカユキ)

      日本マイクロソフトの Premier Field Engineering 部にて、SQL Server ユーザーの支援を行う。前職はシステム エンジニアであり、長く Sybase を使用したプロジェクトに従事。業界歴 14 年の月一ゴルファー。

バックナンバー

連載:SQL Server 2012 Deep Dive

もっと読む

All contents copyright © 2007-2021 Shoeisha Co., Ltd. All rights reserved. ver.1.5