わかりやすいSQL Serverの内部構造 CPUリソース編
SQL ServerがCPUリソースを効率的に使用するためにどのような動作をしているか、割り振りをどのようにしているか記載しています。
マルチスレッドプログラミング
ある処理を単一のスレッドのみでシリアルに処理するプログラミング手法をシングルスレッドプログラミング。これに対して同一アドレス空間のメモリ共有しながら、ある処理を並列で実行する手法をマルチスレッドプログラミングを呼びます。複数のCPUを搭載しているコンピューターであればマルチスレッドが恩恵を受けやすくなります。
もともと「SQL Server」はサイベース社の製品として開発され、データベース製品を保有していなかったマイクロソフト社が独占使用権を獲得したところから協業が始まりました。（1990年代初頭）、UNIXオペレーションシステムにはスレッドで利用できないものも数多く存在していました。そのため、UNIX上で「sybase SQL Server」は純粋な意味でのマルチスレッドをで動作することができませんでした。しかし、WindowsNTではマルチスレッドのスケジュール管理機能が実装されていました。「MicrosoftSQL Server」はその機能を利用しマルチスレッドで動作するように設計されていました。
コンテキストスィッチとは
WindowsServer系のOSは、開発当初からマルチタスクで動作するようにデザインされています。マルチタスクとはOSが複数のタスク/プロセス/スレッドを切り替えながら、それぞれの処理を進行させていく技術です。何らかの原因であるプロセスが停止した場合は、別プロセスを実行できるため、コンピュータのスループットがは向上します。では、まったく待ち状態にならずに、自らが終了するまでCPUを使用し続けるプロセスが発生した場合はどのようになるのでしょうか？。その場合に備えて一般的なマルチタスクOSには、あるプロセスがCPUを一定期間使用すると、強制的に別のプロセスにCPUの使用権を譲る仕組みが実装されています。CPU使用時間の計測にはハードウェアタイマーが使用されます。また、プロセスに対して1回に割り当てられるCPUの使用可能な時間の単位を「タイムスライス」と呼びます。
このようにマルチタスクOSの配下で、CPU上で実行されている処理が待機状態になるか、タイムスライスのしきい値に達することによってCPUの使用権を譲り、別の処理がCPUで実行されることを「コンテキストスィッチ」と呼びます。コンテキストスィッチが発生すると、それまでのCPUを使用していた処理の情報を一旦、レジスタ（CPU内に複数個存在し演算の実行、あるいは実行状態の保存などのに使用される媒体）などに保存する必要があります。そのようなオーバーヘッドがあるため、コンテキストスィッチは負荷のかかる動作となります。Windowsでは、これらの一連のCPUリソース管理を担当しているコンポーネントが「Windowsスケジュール」です。
WindowsスケジューラとSQLServer
SQLServerはマルチスレッドで動作するアプリケーションです。SQL Serverが起動されると１つのインスタンスごとに１つのプロセスがWindows上に生成されます。生成されたSQL Serverプロセスは、様々な作業を行うために内部的に実に多くのスレッドを生成します。
インスタンスとはSQL Serverサービス自体を表します。１つのコンピュータに複数のSQL Serverインスタンスをインストールすることができ、それぞれ独自のデータベースを管理することができます。
SQL Serverプロセスによって内部的に作成されたすべてのスレッドは、WindowsスケジューラによってCPUリソースの使用状況が管理されます。それはSQL Server以外のプロセス、例えばあなたのプログラムが生成したプロセスによって作成されたスレッドと同じような扱いをうけることを意味します。CPUを使用していたSQL Serverプロセスのあるスレッドが、タイムスライスを超えた場合には、別スレッドに使用権を譲ります。また、CPUを使用していたSQL Serverプロセスのスレッドが何らかの待機状態にはいった場合も同様に使用権を譲ります。
タイムスライスの場合は、使用可能な時間を区切ってCPUリソースを効率よく各スレッド間で共有するルールはSQL Serverプロセス内のスレッドに悪影響を与えません。
コンテキストスィッチの場合は、SQL Serverプロセス内のスレッドがWindowsスケジューラの判断可能な待機状態（I/O待ち）になった結果、コンテキストスィッチが発生する場合は問題ありません。問題が発生するのはSQL Serverプロセス内のスレッドがWindowsスケジューラには判断できない待ち状態になっているケースです。
Windowsスケジューラに認識できない待ち状態
SQL Serverやその他のDBMSでは、格納したデータを保護するためにロック機能を持ってます。あるクエリがデータを更新するためには、まず目的となるデータに排他的にアクセスするためのロックを獲得する必要があります。すでに他のクエリが同じデータにロックを獲得している場合には、それが解放されるまで待つ必要があります。
クエリが実行される際に、スレッドが作成されて必要な処理が行われます。そのため、クエリがロックの解放をまっているということは、同様にスレッドがロックの解放待ちであることを意味します。一方、Windowsスケジューラに、ロックの解放待ちにあるスレッドの状態を判断できるでしょうか。ロックの概念はあくまでSQL Server内に限定されたものです。そのため、Windowsスケジューラからはロック獲得待ちのスレッドもロック獲得済みのスレッドも同じ状態であると判断されます。よって、Windowsスケジューラにすべてのスレッド管理を任せるとロック獲得待ちのスレッドが、ロック獲得済みのスレッドより先にCPUリソースの使用権を割り当てられてしまうことがあります。ロック獲得待ちのスレッドがCPUリソースを割り当てされても、何も処理することができません。その結果としてCPUリソースが浪費されます。
Windowsスケジューラが判断できない待ち状態の代表的な例として、複数のCPU及びコアが搭載されているコンピュータでは、SQL Serverは並列処理を行う場合があります。並列の処理のメリットは１つのクエリが複数スレッドに分割されて実行されることによってパフォーマンスが向上します。並列処理が行われる場合は、親スレッドが処理に必要な数の子スレッドを起動します。それぞれの子スレッドには、クエリの結果セットの一部を取得するための作業が割り当てられます。すべての子スレッドの処理が完了した段階で、親スレッドはそれぞれの結果セットをマージしてクライアントへ返します。
ほとんどの場合、それぞれの子スレッドが処理に必要とする時間はまったく同じではありません。そのため親スレッドは最も早く処理が完了する子スレッドと、最も処理が遅い子スレッドの処理を完了するまで待つ必要があります。このような待機時間中のスレッドがCPUリソースを割り当てられたとしても、やはりリソースの浪費となります。このような待機時間もSQL Server内部で発生していることなのでWindowsスケジューラには判断できません。
SQL Server内のスケジューラ
SQL Server内で発生する待機状態は数多く存在します。SQL Serverが比較的小さな規模で使用されている場合は、それぞれの待機時間は短く、さして問題となることはありませんが、SQL Serverがより大きなサイズのデータベースを管理し、より多くのクライアントからのリクエストを効率的に処理するためには、すべてのスレッドのスケジュール管理をWindowsスケジューラだけに任せるのではなく、独自のコンポーネントを準備することになりました。サイベース社のSQL ServerをオリジナルとするSQL Server6.5までおソースコードを90%以上書き換えることになったSQL Server7.0から、そのコンポーネントは導入されました。CPUリソースを効率的に管理するこのコンポーネントは、SQL Server7.0とSQL Server2000では「UMS」（User Mode Scheduler）と呼ばれ、SQL Server 2005では「SQLOSスケジューラ」となりました。
SQLOSスケジューラとは
SQL Server内のスケジューラは、あくまでもSQL Serverというアプリケーション内のコンポーネントとなります。SQL Serverというアプリケーション内のコンポーネントのため、Windowsの下で動作するアプリケーションなので、依然としてWindows支配下にあります。Windowsスケジューラの支配下にありながら効率的なCPUリソースを使用させるための仕組みがSQLOSスケジューラとなります。
SQLOSスケジューラを構成するコンポーネント
スケジューラ
SQL Serverの起動時にCPU数もしくはコア数と等しい数のスケジューラが作成されます。ただし、SQL ServerのエディションやAffinityMaskの設定によっては、コンピュータに搭載されているCPU数もしくはコア数よりも少ない場合もあります。
スケジューラはワーカーの管理を行います。また、実際には様々な作業を行うための「隠し」スケジューラも存在します。
AffinityMaskとは
SQLServerインスタンスが使用するCPU数を制御するための設定です。例えば8個のCPUが搭載されているコンピュータで、2個のSQLServerインスタンスを稼働される場合、それぞれのワークロードの負荷によって、インスタンスAには2個のCPUを割り当て、インスタンスBには6個のCPUを割り当てることができます。
マルチタスク環境でのCPU管理の手法として、ノンプリエンプティブ/プリエンプティブという種類があります。前者はOSがCPU管理を行いません。CPUの使用権は、各アプリケーションがほかのアプリケーションへ自発的に譲る必要があります。
後者はタイムスライスなどのルールに基づいて、OSが各アプリケーションのCPU使用を管理します。
ノンプリエンプティブはOSがCPU管理を行わない分、とても軽いCPU負荷で済みます。ただし、いつまでもCPUを使い続けるアプリケーションが発生すると、他のアプリケーションは動作できず、システム全体に悪影響を与えます。一方、プリエンプティブでは、アプリケーション動作に依存せずOSがCPUの使用を管理するため、すべてのアプリケーションに安定してCPUリソースを割り振ることができます。その分、OSの実装が複雑となりCPUに与える負荷が高くなります。かつては、問題と考えられていたOSがCPUに与える負荷も最近のCPU性能ではほとんど無視できるレベルになっています。主要OSではプリエンプティブ方式が採用されています。
しかし、SQLOSスケジューラはノンプリエンプティブ方式を取り入れています。一旦SQLOSスケジューラ上で実行状態になったスレッドは、自らが譲り渡すまでスケジューラの使用権を取り上げられることはありません。これは、処理に必要とされるすべてのリソースを獲得済みのスレッドに対して、可能な限りCPUを割り当てて効率化を図ろうという設計思想に起因しています。
とは言え、１つのスレッドが使用権を保持し続けると、当然SQLServer全体でのスループットは低下します。そのため、長くても各スレッドは数ms（ミリ秒）の期間スケジューラを占有した後には、他のスレッドに使用権を譲るようにコーディングされています。Windowsスケジューラの観点から見るとSQLOSスケジューラ配下のすべてのスレッドはプリエンプティブで動作します。
ワーカー
SQL Serverでタスクが実行されるために必ず必要となるコンポーネントです。クライアントからクエリは様々な段階を経た後、最終的には1つ以上のワーカーに関連付けられて処理されます。ワーカーはSQL Server内のオブジェクトですが、Windowsの管理オブジェクトであるスレッドとして関連付けられます。1つのワーカーに対して、必ずWindowsスレッドが1つ存在します。
ワーカースレッドプール
各スケジューラで使用可能なワーカーの数を管理します。ワーカーの最大数は「max worker threads」の設定値に依存します。デフォルト値は255です。コンピュータに2個以上のCPUもしくはコアが搭載されている場合、各スケジューラが使用できるワーカー数は「max worker threads」の各設定値をスケジューラ数で割った値になります。
もしも、ワーカースレッドプールに使用可能なワーカーが存在しない時に、ワーカーの使用要求があると、最大数に達していない場合は新たにワーカーが作成されます。すでに最大数に達している場合は、使用要求をしたクライアントは後述のワークリクエストキューに追加され待機状態になります。
ランナブルキュー（Runnable Queue）
各スケジューラは、１つのランナブルキューを持っています。スケジューラは1つのワーカーだけをアクティブにします。もし、2個以上のワーカーが実行可能だった場合は、ランナブルキューにリストされ、スケジューラが使用可能になるのを待ちます。
ワークリクエストキュー（Work Request Queue）
各スケジューラは、作業を実行するためのワーカーをワーカースレッドプールに保持しています。しかし、多くの処理リクエストが発生すると、まれにワーカーの数が不足することがあります。ワーカーが使用可能になるのを待ちます。ワーカー不足の一般的な原因はブロッキングや過度な並列処理です。
I/Oリクエストリスト（I/O Request List）
I/Oリクエストを発行したワーカーは、要求したI/Oが完了するまでの期間、I/Oリクエストリストに追加されます。「I/Oの完了」はWindowsから見た場合のI/Oの完了に加えて、SQLServerが必要とするI/Oの後処理（例えばI/O処理に使用した内部リソースの解放等）までを含みます。それらがすべて終了した時点で、I/Oリクエストが完了したと見なされます。
ウェイターリスト（Waiter List）
ワーカーの処理実行時に、必要となるSQLServer内のリソース（ロックやラッチなど）を獲得できない場合は、ワーカーはウェイターリストに追加されます。ウェイターリストはほかのキューやリストとは違い、スケジューラが直接管理するものではありません。ウェイターリストは、SQL Server内のオブジェクトごとに存在します。
例えば、テーブルAのある行に対して、複数のワーカーがアクセスしてロック競合が発生した場合のウェイターリストは「ウェイターリストの概念図」のようになります。ロックを保持していたワーカーは、ロックが不要になった時点でロックを開放するとともに、ウェイターリストの先頭にいるワーカーへロックの獲得権を譲ります。
ラッチとは
データベースのリソースを保護することを目的としたとても負荷の軽い仕組みです。ロックがテーブルや行といった論理的な単位に対して獲得されるのに対し、ラッチはページやアロケーションユニットなどの物理的な単位に対して獲得されます。また、ロックはトランザクション内の論理的な整合性を保護するために長期間保持されることがありますが、ラッチはSQL Serverが必要とする期間（ページの読み込みや書き込みを行う期間など）が終了するとすぐに解放されます。
スケジューラの動作
１，クライアントがSQL Serverに接続するといずれかのスケジューラと関連付けされる
２，クライアントは処理を実行するワーカーが必要。ワーカスレッドプールに使用可能なワーカーが存在するか確認する。ここでは、プール内に使用可能なものが存在していることにする。
３，処理実行のためには、CPUリソースを割り当ててもらう必要があるため、ワーカーはランナブルキューに追加される。
４，ランナブルキューの上位にあったすべてのワーカーが処理された後、ワーカーAは実行状態になり、実行中であると情報がスケジューラに保持される
５，ワーカーAが処理を進めると、テーブルX全体への排他ロックが必要となったが、すでに他のワーカーがテーブルXに対するロックを取得している。そのため、テーブルXに対するロックリソースのウェイターリストに追加される。さらに、スケジューラの使用権をランナブルキューの先頭のワーカーへ譲り、自分は待機状態になる。
６，一定時間の経過後、テーブルXへの排他ロックが獲得できたワーカーAは再びランナブルキューに追加される。
７，上位にあったワーカーの処理が完了した後に、ワーカーAは実行状態になる
８，次に、ワーカーAはデータ取得のためにディスクへのI/Oが必要となる。そのため、I/Oリクエストを行った後にI/Oリクエストリストに登録される。さらに、スケジューラ使用権をランナブルキューの先頭のワーカーに譲り、自分は待機状態になる。
９，一定時間の経過後、I/Oリクエストが完了すると、ワーカーAは処理の継続のためにランナブルキューへ移動する
１０，ランナブルキューでの待機時間を経て、ワーカーAは実行状態になり、必要な処理を継続する。
１１，ワーカーAは獲得していたテーブルXのロックを開放し、ウェイターリストの先頭のワーカーに獲得権を譲る
１２，ワーカーAは処理の結果をクライアントに返す
１３，すべての作業を完了したワーカーAは、ワーカスレッドプールに自らを登録して次に使用される機会を待つ
SQLOSスケジューラが使う技
SQLOSスケジューラが管理するワーカーは、Windowsスケジューラから見ると単なるスレッドです。単なるスレッドなので、通常であれば、SQLOSスケジューラがどのような種類の待機リストに追加していたとしても、Windowsスケジューラは自らのルールに従った実行状態にしたり、あるいは待ち状態にします。ここでSQLOSスケジューラはちょっとした技を使用しています。待機リストに登録されているワーカーは「WaitForSingleObjectEx関数」を使用する際には、タイムアウト時間を設定します。そのタイムアウト時間に「INFINITE」という値を設定するとスレッドは無限待ち状態であることを示します。設定された「INFINITE」という値が別のものへ変更されるまで、Windowsスケジューラはそのスレッドに対して一切CPUリソースを割り当てません（つまりスレッドは実行状態になりません）
SQLOSスケジューラは、各スケジューラに「実行状態」であると登録されたスレッド（ワーカー）以外、つまりいずれかの待機リストに登録されたスレッドすべてに対して、タイムアウト時間として「INFINITE」を設定しています。これによりSQLOSスケジューラは、Windowsスケジューラから不適切なタイミングでスレッドにCPUリソースを割り当てることを防ぎます。また、同様に自らが必要とするスレッドのみに効率的にCPUリソースを割り当てることが可能になっています。
SQLOSスケジューラをモニタリングする
SQLOSスケジューラをモニタリングする方法はいくつか提供されています。
dbccコマンド
クエリツール（sqlcmd、osql、クエリアナライザ、SQL Server Management Studio等）でSQLServerインスタンスにログインして以下のコマンドを実行するとSQLOSスケジューラの基本的な情報を確認できます。得られた情報を元にSQLServerの内部的な状況を把握することによって、潜在的な問題を早期発見できます。
dbcc sqlperf('umsstats')このコマンドはSQLServer7.0以降のすべてのバージョンで実行できます。
「Scheduler ID」
CPU（もしくはコア）と同じ数だけ作成されたスケジューラを管理するための番号です。Scheduler IDが複数出力される場合は、複数CPUもしくはコアが搭載されています。
「num runnable」
ランナブルキューにリストされているワーカーの数を意味しています。この値が常に高い場合は、CPUリソースを待っているワーカーが常に存在することになります。そのため、要求されている作業量（実行されているクエリ数など）に対して、CPUの処理能力が追い付いてないことが推測できます。
「num workers」
コマンドを実行した時点でスケジューラが保持しているワーカーの数です。SQLOSスケジューラは、必要に応じてワーカーを作成していくため、必ずしもスケジューラは、必要に応じてワーカーを作成していくため、必ずしもスケジューラが管理可能な最大数のワーカーを常に保持しているわけではありません。
「idle workers」
ワーカースレッドプールにリストされているワーカー数です。この値として示される数だけのワーカーが、これから実行される処理の要求を待機している状態にあることを示しています。
「work queued」
ワークリクエストキューにリストされている処理の数です。ここに示される数だけの処理が、その実行に必要になるワーカーの空を待っていることを意味します。この値が常に高い場合は、実際にワーカーの数が不足していることもありますが、ほとんどの場合は別の潜在的な問題が存在することを示しています。たとえば、多くのワーカーが長時間ロック獲得待ちになっていたり、過剰な数の並列処理が行われていたりといったケースです。
動的管理ビュー
SQL Server2005以降のバージョンでは、さらに詳細なSQLOSスケジューラの情報を確認できる「動的管理ビュー」が提供されています。