SHOEISHA iD

※旧SEメンバーシップ会員の方は、同じ登録情報(メールアドレス&パスワード)でログインいただけます

EnterpriseZine(エンタープライズジン)編集部では、情報システム担当、セキュリティ担当の方々向けに、EnterpriseZine Day、Security Online Day、DataTechという、3つのイベントを開催しております。それぞれ編集部独自の切り口で、業界トレンドや最新事例を網羅。最新の動向を知ることができる場として、好評を得ています。

最新イベントはこちら!

Enterprise IT Women's Forum

2025年1月31日(金)17:00~20:30 ホテル雅叙園東京にて開催

Security Online Day 2025 春の陣(開催予定)

2025年3月18日(火)オンライン開催

EnterpriseZine(エンタープライズジン)編集部では、情報システム担当、セキュリティ担当の方々向けの講座「EnterpriseZine Academy」や、すべてのITパーソンに向けた「新エバンジェリスト養成講座」などの講座を企画しています。EnterpriseZine編集部ならではの切り口・企画・講師セレクトで、明日を担うIT人材の育成をミッションに展開しております。

お申し込み受付中!

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

EnterpriseZine編集部が最旬ITトピックの深層に迫る。ここでしか読めない、エンタープライズITの最新トピックをお届けします。

『EnterpriseZine Press』

2024年秋号(EnterpriseZine Press 2024 Autumn)特集「生成AI時代に考える“真のDX人材育成”──『スキル策定』『実践』2つの観点で紐解く」

SQL Server 2012 Deep Dive

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

第4回

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

次のページ
メモリ不足によるSQL Serverの遅延

この記事は参考になりましたか?

  • Facebook
  • X
  • Pocket
  • note
SQL Server 2012 Deep Dive連載記事一覧

もっと読む

この記事の著者

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

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

※プロフィールは、執筆時点、または直近の記事の寄稿時点での内容です

この記事は参考になりましたか?

この記事をシェア

EnterpriseZine(エンタープライズジン)
https://enterprisezine.jp/article/detail/4248 2012/10/12 00:00

Job Board

AD

おすすめ

アクセスランキング

アクセスランキング

イベント

EnterpriseZine(エンタープライズジン)編集部では、情報システム担当、セキュリティ担当の方々向けに、EnterpriseZine Day、Security Online Day、DataTechという、3つのイベントを開催しております。それぞれ編集部独自の切り口で、業界トレンドや最新事例を網羅。最新の動向を知ることができる場として、好評を得ています。

新規会員登録無料のご案内

  • ・全ての過去記事が閲覧できます
  • ・会員限定メルマガを受信できます

メールバックナンバー

アクセスランキング

アクセスランキング