排他制御の落とし穴を避けるインデックス設計：Dr. K's SQL Serverチューニング研修（5）（2/3 ページ）

フラグメンテーションは定期的に解消する

　ページの整合性で思い出しましたが、SQL Server 2000は、ファイルシステムとのI/Oをどう行っているのかご存じですか。SQL Server 2000では、8Kbytesのページが8つ＝64Kbytesのエクステントで読み込み処理を行います。64Kbytes（エクステント）の中にあるデータが「P1」、「P2」……と物理的にきれいに並んでいれば問題ないのですが、フラグメンテーションが進んでしまったシステムで、「P2」だけがどこか別のエクステントに格納されていたりすると、データへの物理アクセスの必要が生じるたびに、SQL Server 2000は別のエクステントから「P2」のデータを取得しバッファプールに転送します。このとき、新たな8Kbytesのページをバッファプール上に確保するために、ページI/Oラッチを発行します。

　フラグメンテーションは、ふだん具体的な障害や予定外の挙動となって現れないため、気にしない人はいつまでたっても気にしません。しかし、見えないところでSQL Server 2000のパフォーマンスを下げている、成人病のような怖い存在なのです。

　ここでフラグメンテーションを解消するためのDBCCコマンドをご紹介しておきましょう。

DBCC DBREINDEX
DBCC INDEXDEFRAG

　「DBREINDEX」は強力で、エクステントを含めて連続した領域に再構築できますが、シングルユーザーでしか使えません。オフタイムに使うコマンドです。一方「INDEXDEFRAG」はオンラインでユーザー接続中でも実行可能ですが、エクステントの断片化は解消できません。

クラスタ化インデックスは何を基準に決定すべきか

　クラスタ化インデックスに用いる列の選択はとても重要です。システム稼働後に、INSERT処理で発生するキー値の特性を知っておく必要があります。

　よく見られる設計でint型の列にIdentityを付与して、自動採番により常に最大値が発生する設計をしている場合があります。この場合、テーブル内に常に最大キー値を持つ行がINSERTされ、このデータページのページヘッダ情報が、複数トランザクション処理の同時実行性を阻害する要因になることがあります。

　クラスタ化インデックスの決定は、INSERT処理でランダムなキー値が発生する列を選択することを推奨します。キー値の変更処理と、キー列への可変長属性の選択とNULLは厳禁です。INSERT処理時に、キー値のランダム性がある場合に、有効なインデックス定義の属性が「FILL　FACTOR」で、0〜100％の範囲に指定可能です。

図4　FILL FACTORの設定（SQL Server 2000）
SQL Server Enterprise Managerからテーブルを右クリックし、ショートカットメニューから「テーブルのデザイン」を選択。Table Designerが開いたら、FILL FACTORを指定すべきインデックスを持つ列を右クリックし、ショートカットメニューの「インデックス／キー」を選択すると、このダイアログボックスが表示される。「FILL FACTOR」ボックスには0〜100の数値を設定する。

　FILL FACTORに1〜99％の範囲を指定した場合、インデックス作成、再編成時に、この値を超えない範囲で、初期ロードが行われます。いい換えれば、各データページ内に、トランザクション処理で追加するための予約領域を確保することになります。この指定により、トランザクション処理で発生するページ分割を回避することが可能です。

　では、ページ分割はINSERT処理だけで発生するものでしょうか。例えば、列の属性には、可変長、NULLの定義が可能です。これらの属性を持つテーブルにUPDATE処理が発生し、データ長の増加、新たな値が加わった場合は、ページ内の同じ場所に格納することができなくなります。その結果、トランザクションでの更新処理においてもページ分割が発生します。（次ページへ続く）