SQL Server / SQL Database で CPU 使用状況が高い場合に発生する「待ち事象」としては、「SOS_SCHEDULER_YIELD」が有名ではないでしょうか。
待ち事象については、sys.dm_os_wait_stats (Transact-SQL) で解説が行われており、SOS_SCHEDULER_YIELD については、次のように解説が行われています。
タスクが、他のタスクの実行にスケジューラを自主的に解放したときに発生します。 この待機中、タスクはクォンタムの更新を待機しています。
これはどのようなことを表しているのでしょうか？
本投稿では、SOS_SCHEDULER_YIELD の基本的な考え方について見ていきます。
今回は、TPC-H の Q1 をシングルスレッドで実行したケースで考えていきます。
実行プランはシンプルなものですね。
このクエリを実行している最中の CPU 使用状況はこのようになっています。
平均して 50% 程度の CPU 使用状況となっていますが、利用可能な CPU については余裕があります。 
クエリを実行している最中に、次のようなクエリで情報の取得を行います。 (Session ID は Q1 のクエリを実行している Session Id を指定します)
select
er.wait_type,
er.last_wait_type,
er.wait_time,
er.cpu_time,
ot.context_switches_count,
ow.quantum_used,
oth.usermode_time,
oth.kernel_time,
er.cpu_time * 1.0 / ot.context_switches_count AS avg_cpu_time
from
sys.dm_exec_requests AS er
inner join sys.dm_os_tasks AS ot
on ot.session_id = er.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
er.session_id = 59
実際に取得された情報がこちらです。
SQL Server 上では、Q1 のクエリしか実行されていませんが、「last_wait_type」から、クエリ実行中に「SOS_SCHEDULER_YIELD」が発生したことが確認できます。
SOS_SCHEDULER_YIELD を説明するときには「CPU 使用権の譲渡待ち」というような説明をすることがあります。
これは実際にはどのようなことを表すのでしょうか？
私は次のような動作ではと考えています。
SQL Server でクエリを実行する際に、タスクは次の状態の遷移を繰り返しながら、クエリの実行を進めていきます。
「実行状態」は CPU を使用してクエリの実行を進めている状態です。
特定のクエリの実行を常に進めていることができるかというと、そういうこともありません。
ハードウェアリソースであれば、今回の焦点となる CPU の使用率が高い状態で他のタスクで CPU を使用する必要がある場合や、データをディスクから取得する場合が発生した場合には、ハードウェアリソースのアクセスにより、瞬間的にクエリの実行が待機される可能性があります。
ハードウェアリソース的な話ではなく、論理的な待機としては「ロック」も該当し、ロック競合が発生している場合も、クエリの実行を一時的に待機する必要があります。
このような「何らかの要因により、クエリの実行に対して待機が発生している状態」が「待機状態」となります。
待機状態は、リソースの競合等が解消されますと、クエリの実行を進めることができるようになりますので、待機状態となる要因が解消されると「実行可能状態」となり、CPU の割り当てが可能になる状態を待機することになります。
今回は 1 クエリしか同時に実行されていない状態にしていますが「待機状態」になった理由として「SOS_SCHEDULER_YIELD 」が発生していることが DMV から確認できました。
これはどのようなことを表すのでしょうか？
このヒントは、スレッドおよびタスクのアーキテクチャ ガイド に記載されています。
このドキュメントには次のような記載があります。
スケジューラ (SOS スケジューラとも呼ばれます) では、タスクに代わって作業を実行するために処理時間を必要とするワーカー スレッドが管理されます。 各スケジューラは、個々のプロセッサ (CPU) にマップされます。 ワーカーがスケジューラでアクティブな状態を維持できる時間は、OS クォンタムと呼ばれ、最大 4 ミリ秒です。 クォンタム時間が経過したワーカーは、CPU リソースへのアクセスを必要とする他のワーカーに時間を明け渡し、その状態を変更します。 CPU リソースへのアクセスを最大化するためのワーカー間のこのような連携は、協調スケジューリング (または非プリエンプティブ スケジューリング) と呼ばれます。 その後、ワーカーの状態の変化は、そのワーカーに関連付けられたタスクと、タスクに関連付けられた要求に伝達されます。 ワーカーの状態の詳細については、「sys.dm_os_workers」を参照してください。 スケジューラの詳細については、「sys.dm_os_schedulers」を参照してください。
SQL Server では、タスクがアクティブで CPU を使用することができるのは最大で 4 ミリ秒となっています。
それでは、先ほどの情報を改めて見てみましょう。
avg_cpu_time は、 「cpu_time (ミリ秒)/ context_switches_count」を算出したものとなります。
コンテキストスイッチの発生状況と CPU 使用時間の相関関係を見ると、平均 4 ミリ秒でコンテキストスイッチが発生していると考えられます。
このことから、実行されているクエリが 1 つであり、 CPU に余裕がある状態でも、コンテキストスイッチが発生し、継続して CPU が使用できているのは 4 ミリ秒であると考えることができるのではないでしょうか。
CPU を 4 ミリ秒使用すると、瞬間的には待機状態となり、CPU が使用されているクエリが存在していないので、直ぐに実行状態に切り替わっていると考えられます。
それでは、このような状態のときに SOS_SCHEDULYER_YIELD はどのようになるでしょうか？
先ほど実行した情報取得のクエリを次のように変更して、Q1 の実行中の状態を取得してみます。
select
er.wait_type,
er.last_wait_type,
er.wait_time,
er.cpu_time,
ot.context_switches_count,
ow.quantum_used,
oth.usermode_time,
oth.kernel_time,
er.cpu_time * 1.0 / ot.context_switches_count AS avg_cpu_time
from
sys.dm_exec_requests AS er
inner join sys.dm_os_tasks AS ot
on ot.session_id = er.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
er.session_id = 59
select * from sys.dm_exec_session_wait_stats where session_id = 59
WAITFOR DELAY '00:00:01'
GO 2
1 秒間隔で、実行中のクエリと、実行中のクエリの待ち事象を取得したものとなります。
1 秒間に発生したコンテキストスイッチの数は「814 – 502 = 312」となっています。
SOS＿SCHEDULER＿YIELD の発生回数も同様にみると「324,349 – 324,038 = 311」となります。(1,000 ミリ秒 / 4 ミリ秒 = 250 となっているとよいのですが、1 秒の待機による順次のクエリ実行では数ミリ秒のずれは出ますので、多少大きい値になっています)
同様の値となっていますね。
このことから「コンテキストスイッチの発生により、CPU の使用状況が切り替わると、SOS_SCHEDULER_YIELD が発生している可能性がある」と考えることができるのではないでしょうか。
「SOS_SCHEDULER_YIELD の発生回数が多い」ということは 4 ミリ秒の CPU 使用時間に達したため、待機状態に推移したが CPU に余裕があったためすぐに実行状態に移ることができたということが言えるのかと。
実際に待機された時間を表す「wait_time_ms」については「1455 – 1453 = 2」となり、311 回の SOS_SCHEDULER_YIELD が発生していますが、それにより待機された時間は 2 ミリ秒ということが確認できます。
つまり CPU の使用時間は多いのですが、他のタスクにより CPU が使用されていないため、直ぐに CPU を使用できる状態であったということが言えるかと。
SOS_SCHEDULER_YIELD 待ち事象が頻繁に発生しているということは、CPU を使用している時間が長いと言えます。
ただし、単純に長いというのではなく、waitig_tasks_count と wait_time_ms の関係にも注視することで、CPU を占有することができていたのか、他に CPU を使用するクエリが同時に実行されていたのかという観点の考察につながります。
「待ち事象がどのような理由により発生したのかを考える」ということは、SQL Server のチューニングを実施する際の重要なポイントとなります。
ドキュメントに記載されている説明を単純に読むだけでなく「記載されている内容を、どのような情報を取得 / 組み合わせることで事象の説明ができるか」を考えると、一歩進んだ学習になるのではないでしょうか。