FIXER Tech Blog - Azure

Azure SQL Databaseのインデックス断片化を解消する方法

　本記事はFIXERが提供する「cloud.config Tech Blog」に掲載された「Azure SQL Databaseのインデックス断片化を解消してみた」を再編集したものです。

はじめに

　Azure SQL Database を運用していると、時間の経過とともにデータベースのパフォーマンスが低下することがあります。その主な原因の一つが「インデックスの断片化」です。

　本記事では、インデックス断片化の概念から具体的な解消方法まで、その確認と簡易的な実施を行い、Azure SQL Database の運用作業に注目します。

前提

　データベースは Azure SQL Database を使用し、Azure SQL Database のみをスコープとします。

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

　インデックスの断片化とは、簡単に説明すると、データの変更に伴って物理的なレベルでディスク上にデータを最適に配置ができなくなる状態のことです。

　詳細な説明は省きますが、概念: インデックスの断片化とページ密度やインデックスのアーキテクチャと設計ガイドがインデックスや断片化に関する参考記事となります。

断片化が発生する仕組みとその種類

　インデックスの断片化は、データの挿入・更新・削除が繰り返されることで発生し、それには主に2種類の断片化があります：

1．論理的断片化：インデックスページの論理的な順序と物理的な順序が一致しない状態
2．内部断片化：インデックスページ内の空き領域が増加した状態

　これらの断片化がデータベースのパフォーマンスに影響します。

パフォーマンス影響

　インデックスの断片化が進行するにしたがって、パフォーマンスへの影響が発生します。

　具体的には下記のようなパフォーマンス影響が見込まれます。

・クエリの実行速度低下
・I/O 負荷の増加
・ストレージ使用量の増加

2つの断片化解消方法

　Azure SQL Databaseでは、主に2つの方法でインデックスの断片化を解消できます。

インデックスの再構成 (REORGANIZE)

ALTER INDEX [インデックス名] ON [テーブル名] REORGANIZE;

特徴：
・オンライン操作 (インデックス再構成中に可用性を維持することが可能)
・リソース消費が再構築処理と比べて小さい

インデックスの再構築 (REBUILD)

-- オフライン操作の場合
ALTER INDEX [インデックス名] ON [テーブル名] REBUILD;

-- オンライン操作の場合
ALTER INDEX [インデックス名] ON [テーブル名] REBUILD (ONLINE=ON);

特徴：
・オンラインまたはオンラインの操作選択が可能(オフライン操作は高速である一方、DBに対してロックが発生し、読み取り操作などができなくなります。)
・リソース消費が再構成処理と比べて大きい

　これらの断片化解消方法は後出の断片化率(avg_fragmentation_in_percent)の値に応じて使い分ける必要があります。

　使い分けの一例として下記の表を参考にします。

出典：クエリのパフォーマンスを向上させてリソースの消費を削減するためにインデックスのメンテナンスを最適化する

※ avg_fragmentation_in_percen は後出となりますが、インデックスの断片化率を図る指標です。これらの断片化解消方法は、クエリのパフォーマンス向上とリソース消費のバランスを考慮して、ワークロードごとに実験的に効果を測定し、戦略を決定することが重要です。

インデックス断片化状態の確認

　インデックスの断片化の進行率を示す値として、`断片化率`という値があります。 この値はsys.dm_db_index_physical_statsという DMV を使って以下のように取得できます。※ ${DB_NAME} は必要に応じて値を置き換えてください。

DECLARE @DB_ID int

SET @DB_ID = DB_ID('${DB_NAME}') -- 引数を指定しない場合は、現在のデータベースが対象となる。

SELECT
    OBJCT.name AS table_name,
    IDX.name AS index_name,
    IPS.avg_fragmentation_in_percent, -- インデックスの断片化率。値が大きいほど、断片化が発生している。
    IPS.fragment_count, -- インデックスが物理的に分割されている断片の数。
    IPS.page_count -- ページ数。断片化が進行するとデータを格納するために必要な最小ページ数より大きくなる。
FROM sys.dm_db_index_physical_stats(@DB_ID, null, null, null, null) AS IPS
LEFT OUTER JOIN  sys.objects AS OBJCT
    ON  IPS.object_id = OBJCT.object_id
LEFT OUTER JOIN  sys.indexes AS IDX
    ON  IPS.object_id = IDX.object_id  AND IPS.index_id = IDX.index_id
WHERE IPS.avg_fragmentation_in_percent > 30 -- 暫定的に、インデックス再構築実施判断の基準となる値で断片化率をフィルタリング
ORDER BY IPS.avg_fragmentation_in_percent DESC

　これを実施すると、例として下記のような出力が得られます。

　もし、このような結果が確認できた場合にはインデックスの再構築実施可否の検討が必要です。

　捕捉として、下記のコマンドの通り sys.dm_db_index_physical_stats の出力モードを詳細モードにすることが可能です。

　このモードを指定することで、デフォルトの状態では出力できなかったレコード(例えば、avg_page_space_used_in_percent や record_count)などの値を出力することが可能になります。

　ただ一方で、出力の計算に時間がかかるため、断片化率の確認スコープを対象データベースのすべてのテーブルとするのではなく、対象データベースの一つの任意テーブルとする方が実行時間の関係上、好ましいかもしれません。※ ${DB_NAME}と${TABLE_NAME}は必要に応じて値を置き換えてください。

DECLARE @DB_ID int,
        @OBJECT_ID int

SET @DB_ID = DB_ID('${DB_NAME}') -- 引数を指定しない場合は、現在のデータベースが対象となる。
SET @OBJECT_ID = OBJECT_ID('${TABLE_NAME}')

SELECT
    OBJCT.name AS table_name,
    IDX.name AS index_name,
    IPS.avg_fragmentation_in_percent,
    IPS.fragment_count,
    IPS.page_count,
    IPS.avg_page_space_used_in_percent, -- ページごとのレコード占有率。100%を最大値とし、値が小さいほどI/Oのパフォーマンスが低下する。
    IPS.record_count -- 該当インデックスに存在するレコードの数。
FROM sys.dm_db_index_physical_stats(@DB_ID, @OBJECT_ID, null, null, 'Detailed') AS IPS -- 詳細モード指定
LEFT OUTER JOIN  sys.objects AS OBJCT
    ON  IPS.object_id = OBJCT.object_id
LEFT OUTER JOIN  sys.indexes AS IDX
    ON  IPS.object_id = IDX.object_id  AND IPS.index_id = IDX.index_id
WHERE IPS.avg_fragmentation_in_percent > 30 -- 暫定的に、インデックス再構築実施判断の基準となる値で断片化率をフィルタリング
ORDER BY IPS.avg_fragmentation_in_percent DESC

　以降では、ここまでの情報を基に、オンライン操作による再構築でインデックスの断片化解消を実施してみます。

インデックス断片化解消実施

事前注意事項

　オンライン操作によるインデックスの再構築を実施した場合でもダウンタイムは発生しない一方で、書き込み処理のパフォーマンスがわずかに低下する可能性があることが明記されています。

　インデックスがオンラインで再構築されている間、インデックス付き列のデータのすべての変更で、インデックスの追加コピーを更新する必要があります。 これにより、オンライン再構築中のデータ変更ステートメントのパフォーマンスが、わずかに低下する可能性があります。

引用元：【インデックスを再構築する】

　加えてこちらの考慮もする必要がありそうです。

　インデックス再構築作業を実際に運用中の DB に対して実施する場合は、必要に応じてシステムに影響が発生する可能性がある旨を顧客に連絡する必要がありそうですね。

オンライン操作による再構築でインデックス断片化解消

　今回は、断片化の状態を確認する際に例に挙げた Table01テーブルの PK_Table01 インデックスの断片化解消を試みます。

　実行するコマンドは下記の通りです。

ALTER INDEX PK_Table01 ON Table01 REBUILD (ONLINE=ON);

　このコマンドを実行後に下記のコマンドを実行すると断片化が解消されていることが確認できます。

※ 出力結果は例になります。また、${DB_NAME}は必要に応じて値を置き換えてください。

DECLARE @DB_ID int,
        @OBJECT_ID int
        
SET @DB_ID = DB_ID('${DB_NAME}') -- 引数を指定しない場合は、現在のデータベースが対象となる。
SET @OBJECT_ID = OBJECT_ID('Table01')

SELECT
    OBJCT.name AS table_name,
    IDX.name AS index_name,
    IPS.avg_fragmentation_in_percent,
    IPS.fragment_count,
    IPS.page_count
FROM sys.dm_db_index_physical_stats(@DB_ID, @OBJECT_ID, null, null, null) AS IPS
LEFT OUTER JOIN  sys.objects AS OBJCT
    ON  IPS.object_id = OBJCT.object_id
LEFT OUTER JOIN  sys.indexes AS IDX
    ON  IPS.object_id = IDX.object_id  AND IPS.index_id = IDX.index_id
WHERE IPS.avg_fragmentation_in_percent > 30 -- 暫定的に、インデックス再構築実施判断の基準となる値で断片化率をフィルタリング
    AND IDX.name = 'PK_Table01'
ORDER BY IPS.avg_fragmentation_in_percent DESC

出力結果例：

　これで断片化率を100%から50%に下げることができました。

　ただし、再構築によるインデックスの断片化解消実施後に、断片化率の値が50%であることが確認されました。

　この状態であれば、再度インデックス再構築ができそうに見えますが、これ以上ページを最適化できない状態のため追加のインデックス再構築はできません。

　こちらの記事を確認することでその理由がわかるかと思います。

まとめ

　本記事では、データベースの運用に伴って発生するインデックスの断片化とその解消方法について確認していきました。

　インデックスの断片化はシステムの運用に伴って発生するため、運用作業に断片化状況の監視や定期的な断片化解消作業を運用作業に組み込む必要がありそうですね。

　Azure SQL Database で最適なパフォーマンスを提供するために、適切な監視と定期メンテナスを実施し、システムの安定運用に努めていきましょう！

中島悠太／FIXER
2023年度新卒で入社。エンジニア1年生。
社内での担当はまだ未定。
社外では42Tokyoという場所でエンジニアとしてのスキルを研鑽しています。

■関連サイト

• cloud.config Tech Blogのエントリー
• cloud.config Tech Blog
• FIXER