チューニングとは……スレッドとの格闘に尽きる：Dr. K's SQL Serverチューニング研修（9）（1/3 ページ）

[熊澤幸生, 工藤淳（インタビュアー），CSK Winテクノロジ/オフィスローグ]

SQL Serverは一般的にチューニング不要のデータベースと認識されている。しかし基幹系業務システムへの導入が進むにつれて、パフォーマンス・チューニングのニーズは急速に高まってきた。そこで本記事では、日本におけるSQL Serverコンサルタントの第一人者、熊澤幸生氏にSQL Serverチューニングのノウハウを語っていただくことにした。インタビュアーはSQL Serverへの造詣が深いITジャーナリスト、工藤淳氏が担当する。（編集部）

　本連載はいよいよ今回が最終回となります。本当に満足のいくパフォーマンスを実現するシステムを作ろうとするならば、SQL Serverのアーキテクチャに基づいて、クライアントが依頼した命令がどの階層を通ってどうなっていくかを推測できないといけません（図1）。

図1　SQL Serverのアーキテクチャ

　その手掛かりとなるのがスレッドです。今回はSQL Serverの内部で動いているさまざまなスレッドを紹介し、それがどのような仕組みでパフォーマンスに影響を与えているかについて説明していきましょう。

ユーザースレッドは、多くの“裏方”スレッドに支えられている

　SQL Serverでは1つのトランザクションが処理される背後で、さまざまな役割のスレッド、例えてみれば舞台の“裏方”のようなスレッドが動いています。こういう裏方スレッドが、実はSQL Serverでは非常に重要なのです。通常の処理では、クライアントがSQL Serverに対してコネクションを張って、いろいろな命令をT-SQLを通してやりとりしているのですが、それを支えている裏方がいっぱいいるわけです。そういうスレッドは、一見主役に見えるユーザーのスレッドよりも高い優先順位で動かないといけない場合もあるのです。

　またCPUの数という点でも同様です。CPUの数が足りなくて、裏方の使うCPU領域が確保できなければ、処理はスムーズに進みません。それなら、ユーザーからの要求を処理するCPUと、裏方を支えるCPUを別個に用意すればいいですね。だからこそ、私は前回「進化するCPUをパワー全開で走らせるテクニック」でマルチCPUの話をしたわけです。パフォーマンスを上げたいのなら、そういう裏方のために、いくつの専用CPUを与えてあげればいいのかということです。

　データベースエンジンの内部で、いくつか重要な機能を担っている裏方があります。例えば「I/Oマネージャ」という、ディスク（OS上のファイルシステム）とのやりとりをする裏方。バッファ・プール上の、物理ディスク領域に未反映である更新済みデータ（ダーティページ）を、チェックポイント処理で、ある一定期間ごとに一括実更新でディスクに反映する裏方。また、トランザクションログは、リアルタイムでディスクに書き込まなくてはならないので、それ専用の裏方が必要です。

　同じように、ファイルI/Oの裏方というのは、ドライブレターあるいはファイルの単位で存在します。さらに、ネットワークとやりとりする裏方、受け取ったT-SQLの文法チェックを行ってストレージエンジンが分かる形に変換する、インタプリタのような役割をする裏方もあります。こうした目に見えない裏方が、SQL Serverの中に多数のスレッドとして存在しているわけです。

　スレッドの数は「MAX WORKER THREAD」として指定されています。既定値は255です。

・第2回「誰も知らないメモリ・チューニングの極意を教えよう」の3ページ目を参照

　だからCPUが4つ載っているマシンならば、CPU単位に生成されるUMS（User Mode Scheduler）ごとに、64個のスレッドが結び付いているわけです。

・第1回「SQL Serverというブラックボックスを開いてみる」の1ページ目、図1参照

　なお、ユーザーがSQL Serverに接続した時点で、そのコネクションがどのスケジューラで処理されるかが決まってしまいます。処理が終わるまでの間、自分の要求は何番のUMSがやってくれるのか自動的に決まってしまい、最後まで変わりません。

裏方スレッドの2大勢力
「Special UMS Workers」と「Special Non-UMS Threads」

　では、裏方専用のスレッドにはどういうものがあるか少し見てみたいと思います。これらは「Special UMS Workers」と呼ばれます。まず、UmsIdolですね。これはCPUが遊んでいる状態のときに、専用のIdolというスレッドで一括管理します。

　「Log Writer」はトランザクションログ書き込み、「Lazy Writer」は更新済みデータの一括書き込み専用のスレッドです。

　「Lock Monitor」は排他制御の状態を監視しています。これはSQL Serverのアーキテクチャでいうと、Storage Engine内の「Lock Manager」を指しています。

　「Checkpoint」はバッファマネージャです。バッファの中のどのページが「更新されている／されていない」という情報をビットマップで持っているのです。それがある閾値を超えると、Lazy Writerというスレッドで一括更新するタスクをキックして、バッファフラッシュという形で一括更新するわけです。

　こういうスレッドたちが巧みに連動して処理を円滑に実行しているわけです。SQL ServerにはSPID（システムプロセスID）というのがあって、Special UMS Workersが関係するものは通常、50以下の値を持ちます。一方、User SPIDは51以上です。だからSPIDの番号を見るとシステムのSPIDなのかユーザーのSPIDなのか判別できるのです。

　このほかに「Special Non-UMS Threads」、つまりUMSの管理配下にないスレッドもあります。この主なものとしては、1番目は「FRunCM」と呼ばれるデッドロックモニタです。60秒ごとに動いて、デッドロックが発生しているか監視しています。

　また、「Wait for Client Connect」というスレッドがあります。これはネットワークの無応答が何秒以上の場合は切断するといった設定に基づいてネットワークを監視するものです。

　ほかにもオープンデータサービス（ODS）の2フェイズコミットを専用に監視するスレッドや、ネットワークのデータを読むためのスレッドなどが、UMSとは別個のところで動いています。つまりOSのスレッドとして動いているわけです。

　これらUMSモードのシステムスレッドとそれ以外のスレッドの両方が動いていることを覚えておきましょう。（次ページへ続く）