SQL Serverのインデックスは、更新および削除操作によっては断片化が発生します。ですので、定期的にインデックスの再構築・再構成が必要になります。
今回はインデックスの断片化率を確認して、インデックスの再構築・再構成を行う方法をご紹介させていただきます。
断片化率の確認方法
Microsoft SQL Server Management Studioから下記クエリを実行することで、各インデックスの断片化率を確認することができます。
今回作成したクエリの場合、データベースで実行する必要があります。主要なカラムの出力結果の意味は下記に記載しております。
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(), default, default, default, '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
断片化率(%)です。数値が高いほど断片化が激しいことを示します。
fragment_count
断片化しているページ数です。
page_count
各レベルにおけるページ数です。断片化が激しいと、本来必要なページ数より多くなります。
avg_page_space_used_in_percent
各レベルにおいて、各ページにどれだけレコード数が格納されているかを示します。100%に近いほど読みとり性能が良くなります。
詳細についてはMicrosoftの公式サイトをご確認ください。
パフォーマンス向上とリソース使用量削減のためのインデックスの最適なメンテナンス - SQL Server | Microsoft Learn
実行手順
1.Microsoft SQL Server Management Studioを起動します
2.該当のインスタンスにログインして、上記クエリを参考にしてクエリを実行します
インデックス再構築・再構成方法
Microsoft SQL Server Management Studioから下記クエリを実行することで、断片化が発生しているインデックスだけ抽出することができます。
断片化率が30%超えている場合は再構築を実施した方が良いです。
断片化率が1%以上30%以下のインデックスを抽出する
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(), default, default, default, 'SAMPLED') AS ips
INNER JOIN sys.indexes AS i
ON ips.object_id = i.object_id
AND
ips.index_id = i.index_id
-- 断片化率が1%以上30%以下のものを抽出する
AND IPS.avg_fragmentation_in_percent BETWEEN 1 AND 30
ORDER BY page_count DESC;断片化率が30%以上のインデックスを抽出する
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(), default, default, default, 'SAMPLED') AS ips
INNER JOIN sys.indexes AS i
ON ips.object_id = i.object_id
AND
ips.index_id = i.index_id
-- 断片化率が30%以上のインデックスを抽出する
AND IPS.avg_fragmentation_in_percent >= 30
ORDER BY page_count DESC;再構築（オフライン）
ALTER INDEX ［インデックス名］ON ［テーブル名］REBUILD再構築（オンライン）
インデックス全体がロック状態になるので、ユーザー操作は待ち状態になります。
オプションでWITH (ONLINE = ON )を追加することで、オンライン操作が可能になります。
ALTER INDEX ［インデックス名］ON ［テーブル名］REBUILD WITH（ONLINE = ON）再構成
ALTER INDEX ［インデックス名］ON［テーブル名］REORGANIZE実行手順
1.Microsoft SQL Server Management Studioを起動します
2.該当のインスタンスにログインして上記クエリを参考にしてクエリを実行します
※キャプチャは断片化率が1%以上30%以下の場合
3.上記クエリ(再構成・再構築)を参考に、断片化が発生しているインデックスに対して再構成 or 再構築を実行します。
さいごに
今回作成したクエリでは、断片化が発生しているインデックスを確認して、手動でインデックスの再構成・再構築を実行する手順になっています。
ですが、ストアドプロシージャなどで自動的にインデックスの断片化率を確認してインデックスの再構成・再構築まで実行することも可能です。
