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のメモリ診断を実行するなどメモリモジュールの調査を検討してください。
