SQL Serverのパフォーマンスチューニング：インデックス断片化対策の基礎と実践

SQL Serverを使用していると、データベースのパフォーマンスが徐々に低下することがあります。その大きな原因の一つがインデックスの断片化です。

インデックス断片化は、データの更新や削除、挿入操作が繰り返されることでインデックスページが効率的に利用されなくなる現象です。

この記事では、SQL Serverのインデックス断片化を調査・対策する方法を学びます。特に、DBCC SHOWCONTIG や DBCC INDEXDEFRAG の使い方を中心に説明し、インデックスの最適化を通じてデータベースのパフォーマンスを向上させる方法を解説します。

インデックス断片化とは？

インデックス断片化には主に以下の2種類があります：

  1. 内部断片化（Internal Fragmentation）
    • インデックスページ内の未使用領域が多くなること。
    • データがランダムに挿入されると、インデックスページが無駄に使用される可能性があります。
    • これによりI/Oの効率が低下します。
  2. 外部断片化（External Fragmentation）
    • インデックスページが物理的に連続していないこと。
    • ページが飛び飛びに配置されると、SQL Serverが連続した読み取りを行えなくなり、パフォーマンスが低下します。

インデックス断片化の影響

断片化が進行すると、以下のような問題が発生します。

  • クエリの実行速度が低下する
  • ディスクI/Oの増加
  • メモリの非効率な利用

インデックス断片化の調査方法

SQL Serverでは、断片化の状態を調査するためにいくつかの手法が提供されています。その代表的な方法が DBCC SHOWCONTIG と sys.dm_db_index_physical_stats です。

(1) DBCC SHOWCONTIG の使い方

DBCC SHOWCONTIG コマンドを使用すると、特定のインデックスの断片化状況を確認できます。以下は基本的なクエリ例です。

DBCC SHOWCONTIG ('テーブル名', 'インデックス名');
主な出力項目
  • Scan Density (%)
    高いほど断片化が少ない状態。100%が理想的です。
  • Logical Fragmentation (%)
    断片化の割合。低いほど良い状態です。

Sales テーブルの IX_Sales_Date インデックスを調査する場合の例です。

DBCC SHOWCONTIG ('Sales', 'IX_Sales_Date');

(2) sys.dm_db_index_physical_stats を使った調査

sys.dm_db_index_physical_stats ビューは、より詳細な情報を提供します。このクエリを使用すると、データベース全体または特定のインデックスの断片化を調べられます。

SELECT
    OBJECT_NAME(ips.OBJECT_ID) AS TableName,
    i.name AS IndexName,
    ips.index_type_desc,
    ips.avg_fragmentation_in_percent
FROM
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
INNER JOIN sys.indexes i
    ON ips.OBJECT_ID = i.OBJECT_ID
    AND ips.index_id = i.index_id
WHERE
    ips.avg_fragmentation_in_percent > 10
ORDER BY
    ips.avg_fragmentation_in_percent DESC;

インデックス断片化への対策

断片化が確認された場合、以下の方法で対策を行います。

(1) DBCC INDEXDEFRAG を使った断片化の解消

DBCC INDEXDEFRAG を使用すると、断片化をオンラインで再構築できます。これは軽度の断片化に適しています。

DBCC INDEXDEFRAG (データベース名, テーブル名, インデックス名);
例 
DBCC INDEXDEFRAG ('AdventureWorks', 'Sales', 'IX_Sales_Date');

(2) インデックスの再構築（Rebuild）

断片化が深刻な場合は、インデックスの再構築が推奨されます。再構築は以下の方法で実行できます。

ALTER INDEX ALL ON テーブル名 REBUILD;

または、特定のインデックスを対象にする場合の例です。

ALTER INDEX インデックス名 ON テーブル名 REBUILD;

定期的なメンテナンスの実施

インデックス断片化を防ぐためには、以下のような定期的なメンテナンスをスケジュール化することが重要です。

  • 週次または月次でのインデックスの監視
  • インデックスの再構築または再編成を定期的に実施
  • インデックスの設計見直し（必要に応じて）

演習問題

問題 1

以下のコードを実行した際に得られる情報として正しい番号を選んでください。

DBCC SHOWCONTIG ('Orders', 'IX_Orders_CustomerID');
  1. テーブルの総データ量
  2. インデックスの断片化の割合
  3. インデックスのメモリ使用量
  4. テーブル内のNULL値の割合

問題 2

以下のクエリで確認できるカラム avg_fragmentation_in_percent の値が示す内容として正しい番号はどれですか？

SELECT avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED');
  1. テーブルの行数
  2. インデックスのフラグメント化の割合
  3. データベースの使用容量
  4. インデックスの作成時間

問題 3

次のシナリオにおいて適切なアプローチの番号を選んでください。

「SalesテーブルのIX_Sales_Dateインデックスの断片化が20%を超えているが、テーブルが非常に大きくダウンタイムを許容できない。」

  1. インデックスを削除して再作成する
  2. DBCC INDEXDEFRAG を使用する
  3. ALTER INDEX ALL ON Sales REBUILD を使用する
  4. クエリを再設計する

解答例

  • 問題 1：2. インデックスの断片化の割合
  • 問題 2：2. インデックスのフラグメント化の割合
  • 問題 3：2. DBCC INDEXDEFRAG を使用する

まとめ

インデックス断片化は、SQL Serverのパフォーマンスを低下させる大きな要因です。

本記事で解説した手法を用いることで、断片化を特定し、適切に対応する方法を学ぶことができます。

定期的なメンテナンスを行い、快適なデータベース環境を保ちましょう。