I/Oボトルネックの病巣はこれで究明できる：Dr. K's SQL Serverチューニング研修（7）（1/3 ページ）

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

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

　前回の「I/Oチューニングを成功させる必修ポイント」では、I/Oの負荷分散という観点から物理ファイルを分割するメリットについて解説しました。今回はI/Oチューニングの後半として、I/Oボトルネックを発生させる原因を究明するテクニックについて解説します。そのためには、SQL Serverの内部構造を正しく理解する必要があります。

ユーザーデータベースの領域を管理する2つの方法を知ろう

　今回はSQL Serverとファイルシステムの関係に関して、もう少し深いところに踏み込んでみましょう。SQL Serverのファイル構造と領域管理です。これには、ユーザーデータベースの領域管理を2種類に分けて考えていきます。

　図1　SQL Serverのファイル構造と領域管理

　SQL Serverから見た場合、ユーザーデータベースと呼ばれる管理領域の下位階層には、n個のファイルグループがあります。ファイルグループのさらに下位階層にn個のファイルがあり、各ファイルはWindows OSが管理するディスクに配置されます。ファイルの下位階層にはn個のエクステントがあります。エクステントというのは64Kbytes（8Kbytesのページ×8個）で構成されます。エクステントには2種類あって、エクステントとオブジェクトの関係により、1つのエクステントに1種類のオブジェクトが入ったものと、n種類のオブジェクトが入ったものに分けられ、それぞれ単一エクステントと混合エクステントと呼ばれます。SQL Server起動パラメータでトレースフラグ「T1118」を指定することにより、混合エクステントの利用をサーバ全体で禁止し、複数トランザクションの同時実行性を高めることも可能です。

　オブジェクトの種類は、まずデータページです。データページにも3種類あって、通常のデータのほかにBLOBとCLOBがあります。通常のベースとなるデータのデータ長は8096bytesを超えることができません。しかし、画像データのようなバイナリーデータやXMLで書かれた巨大なテキストファイルといったデータは8Kbytesの通常ページに収まらないので、BLOBやCLOBといった専用のデータページを持たせるのです。通常ページ内には、これらのBLOB／CLOBが格納されているページへのリンク用ポインタを持ち、論理的なデータとして連結されます。このほかのオブジェクトには、インデックスページやストアドプロシージャなどがあります。

　こういった各種のオブジェクトはすべてエクステントという同一の領域に入るわけですから、的確な領域管理が必要になります。ただWindows OSから見えている管理単位はファイルですが、SQL Serverから見た領域管理はファイルグループ、ファイル、エクステントと階層化されています。この違いに注意する必要があります。

　例えば、SAP用のデータベースはファイルを複数のファイルグループに分けて格納することを禁じています。その代わり、1つのファイルグループの下に複数（サーバ上の物理CPUコア数を推奨）の同容量ファイルを配置し、これらに対してデータ格納時にラウンドロビンのアルゴリズムを使って、均等にデータをストライピングしていくという手法を取っています。これはこれで非常に良い領域管理の方法です。論理的な格納用の単位が1つのファイルグループであっても、その下はn個のファイルから構成されているので、ディスクI/Oの負荷分散になるわけですね。SQL Serverではこういう手法も可能なのです。

　この手法は、tempdbのデータ領域の設定にも有効で、同時稼働トランザクションが必要な一時データ領域確保時の、空領域確保処理を並列化することが可能で、同時実行性の向上が期待できます。

　またSQL Serverのファイル単位は、「MDF」と呼ばれるプライマリ、「NDF」と呼ばれるセカンダリのファイルで構成されているわけですが、あるファイルはデータページ専用、あるものはBLOB専用、またあるものはインデックス専用といった具合に、データベースの定義を行うときにファイルグループを作成し、I/Oの負荷分散を行うことも可能です。

　このようにデータの領域管理には、2種類あるということですね。1つの管理単位に対してn個のファイルを作っておいて、そこをラウンドロビンで負荷分散していくという方法と、オブジェクトの種類ごとに専用のファイルを設けてn個のファイルグループによって管理するという2種類の方法があるということです。

データ領域の増分指定は万が一の保険。
「足りなくならない」設計を心掛けよう

　次は新規にデータベースを作成する際の話です。「CREATE DATABASE database_name ……」というDDL文を使ってデータベース名を指定し、物理ファイル名（ファイルグループとファイル）を指定して作る際に、ファイルをどれだけの初期サイズでどのパーティションに作るか考えてみましょう。このSQLに必要なのは論理名（NAME）、物理ファイルへのパス名（FILENAME）、初期サイズ（SIZE）、そしてオプションで増分指定（FILEGROWTH）などがあります（リスト1）。

CREATE DATABASE test0608 ON PRIMARY ( NAME = SPri1_dat,　　←論理名    FILENAME = 'c:\test0608\data\SPri1dat.mdf',　　←物理ファイルへのパス名    SIZE = 10,　　←初期サイズ（既定値：Mbytes）    MAXSIZE = 50,    FILEGROWTH = 15% ),　　←増分指定     ……

リスト1　データベースを作成するSQL

　SQL Serverの場合、データベースの新規作成はGUIの環境でできてしまうのですが、実はこれがクセモノです（図2）。開発時は、それほど大きい容量のデータベースは必要としません。このためいいかげんな領域設定でも、問題なく作れてしまうわけです。その設定値のまま本番環境に持っていってアタッチしたりすると、最初に作ったいいかげんな情報をそのまま引き継いでしまうわけです。

図2　SQL Server 2000のEnterprise Managerから新規データベースを作成するダイアログ
初期設定では「ファイルの自動拡張」にチェックが入っており、「ファイルの最大サイズ」は「ファイルを無制限に拡張」が選択されている。

　私が指摘したいのは、チューニングという観点から見て「初期アロケーションサイズの見積もりと、データ増分領域見積もりをちゃんとやっていますか」ということです。おそらく9割の人は何もやっていないと答えると思います。SQL Serverはすごく賢くて、領域が足りなくなると自動拡張してくれますから、ユーザーは何も考えなくても領域管理の面倒を見てもらえるのです。いったん作ったデータベースの領域が足りなくなると、増分指定によってSQL Serverが自動的にファイルを拡張してくれます。

　ところが私は、増分指定というのは万が一のときの保険だと思っているのです。最初から「便利に使える機能」という見方は誤っています。

　もし初期のサイズ見積もりが間違っていて、運用途中で領域が枯渇したらどうなるでしょうか。まず、ファイルを増大させる更新系のクエリが受け付けられなくなります。するとユーザーは「増分指定を使えばいいや」と考え、新たなディスク領域をディスクサブシステムに対して動的に確保し、再び何事もなかったかのようにデータベースを使い続けるでしょう。

　しかしSQL Serverの運用中に新規アロケーションを行うと、「ページヘッダの初期化」が行われます。さらにビットマップの作成などを行いつつ、領域の確保を行います。もし通常のトランザクションが行われているときにファイルの自動拡張が起きると、トランザクション処理と並行して上記のようなプロセスを実行しなくてはいけません。これがSQL Serverにとっては大きな負荷となり、接続利用者からはレスポンスの乱れとして体感されます。そういうこともあって、この増分指定というのは、本当に最悪の事態に備えた保険という考え方で使ってください。基本はやはり初期設定における、正確なサイズ見積もりなのです。（次ページへ続く）