本記事はFIXERが提供する「cloud.config Tech Blog」に掲載された「SQL Serverで断片化率に応じてインデックスの再構築・再構成をする」を再編集したものです。
こんにちは、あおいです。
相場は必ず呼吸をする。息の荒い相場には気を付けろ。
さて、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
0がリーフレベル、1が中間レベル、2がルートです。
avg_fragmentation_in_percent
各レベルにおける断片化率（％）です。数値が高いほど断片化が激しいことを示します。
fragment_count
断片化しているページ数
page_count
各レベルにおけるページ数です。断片化が激しいと、本来必要なページ数より多くなります。
avg_page_space_used_in_percent
各レベルにおいて、各ページにどれだけレコード数が格納されているかを示します。100%に近いほど読みとり性能が良くなります。
断片化の解消方法
インデックスの断片化を解消する方法で「再構成」と「再構築」があります。
Microsoftのドキュメントでは以下のガイドラインが紹介されています。
| avg_fragmentation_in_percent値 | 断片化解消ステートメント |
| 5～30％ | ALTER INDEX REORGANIZE |
| 30％以上 | ALTER INDEX REBUILD(WITH ON = ONLINE) |
つまり、断片化率が一定の閾値を超えた場合は再構築（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で断片化率に応じてインデックスの再構築をする方法について紹介させていただきました。
最初はインデックスの知識は皆無で何も分からない状態でしたが、インデックスの内部構造やインデックスを作成時の観点など調査と実践を繰り返すことで少し分かるようになりました。本記事が少しでも読者の皆様のお役に立てれば幸いです。
参考記事
クエリのパフォーマンスを向上させてリソースの消費を削減するためにインデックスのメンテナンスを最適化する | Microsoft Docs
SQL ServerとAzure SQLのインデックスアーキテクチャとデザインガイド | Microsoft Docs
ページとエクステントのアーキテクチャ | Microsoft Docs
ALTER INDEX（Transact-SQL）| Microsoft Docs
インデックス再構築と再構成の違い | Microsoft Docs
SQL Serverにおけるインデックスの再構成と再構築の性能比較 | ZOZO Tech Blog
あおい／FIXER
「初心者の方にも分かりやすく」をモットーにBlog執筆。米株取引とK-POPが好きです。普段はC#とSQL Serverを触ってます。
