SQL Serverで断片化率に応じてインデックスの再構築・再構成をする | cloud.config Tech Blog

こんにちは、あおいです。
相場は必ず呼吸をする。息の荒い相場には気を付けろ。

さて、DBのパフォーマンスチューニングでインデックスの作成は非常に重要です。
前回の記事では、実際にインデックスを作成して検索を高速化する方法について紹介させていただきました。
SQL Serverのインデックスを作成して検索を高速化する | cloud.config Tech Blog

そこで、今回はSQL Serverで断片化率に応じてインデックスの再構築をする方法について紹介したいと思います。

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

SQL Serverのインデックスは、更新および削除操作によっては断片化が発生します。
断片化によって、CPU負荷増やクエリ実行時間の遅延などの性能低下を引き起こす傾向にあります。

断片化情報を抽出する

「sys.dm_db_index_physical_stas」を実行し、インデックスの断片化情報を抽出します。

SELECT 
    SCM.name AS schema_name,
    OBJCT.name AS table_name,
    IDX.name AS index_name,
    IPS.index_level,
    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 
LEFT OUTER JOIN  sys.schemas AS SCM 
    ON  OBJCT.schema_id = SCM.schema_id 

WHERE OBJCT.type = 'U'
    AND   IDX.index_id > 0 

-- 断片化率が30%以上の情報を抽出する
AND   IPS.avg_fragmentation_in_percent > 30 
ORDER BY IPS.avg_fragmentation_in_percent DESC

SQL実行後、以下のような断片化情報が得られます。

index_level

０がリーフレベル、１が中間レベル、２がルートです。

avg_fragmentation_in_percent

各レベルにおける断片化率(%)です。数値が高いほど断片化が激しいことを示します。

fragment_count

断片化しているページ数

page_count

各レベルにおけるページ数です。断片化が激しいと、本来必要なページ数より多くなります。

avg_page_space_used_in_percent

各レベルにおいて、各ページにどれだけレコード数が格納されているかを示します。100%に近いほど読みとり性能が良くなります。

断片化の解消方法

インデックスの断片化を解消する方法で「再構成」と「再構築」があります。
Microsoftのドキュメントでは以下のガイドラインが紹介されています。

avg_fragmentation_in_percent値	断片化解消ステートメント
５～３０％	ALTER INDEX REORGANIZE
３０％以上	ALTER INDEX REBUILD(WITH ON = ONLINE)

出典：インデックスの再構築と再構成 | Microsoft Docs

つまり、断片化率が一定の閾値を超えた場合は再構築（REBUILD）、そうでない場合は再構成（REORGANIZE）を推奨する、というものです。

高頻度の更新処理が行われることで、インデックスの断片化率は上昇し、システムの処理速度が落ちてしまいます。
定期的な再構築・再構成の実行(お掃除)をしましょう。

インデックス再構築・再構成の基本構文

再構築（オフライン）

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

再構築（オンライン）

インデックス全体がロック状態になるので、ユーザー操作は待ち状態になります。
オプションでWITH (ONLINE = ON )を追加することで、オンライン操作が可能になります。

ALTER INDEX ［インデックス名］ON ［テーブル名］REBUILD WITH（ONLINE = ON）

再構成

ALTER INDEX ［インデックス名］ON［テーブル名］REORGANIZE

断片化率が1%以上30%以下の場合、インデックスの再構成をする

DECLARE @Command VARCHAR(255)
DECLARE cur CURSOR LOCAL FOR
-- インデックス再構成コマンドの生成
SELECT 'ALTER INDEX ' + '[' + IDX.name + ']' + ' ON [' + SCM.name + '].[' + OBJCT.name + '] REORGANIZE' AS command
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 
LEFT OUTER JOIN  sys.schemas AS SCM
ON  OBJCT.schema_id = SCM.schema_id 
WHERE OBJCT.type = 'U'
AND   IDX.index_id > 0 
-- 断片化率が1%以上30%以下のものを抽出する
AND   IPS.avg_fragmentation_in_percent BETWEEN 1 AND 30

OPEN cur

FETCH NEXT FROM cur INTO @Command
WHILE @@FETCH_STATUS = 0

BEGIN
  -- インデックスの再構成を実行する
  EXEC(@Command)
  FETCH NEXT FROM cur INTO @Command
END

CLOSE cur
DEALLOCATE cur

断片化率が30%以上の場合、インデックスの再構築をする

DECLARE @Command VARCHAR(255)
DECLARE cur CURSOR LOCAL FOR
-- インデックスと断片化率の一覧を取得する
SELECT 'ALTER INDEX ' + '[' + IDX.name + ']' + ' ON [' + SCM.name + '].[' + OBJCT.name + '] REBUILD WITH (ONLINE = ON)' AS Command
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 
LEFT OUTER JOIN  sys.schemas AS SCM
	ON  OBJCT.schema_id = SCM.schema_id 
WHERE OBJCT.type = 'U'
AND   IDX.index_id > 0 

-- 断片化率が30%以上のものを抽出する
AND   IPS.avg_fragmentation_in_percent >= 30
ORDER BY IPS.avg_fragmentation_in_percent DESC

OPEN cur

FETCH NEXT FROM cur INTO @Command
WHILE @@FETCH_STATUS = 0

BEGIN
  -- インデックスの再構築を実行する
  EXEC(@Command)
  FETCH NEXT FROM cur INTO @Command
END

CLOSE cur
DEALLOCATE cur

今回はSQL Serverで断片化率に応じてインデックスの再構築をする方法について紹介させていただきました。
最初はインデックスの知識は皆無で何も分からない状態でしたが、インデックスの内部構造やインデックスを作成時の観点など調査と実践を繰り返すことで少し分かるようになりました。本記事が少しでも読者の皆様のお役に立てれば幸いです。