SQL Serverインデックスメンテナンス完全ガイド

システムが静かに稼働を始める――サーバーが深く息を吸い、データの血流が滞りなく巡り始める、その瞬間はまるで生き物の呼吸を感じるようです。

しかし日々の更新や削除という波が繰り返されるたび、データベースの「呼吸」は少しずつ乱れ、秩序だった動きに小さな亀裂が生まれていきます。

最も目に見える兆候が、インデックスの断片化です。

断片化は静かに、しかし確実にクエリ応答を遅くし、CPUやI/Oリソースというシステムの心臓を蝕む――まさにパフォーマンスの暗殺者です。

気づかぬまま放置すれば、やがてシステム全体の生命力を奪いかねません。

この記事では、データの迷宮に潜む影の脅威を、制御する術をまとめています。

診断から予防、あなたはデータベースの奥深くに入り込み、その呼吸を再び整え、力強く滑らかに動かす秘策を手に入れることができるのです。

第1章｜インデックス断片化のメカニズム

SQL Serverのデータは「ページ」という単位で物理格納されます。

インデックスはページが効率よく並ぶことで高速検索を実現しますが、INSERT、UPDATE、DELETEが繰り返されると以下が発生します。

• ページ分割 (Page Split)：新規データ挿入時にページが分割され、論理順序と物理格納順がズレる → 外部断片化
• 空き領域の発生：削除や更新で空きが残る → 内部断片化

これにより余計なディスクI/Oが発生し、システムパフォーマンスを低下させます。

第2章｜断片化の診断と指標

代表的指標

• avg_fragmentation_in_percent：外部断片化率
• page_count：インデックスサイズ。巨大テーブルでは断片化率が低くても影響大
• avg_page_space_used_in_percent：内部断片化指標

診断クエリ（サンプル）

SELECT 
    OBJECT_SCHEMA_NAME(PS.object_id) AS schema_name,
    OBJECT_NAME(PS.object_id) AS table_name,
    IDX.name AS index_name,
    PS.avg_fragmentation_in_percent,
    PS.page_count,
    PS.avg_page_space_used_in_percent
FROM 
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS PS
LEFT JOIN sys.indexes AS IDX 
    ON PS.object_id = IDX.object_id AND PS.index_id = IDX.index_id
WHERE PS.index_id > 0
  AND PS.page_count > 100
ORDER BY PS.avg_fragmentation_in_percent DESC;

大規模DBのヒント: 全ページスキャン (DETAILED) は高負荷のため、まずは SAMPLED で確認。ページ数が数百万単位のテーブルは別スケジュールでメンテナンス推奨。

第3章｜REORGANIZE と REBUILD の最適戦略

注意点（大規模DB）

• ONLINE=ON はEnterprise Edition限定
• REBUILD 時は tempdb とトランザクションログの増加に注意
• 巨大テーブルでは断片化率15%でも REBUILD 検討が必要
• メンテナンス時間を業務影響が少ない深夜・週末に設定

再構築と再構成についての詳細はこちらから。

第4章｜実践スクリプト（セットベース版）

大規模DBではカーソルよりセットベース処理が高速です。

以下は断片化率に応じて REORGANIZE/REBUILD を自動判断する例です。

WITH Targets AS (
    SELECT 
        PS.object_id,
        PS.index_id,
        PS.partition_number,
        PS.avg_fragmentation_in_percent,
        IDX.name AS index_name,
        OBJECT_SCHEMA_NAME(PS.object_id) AS schema_name,
        OBJECT_NAME(PS.object_id) AS table_name
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') PS
    JOIN sys.indexes IDX
        ON PS.object_id = IDX.object_id AND PS.index_id = IDX.index_id
    WHERE PS.index_id > 0
      AND PS.page_count >= 100
)
SELECT *,
    CASE 
        WHEN avg_fragmentation_in_percent >= 30 THEN 'REBUILD'
        WHEN avg_fragmentation_in_percent >= 5 THEN 'REORGANIZE'
    END AS Action
INTO #TargetsToMaintain
FROM Targets
WHERE avg_fragmentation_in_percent >= 5;

-- 動的SQLで一括処理
DECLARE @sql NVARCHAR(MAX);
SELECT @sql = STRING_AGG(
    'ALTER INDEX [' + index_name + '] ON [' + schema_name + '].[' + table_name + '] ' + Action + 
    CASE WHEN Action='REBUILD' THEN ' WITH (ONLINE = ON, SORT_IN_TEMPDB = ON);' ELSE ';' END
, CHAR(10))
FROM #TargetsToMaintain;

EXEC sp_executesql @sql;
DROP TABLE #TargetsToMaintain;

ポイント: カーソルを避けることで、大規模DBでも短時間で処理可能。

負荷軽減したい場合、特定スキーマやテーブルに絞る。バッチ処理にする。実行順を断片化率の高い順にして、時間切れになっても優先度の高いものから処理。などが考えられます。

第5章｜断片化予防と FILLFACTOR

• 高頻度更新テーブルでは FILLFACTOR=80〜90 を設定
• ページに余裕を持たせることでページ分割を減らし、断片化進行を抑制

CREATE INDEX IX_YourIndex ON YourTable(YourColumn)
WITH (FILLFACTOR = 85);

• 注意: 空き領域が増えるためストレージ効率は低下。更新頻度に応じて調整。

第6章｜運用自動化とモニタリング

• SQL Server エージェントで夜間・週末にジョブ化
• メンテナンス後は sys.dm_db_index_usage_stats や sys.dm_db_index_physical_stats で効果確認
• REBUILD で tempdb/log 増加が発生する場合は、事前に容量監視

まとめ（大規模DB向けポイント）

1. 診断：sys.dm_db_index_physical_stats で定期確認
2. 解消：セットベースで REORGANIZE / REBUILD を戦略的に適用
3. 予防：FILLFACTORでページ分割を抑制
4. 自動化：ジョブ化＆リソース監視で安全運用

サイクルを確立することで、大規模DBでも安定したパフォーマンスを維持できます。