SQL Server / SQL Database で CPU 使用率が高い場合、CPU を使用しているクエリの特定を行い、CPU 負荷を低減させるという作業を行うことがあるかと思います。
SQL Server / SQL Database で CPU 使用率が高い状態とは、そもそもとしてどのような状態を指しているのでしょうか??
今回は情報を取得しやすい SQL Server で情報を取得していますが、基本的な考え方は、SQL Databse でも同様です。
また、クエリによる CPU の利用を考える際には、スケジューラーやスレッドの意識も重要となりますので、本投稿についてはそれらについても触れておきたいと思います。
CPU 使用率とは?
そもそもとして、CPU 使用率とはどのような情報を指すのでしょうか?
次の画像は、SQL Server に対して、CPU 負荷の高いクエリを実行している際のタスクスケジューラーの CPU 使用率の情報です。
各コアの CPU 使用率が 100% の状態になっていますね。
CPU 使用率ですが、Windows の各種情報では、情報の確認者が一目で把握しやすい情報として出力を行うために、「CPU 使用時間」を CPU 使用率に変換して情報の出力を行っているという認識でいます。
CPU 使用率のベースとなる情報は「CPU 使用時間」であり、SQL Server の各種情報でも、CPU は使用率ではなく、使用時間で情報が表現されています。
CPU 使用時間とは?
CPU 使用時間とはその名称の通り、CPU を使用していた時間となります。
基本的な考え方としては、「測定時間内で CPU を使用していた時間」が CPU 使用時間となるのではないでしょうか。
測定時間を 1 秒として考えた場合、1 秒の中で 500 ms (0.5) 秒、CPU を使用していた時間があるとすれば、それは 1 秒という測定間隔の中では CPU 使用率は 50% となります。
SQL Server の CPU 使用率を確認する方法として Resource Pool Stats の情報を確認するという方法があります。
(リソースガバナーの機能が使用できるエディション / SQL Database でのみ利用することができる方法となります)
select * from sys.dm_os_performance_counters where object_name = 'SQLServer:Resource Pool Stats' AND counter_name like 'CPU usage [%]%'
クエリを実行すると次のような情報を取得することができます。
「CPU usage %」については、ドキュメントに次の記載があります。
パフォーマンス オブジェクトの指定されたインスタンス内のすべての要求を待機するシステム CPU (アクティブ時間の合計に対する割合)
CPU usage % は、CPU がアクティブだった時間を示すものとなり、CPU 使用率を取得する際には、「CPU usage % base / CPU usage %」により、算出を行うことができます。
ここでポイントとなるのが、「CPU usage % base」です。
今回使用している環境では「8008」が比率を算出する際の基準値となっています。
この値ですが、「搭載されている CPU コア数× 1000」相当の値となっているはずです。
(実際には、SQL Server のスケジューラーが認識しているコア数×8 だと思いますが)
今回使用している環境は 8 コアの環境となります。そのため「8000」程度の値となっていることが確認できるかと。
「CPU usage % base」の 8,000 という値がどのような値を指しているかというと、「1 秒の測定期間の中で 8,000 ミリ秒、CPU を使用することができる」ということを示しているのかと思います。
「CPU usage %」は「1 秒の測定期間の中で何ミリ秒、CPU を使用していたか」ということになるのかと。
このような内容となっているため、この 2 つの値を活用することで、CPU の使用率を算出することができます。
CPU に負荷をかけている状態に取得した情報が下図となります。
CPU usage % base については、取得したタイミングで多少、cntr_value の値が増減することがあるため、先ほど取得した画像とはミリ秒単位のせいで多少のずれがありますが、8,000 付近になっていることは変わりないですね。
内部で管理しているの CPU 使用時間であるということを意識すると上記の情報の意味が分かってきます。
上記の情報であれば、次のようになっていることを示します。
- 1 秒間の計測時間の中で 8,016 秒、CPU を使用することができる
- 実際に 1 秒間の計測時間の中で、7,553 秒、CPU を使用していた
- 7,553 / 8,016 = 0.94 = 1 秒間の計測時間の中では、94% の CPU 使用率であった
SQL Server の情報では、CPU の使用状況を時間で表している項目はいくつかあり、sys.dm_exec_requests の cpu_time についても、CPU を利用していた時間となります。
SELECT er.session_id, er.wait_type, er.start_time, er.total_elapsed_time, er.cpu_time FROM sys.dm_exec_requests AS er INNER JOIN sys.dm_exec_sessions AS es ON es.session_id = er.session_id AND es.is_user_process = 1 AND es.session_id <> @@SPID
dm_exec_requests には、処理の経過時間として total_elapsed_time があり、この値と cpu_time を比較するというアプローチも取れます。
例としては次のような利用です。
- total_elapsed_time が cpu_time より大きい
- クエリの経過時間の中で CPU を使用できていなかった時間がある
- CPU 以外のリソースの待機 (ブロッキング) により、クエリの実行時間内で CPU を使用していない時間があった
- CPU リソースの競合により、CPU を使用できない時間があった
- total_elapsed_time より、cpu_time が大きい
- 並列クエリにより、同時に複数の CPU コアを使用していた
SQL Server は Microsoft 社の製品ですが、MS 製品だからと言って他のアプリケーションと CPU の使用方法が異なるかというと、そういうこともありません。
一般的なアプリケーションと同様に、クエリにより CPU を使用する際には、OS 上ではスレッドとして処理が行われています。
これらの処理を考える場合、SQLOS のスケジューラーの概念も重要となります。
SQL Server のスケジューラーと同時実行可能な処理数
SQL Server の CPU 使用を考量する際には「スケジューラー」という概念があります。
基本的なアーキテクチャについては スレッドおよびタスクのアーキテクチャ ガイド から確認することができます。
SQL Server は内部的に「SQLOS」(SQL Server オペレーティングシステム) という、SQL Server のリソースを管理するためのスケジューラーの機構を持っており、この SQLOS によって、SQL Server に対しての様々な処理が管理 / 実行されます。
SQL Server の要求が CPU を使用する際には、SQLOS が持っている CPU スケジューラーを介して、CPU の利用が行われる形となります。
SQL Server は Windows 上で実行されるアプリケーションと同一の扱いであり、実際に処理が実行される際には、SQL Server のプロセス内のスレッドとして実行が行われます。
SQL Server に対してのクエリを実行した際の流れですが、次のような形になります。
- SQL Server に対して接続 (sys.dm_exec_connections) が行われ、セッション (sys.dm_exec_sessions) が生成される
- SQL Server に対して、クエリ処理の要求 (sys.dm_exec_requests) が行われる
- 実行するクエリがタスク (sys.dm_os_tasks) として処理が行われる
- タスクは、SQL Server のワーカースレッド (sys.dm_os_workers) によって処理が行われる
- ワーカースレッドは OS 上ではスレッド (sys.dm_os_threads) として処理が行われる
基本的な考え方として、SQL Server に対してのクエリ実行要求は、SQL Server 観点ではタスクとして処理が行われ、タスクは OS 上では通常のアプリケーションと同様にスレッドとして処理が行われるという形になります。
SQL Server に対してのクエリの実行要求については、通常のアプリケーションと同様にスレッドとして処理が行われています。
そのため、CPU の利用についての考え方も基本的には通常のアプリケーションと同様です。
「各 CPU コアは同時に一つのスレッドを実行することができる」という原則に基づいて処理が行われることになります。
これについてはアーキテクチャガイドにも記載が行われています。
プロセッサ (CPU) が 1 つしかないコンピューターであっても、スレッドにより、複雑なアプリケーションで CPU をより効率的に使用できるようになります。 CPU が 1 基の場合、一度に実行できるスレッドは 1 つだけです。 あるスレッドが、ディスクの読み書きなど、CPU を使用しない処理を長時間にわたって実行している場合、この処理が終了するまで他のスレッドを実行できます。 処理の終了を待っている間に他のスレッドを実行できるようにすることで、アプリケーションは CPU を最大限に使用できます。 データベース サーバーなど、ディスク I/O が多発するマルチユーザー アプリケーションの場合、特にそのことが当てはまります。 複数の CPU を備えるコンピューターでは、各 CPU で 1 つのスレッドを同時に実行できます。 たとえば、CPU を 8 基搭載したコンピューターは同時に 8 個のスレッドを実行できます。
今回、使用している環境であれば、8 コアですので、同時に 8 このスレッドを実行できることになります。
これを確認するために次のようなクエリを実行してみます。
SELECT ot.session_id, ot.exec_context_id, ot.request_id, ot.scheduler_id, er.dop, ot.task_state, ow.state, ow.last_wait_type, ot.context_switches_count, ow.pending_io_count, ow.pending_io_byte_count, ow.pending_io_byte_average, oth.os_thread_id FROM sys.dm_os_tasks AS ot INNER JOIN sys.dm_exec_sessions AS es ON es.session_id = ot.session_id AND es.is_user_process = 1 INNER JOIN sys.dm_exec_requests as er ON er.session_id = ot.session_id INNER JOIN sys.dm_os_workers AS ow ON ow.worker_address = ot.worker_address INNER JOIN sys.dm_os_threads AS oth ON oth.thread_address = ow.thread_address WHERE ot.session_id IS NOT NULL AND (ot.task_state = 'RUNNING' AND ow.state = 'RUNNING') ORDER BY ot.session_id ASC, ot.exec_context_id ASC
このクエリは、「SQL Server で実行されているタスクの中で、実行中のタスクのみを抽出」するためのクエリとなります。
厳密にいうと、ワーカースレッドのレベルでも実行中であるものを抽出していますので、SQL Server が認識している処理粒度の中で最も粒度の低い単位での実行状況を確認したものとなります。
今回の環境は 8 コアの環境であり、DB では MAXDOP を 4 に設定しています。
この環境下で、CPU 負荷が高いクエリを 10 並列で実行した際のクエリの結果が以下になります。
8 コアの環境ですので、同時に実行されているタスクは 8 個となっていることが確認できます。
8 コアの環境では、ユーザーの要求を実行するためのスケジューラーは 0~7 までの 8 個のスケジューラーが生成されます。
WHERE で実行中の要求のみにフィルターしなかった場合は 52 のタスクが動作している状態となっています。
1 スケジューラーで同時に実行が可能なタスクについては 1 個となりますので、52 個のタスクが動作しているが、RUNNING の状態にできるのは、各スケジューラー 1 個のみとなるため、実行中のタスクは 8 個であるということがこの情報から確認することができます。
上記のクエリは CPU の数が少ない環境で取得したものとなりますが、Many CPU の環境で情報を取得した場合は「土のセッションで CPU コア数を多く使用しているか」というような情報を取得することにも活用できますので「セッション単位で利用している CPU コア数」という観点の情報は、いくつか活用できるバリエーションがあるのではないでしょうか。
なお、SQL Server では、一つのワーカーが CPU を使用している状態を維持できるのは最大で 4 ミリ秒となっています。
ワーカーがスケジューラでアクティブな状態を維持できる時間は、OS クォンタムと呼ばれ、最大 4 ミリ秒です。 クォンタム時間が経過したワーカーは、CPU リソースへのアクセスを必要とする他のワーカーに時間を明け渡し、その状態を変更します。
RUNNING が 4 ミリ秒続いたら、CPU リソースの利用を解放し、次に CPU を使用することができるようになるまで、SOS_SCHEDULER_YIELD により待機が発生している状態となります。
そのため、SOS_SCHEDULER_YILD が発生しているということは、該当のタスクが、4 ミリ秒以上、CPU を使用している状態になっているということになるかと思います。
コーディネータースレッドと実際の処理スレッド
MAXDOP = 1 の場合は、一つのタスクは一つのスレッドで実行が行われますが、それ以外の場合、並列クエリとして実行が可能な場合には、一つのタスクは複数のスレッドに分割されて実行が行われます。
上記の実行結果はストレスツールで複数の要求を同時に実行していた場合の情報ですが、一つの要求のみを実行していた場合は、次のような結果となります。
並列クエリとして実行している場合、スレッドを管理するためのコーディネータースレッドを親として、実際の処理が子スレッドで実行されます。
上記の画像で言うと「exec_context_id = 0」がコーディネータースレッドとなり、1~4 の exec_context_id が子スレッド (実際の処理スレッド) となります。
今回の環境は MAXDOP = 4 で実行していますので、子スレッドが 4 子生成されていることが確認できますね。
SQL Server の実行中の要求を確認する際には、sys.dm_exec_requests の内容を確認することが多いかと思います。
DMV の説明には次のような記述があります。
この DMV では、コーディネーターのスレッドのみが要求に対して表示されます。 列の 読み取り、 書き込み、 logical_reads、 row_count は、コーディネータースレッドに対して更新され ません 。 列 wait_type、 wait_time、 last_wait_type、 wait_resource、および granted_query_memory は、コーディネータースレッドに対して のみ更新 されます。 詳細については、「スレッドおよびタスクのアーキテクチャ ガイド」を参照してください。
sys.dm_exec_requests で表示されているいくつかの項目については、上記の図のコーディネータースレッドの情報となり、待機事象についてもコーディネータースレッドの情報が代表して表示されています。
並列クエリを実行している場合、コーディネータースレッドは、このスレッドの処理が完了するまで CXPACKET の待機状態となっていることが多く、sys.dm_exec_requests で情報を確認すると wait_type / last_wait_type が CXPACKET による待機となっていることが確認できるケースが大半ではないでしょうか?
CXPACKET の待機事象になっているクエリでは、「実際に処理が行われているスレッドではどのような待機事象が発生しているか?」が重要となるケースが多いかと。
並列クエリによる子スレッドの情報については、exec_requests ではなく、SQLOS のタスクレベルでブレークダウンすることで情報の取得を行うことができます。
並列クエリによって、各スレッドでどのような待機が発生しているかの確認については、sys.dm_os 系の情報を参照することで確認ができるかと。
SQL Server のインスタンスの CPU 使用率が高い場合、CPU 使用時間の高いクエリに対してチューニングを行っていますが、そもそも、CPU 使用率ってなんでしたっけということをまとめたことが無かったので、今回軽くではありますがまとめてみました。
[…] https://blog.engineer-memo.com/2021/03/07/sql-server-sql-database-%e3%81%ab%e3%81%8a%e3%81%91%e3%82%… […]
【後で読みたい!】SQL Server / SQL Database における CPU 使用率 (CPU 使用時間) とは? | Tak's Bar
23 3月 21 at 23:18