SQLインデックスの断片化とメンテナンス

インデックス断片化とは

インデックスの断片化（Fragmentation）は、SQL Serverのパフォーマンスに影響を与える重要な要因です。時間の経過とともに、INSERT、UPDATE、DELETEなどの操作により、インデックスの物理的な配置が論理的な順序と一致しなくなる現象を指します。

断片化の種類

SQL Serverでは、主に2つの観点からインデックスの健全性を評価します：

1. 論理的断片化（Logical Fragmentation）

   • インデックスのキー値に基づく論理的な順序と、物理的なページの順序が一致しない状態
   • avg_fragmentation_in_percent で測定
   • 高い値はランダムI/Oの増加を意味する

2. ページ密度（Page Density）

   • ページ内でデータが占める割合
   • avg_page_space_used_in_percent で測定
   • 低い値は、同じデータを格納するために多くのページが必要となり、I/Oが増加

断片化が発生する原因

1. データの挿入

新しい行がテーブルに追加されると、既存のページが分割（Page Split）される可能性があります。

-- ページ分割の例
-- 既存のページが満杯の状態で新しい行を挿入
INSERT INTO Orders (OrderID, CustomerID, OrderDate)
VALUES (12345, 'CUST001', GETDATE());
-- → ページ分割が発生し、データが2つのページに分散

2. データの更新

可変長データ型（VARCHAR、NVARCHAR）の更新により、行のサイズが変化すると、ページ内で収まらなくなる場合があります。

3. データの削除

行が削除されても、そのページはすぐには解放されず、空白領域が残ります。

断片化によるパフォーマンスへの影響

1. I/Oの増加

• 断片化されたインデックス: 複数の小さなI/O要求が必要
• 断片化されていないインデックス: 少数の大きなI/O要求で済む

2. シーケンシャルI/O vs ランダムI/O

┌───────────────────────────────────┐
│ シーケンシャルI/O（断片化なし）   │
│ [Page1][Page2][Page3][Page4]      │
│ ↓ 効率的な連続読み取り            │
└───────────────────────────────────┘

┌───────────────────────────────────┐
│ ランダムI/O（断片化あり）         │
│ [Page1]...[Page4]...[Page2][Page3]│
│ ↓ 非効率なランダムアクセス        │
└───────────────────────────────────┘

3. メモリ効率の低下

ページ密度が低いと、同じデータをキャッシュするためにより多くのメモリが必要になります。

断片化の測定方法

sys.dm_db_index_physical_stats の使用

-- 現在のデータベースの全インデックスの断片化とページ密度を確認
SELECT 
    OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
    OBJECT_NAME(ips.object_id) AS object_name,
    i.name AS index_name,
    i.type_desc AS index_type,
    ips.avg_fragmentation_in_percent,
    ips.avg_page_space_used_in_percent,
    ips.page_count,
    ips.alloc_unit_type_desc
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS ips
INNER JOIN sys.indexes AS i 
    ON ips.object_id = i.object_id
    AND ips.index_id = i.index_id
ORDER BY page_count DESC;

結果の解釈

avg_fragmentation_in_percent	推奨アクション
< 5%	メンテナンス不要
5% - 30%	再構成（REORGANIZE）を検討
> 30%	再構築（REBUILD）を検討

インデックスメンテナンス方法

1. インデックスの再構成（REORGANIZE）

特徴:

• オンライン操作（ロックが少ない）
• リソース消費が少ない
• リーフレベルのみを最適化
• 中断可能（進行状況が保持される）

構文:

-- 単一インデックスの再構成
ALTER INDEX IX_Orders_CustomerID 
ON Sales.Orders
REORGANIZE;

-- テーブルの全インデックスを再構成
ALTER INDEX ALL 
ON Sales.Orders
REORGANIZE;

利点:

• 本番環境で実行中のクエリへの影響が最小限
• トランザクションログの使用量が少ない
• ディスク領域の追加が不要

2. インデックスの再構築（REBUILD）

特徴:

• インデックスを削除して再作成
• すべてのレベルで断片化を解消
• オフラインまたはオンラインで実行可能
• より多くのリソースを消費

構文:

-- オフライン再構築（高速だがロックが発生）
ALTER INDEX PK_Orders_OrderID 
ON Sales.Orders
REBUILD;

-- オンライン再構築（実行中もテーブルにアクセス可能）
ALTER INDEX PK_Orders_OrderID 
ON Sales.Orders
REBUILD WITH (ONLINE = ON);

-- 再開可能なオンライン再構築（中断・再開が可能）
ALTER INDEX PK_Orders_OrderID 
ON Sales.Orders
REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);

オプション:

-- FILL FACTORの指定（ページ分割を減らすため）
ALTER INDEX IX_Orders_OrderDate 
ON Sales.Orders
REBUILD WITH (FILLFACTOR = 80);

-- 統計の更新を無効化（別途更新する場合）
ALTER INDEX ALL 
ON Sales.Orders
REBUILD WITH (STATISTICS_NORECOMPUTE = ON);

3. 再構成 vs 再構築の選択基準

項目	再構成（REORGANIZE）	再構築（REBUILD）
断片化率	5% - 30%	> 30%
リソース消費	低	高
実行時間	短～中	中～長
ロック	最小限	オフライン時は排他ロック
トランザクションログ	少ない	多い
ディスク領域	不要	インデックスサイズの約2倍
統計更新	なし	フルスキャンで更新

インデックスメンテナンス戦略

推奨アプローチ

1. 測定から始める

   • インデックスの断片化を定期的に監視
   • パフォーマンスへの実際の影響を確認

2. 統計更新の重要性

   • 多くの場合、インデックス再構築による改善は統計更新によるもの
   • より低コストで統計更新を実行可能

-- 統計の更新（フルスキャン）
UPDATE STATISTICS Sales.Orders 
WITH FULLSCAN;

-- 特定のインデックスの統計のみ更新
UPDATE STATISTICS Sales.Orders IX_Orders_CustomerID 
WITH FULLSCAN;

3. 段階的なアプローチ

-- ステップ1: 断片化を確認
-- ステップ2: 統計を更新してパフォーマンスを測定
-- ステップ3: 改善が不十分なら再構成を実行
-- ステップ4: それでも不十分なら再構築を検討

Azure SQL Database / Azure SQL Managed Instance での考慮事項

特有の制約

1. リソースガバナンス

   • CPU、メモリ、I/Oに制限がある
   • メンテナンス実行時は他のワークロードに影響

2. コスト最適化

   • 最小限のリソースでプロビジョニングされることが多い
   • メンテナンスのための余剰リソースが少ない

3. レプリケーションへの影響

   • 読み取りスケールアウト使用時、レプリカの遅延が増加
   • Geo レプリケーション使用時も同様

推奨事項

-- オンライン再構成を使用（推奨）
ALTER INDEX IX_Orders_CustomerID 
ON Sales.Orders
REORGANIZE;

-- または再開可能なオンライン再構築
ALTER INDEX IX_Orders_CustomerID 
ON Sales.Orders
REBUILD WITH (ONLINE = ON, RESUMABLE = ON);

Azure特有のシナリオ:

• ファイル圧縮前のページ密度向上
• データベースサイズ制限への対応

GUIDを主キーとして使用する際の懸念点

GUID（Globally Unique Identifier）の特性

GUIDは16バイトのバイナリ値で、グローバルに一意な識別子を生成できます。

-- GUIDを主キーとして使用する例
CREATE TABLE Customers
(
    CustomerID UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    CustomerName NVARCHAR(100),
    Email NVARCHAR(100)
);

GUIDの主な問題点

1. ランダム性による断片化

問題:

• NEWID() で生成されるGUIDはランダム
• クラスター化インデックスのキーとして使用すると、挿入のたびにページ分割が発生
• インデックスが極度に断片化

ランダムGUIDの挿入パターン:
┌────────────────────────────────────┐
│ [GUID-5] [GUID-2] [GUID-8] [GUID-1]│
│ ↓ あらゆる場所でページ分割発生    │
└────────────────────────────────────┘

順次IDの挿入パターン:
┌────────────────────────────────────┐
│ [ID-1] [ID-2] [ID-3] [ID-4]        │
│ ↓ 末尾のみで挿入（分割なし）      │
└────────────────────────────────────┘

2. ストレージオーバーヘッド

データ型	サイズ	備考
INT	4バイト	最大約21億行
BIGINT	8バイト	最大約922京行
UNIQUEIDENTIFIER (GUID)	16バイト	INT の 4倍、BIGINT の 2倍

影響:

• 主キー値が各非クラスター化インデックスにコピーされる
• ストレージ使用量が大幅に増加
• I/Oとメモリ消費も増加

3. パフォーマンスへの影響

-- 非効率: ランダムGUID
CREATE TABLE Orders
(
    OrderID UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY CLUSTERED,
    CustomerID INT,
    OrderDate DATETIME
);
-- 問題点:
-- ✗ 挿入のたびにランダムな位置でページ分割
-- ✗ インデックスが断片化
-- ✗ I/Oが増加

-- 改善策1: IDENTITY を使用
CREATE TABLE Orders
(
    OrderID BIGINT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    OrderGUID UNIQUEIDENTIFIER DEFAULT NEWID() UNIQUE NONCLUSTERED,
    CustomerID INT,
    OrderDate DATETIME
);
-- 利点:
-- ✓ 順次挿入でページ分割が最小限
-- ✓ GUIDは非クラスター化インデックスとして保持
-- ✓ 断片化を防止

-- 改善策2: NEWSEQUENTIALID() を使用
CREATE TABLE Orders
(
    OrderID UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() PRIMARY KEY CLUSTERED,
    CustomerID INT,
    OrderDate DATETIME
);
-- 利点:
-- ✓ GUIDが順次生成される
-- ✓ ページ分割が大幅に減少
-- 注意点:
-- ⚠ 完全な順次ではなく、部分的に順次
-- ⚠ サーバー再起動で順序がリセット

GUIDを使用する正当な理由

GUIDが適切な場合もあります：

1. 分散システム

   • 複数のサーバーで独立してIDを生成する必要がある
   • マージレプリケーション

2. 外部システムとの統合

   • 外部APIがGUIDを要求
   • グローバルな一意性が必須

3. セキュリティ

   • IDの推測を防ぎたい場合

推奨設計パターン

パターン1: サロゲートキー + GUID（推奨）

CREATE TABLE Products
(
    -- 内部用の順次ID（クラスター化インデックス）
    ProductID BIGINT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    
    -- 外部公開用のGUID（非クラスター化一意インデックス）
    ProductGUID UNIQUEIDENTIFIER DEFAULT NEWID() UNIQUE NONCLUSTERED,
    
    ProductName NVARCHAR(100),
    Price DECIMAL(18,2)
);

-- 内部的には ProductID を使用（高速）
-- 外部APIには ProductGUID を公開（安全）

パターン2: 順次GUID

CREATE TABLE Sessions
(
    SessionID UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() PRIMARY KEY CLUSTERED,
    UserID INT,
    LoginTime DATETIME
);

-- NEWSEQUENTIALID() の制約:
-- ・DEFAULT 制約でのみ使用可能
-- ・INSERT 文で直接呼び出し不可

パターン3: 複合キー（ユーザーID + トランザクションID）

-- ラッチ競合を軽減するための複合キー
CREATE TABLE Transactions
(
    UserID INT,
    TransactionID BIGINT,
    Amount DECIMAL(18,2),
    TransactionDate DATETIME,
    
    -- UserID を先頭にすることでランダム分散
    PRIMARY KEY CLUSTERED (UserID, TransactionID)
);

-- 利点:
-- ✓ 挿入がユーザーごとに分散
-- ✓ ラッチ競合を軽減
-- ✓ ページ分割が各ユーザー範囲内で発生

メンテナンスのベストプラクティス

1. 定期的な監視

-- 定期監視クエリの例
SELECT 
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.avg_fragmentation_in_percent AS FragmentationPercent,
    ips.avg_page_space_used_in_percent AS PageDensity,
    ips.page_count AS PageCount,
    CASE 
        WHEN ips.avg_fragmentation_in_percent > 30 AND ips.page_count > 1000 
        THEN 'REBUILD推奨'
        WHEN ips.avg_fragmentation_in_percent > 10 AND ips.page_count > 1000 
        THEN 'REORGANIZE推奨'
        ELSE 'メンテナンス不要'
    END AS Recommendation
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS ips
INNER JOIN sys.indexes AS i 
    ON ips.object_id = i.object_id
    AND ips.index_id = i.index_id
WHERE ips.page_count > 100  -- 小さいインデックスは除外
ORDER BY ips.avg_fragmentation_in_percent DESC;

2. メンテナンスウィンドウの設定

-- メンテナンスジョブの例（SQL Server Agent）
-- 夜間バッチとして実行

-- ステップ1: 統計更新
EXEC sp_updatestats;

-- ステップ2: 断片化が高いインデックスのみ再構成
DECLARE @TableName NVARCHAR(128);
DECLARE @IndexName NVARCHAR(128);
DECLARE @SQL NVARCHAR(MAX);

DECLARE index_cursor CURSOR FOR
SELECT 
    OBJECT_NAME(ips.object_id),
    i.name
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS ips
INNER JOIN sys.indexes AS i 
    ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10
  AND ips.page_count > 1000;

OPEN index_cursor;
FETCH NEXT FROM index_cursor INTO @TableName, @IndexName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = N'ALTER INDEX ' + QUOTENAME(@IndexName) + 
               N' ON ' + QUOTENAME(@TableName) + N' REORGANIZE;';
    EXEC sp_executesql @SQL;
    
    FETCH NEXT FROM index_cursor INTO @TableName, @IndexName;
END;

CLOSE index_cursor;
DEALLOCATE index_cursor;

3. リソース制限を考慮

-- Azure SQL Database向け: 再開可能メンテナンス
ALTER INDEX IX_LargeTable_Column 
ON dbo.LargeTable
REBUILD WITH (
    ONLINE = ON,
    RESUMABLE = ON,
    MAX_DURATION = 120  -- 2時間でタイムアウト
);

-- 後で再開
ALTER INDEX IX_LargeTable_Column 
ON dbo.LargeTable
RESUME;

まとめ

インデックス断片化について

• ✅ 定期的な監視が重要
• ✅ 断片化の影響はワークロードによって異なる
• ✅ 統計更新だけで改善することも多い
• ⚠️ 無闇にメンテナンスを実行しない（リソースを消費）

GUIDの使用について

• ❌ 非推奨: クラスター化インデックスのキーとしてランダムGUID（NEWID()）を使用
• ⚠️ 注意: GUID使用時は断片化が急速に進行
• ✅ 推奨: 順次ID（IDENTITY）+ 非クラスター化GUID
• ✅ 代替: NEWSEQUENTIALID() の使用（制約あり）

メンテナンス戦略

シナリオ	推奨手法
軽度の断片化（5-10%）	統計更新のみ
中程度の断片化（10-30%）	REORGANIZE
高度の断片化（>30%）	REBUILD（可能ならオンライン）
Azure SQL	REORGANIZE または RESUMABLE REBUILD
小さいインデックス（1000ページ未満）	メンテナンス不要

設計時の推奨事項

1. 主キーの選択

   • 単調増加する値（IDENTITY、SEQUENCE）を優先
   • GUIDが必要な場合は非クラスター化インデックスとして追加

2. Fill Factor の設定

   • 通常は100（デフォルト）のままにする
   • ページ分割が頻繁な場合のみ80-90に設定

3. 監視と改善

   • クエリストアを使用してパフォーマンスを測定
   • A/Bテストでメンテナンスの効果を確認

参考リンク

• Microsoft Learn: インデックスの再構成と再構築
• Microsoft Learn: インデックスアーキテクチャとデザインガイド
• sys.dm_db_index_physical_stats