システムが静かに稼働を始める――サーバーが深く息を吸い、データの血流が滞りなく巡り始める、その瞬間はまるで生き物の呼吸を感じるようです。
しかし日々の更新や削除という波が繰り返されるたび、データベースの「呼吸」は少しずつ乱れ、秩序だった動きに小さな亀裂が生まれていきます。
最も目に見える兆候が、インデックスの断片化です。
断片化は静かに、しかし確実にクエリ応答を遅くし、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 の最適戦略
| 項目 | REORGANIZE | REBUILD |
|---|---|---|
| 処理 | ページデフラグ | インデックス完全再作成 |
| 負荷 | 低 | 高 |
| 統計情報 | 更新されない | 更新される |
| 効果 | 外部断片化の解消 | 内外部断片化を完全解消 |
| 適用 | 軽微断片化（5〜30%） | 深刻断片化（30%以上） |
注意点（大規模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向けポイント）
- 診断：
sys.dm_db_index_physical_statsで定期確認 - 解消：セットベースで REORGANIZE / REBUILD を戦略的に適用
- 予防：FILLFACTORでページ分割を抑制
- 自動化：ジョブ化＆リソース監視で安全運用
サイクルを確立することで、大規模DBでも安定したパフォーマンスを維持できます。
