SQLServerで断片化の状態に応じてIndexを再構築する
SQLServerにおいて、インデックスが断片化してしまうとクエリのパフォーマンスが低下してくる。
インデックスの再構成(Reorganize)や再構築(Rebuild)を行うと断片化が解消される為、定期的にインデックスの再構成(Reorganize)や再構築(Rebuild)をすることでこれを解決することができる。
ただし、やみくもにデータベース内の全てのインデックスを再構成(Reorganize)や再構築(Rebuild)するとデータベースの規模が大きいほど、実行に多くの時間がかかってしまったり、ログファイルが大きくなりすぎてしまう。
そこで、断片化の状態を判断して再構成(Reorganize)または再構築(Rebuild)をする。
まず、インデックスの断片化の状態は sys.dm_db_index_physical_stats を使って調べることができる。
sys.dm_db_index_physical_stats が返す avg_fragmentation_in_percent は論理的な断片化(インデックス内で順序が乱れたページ)の割合を示していて、再構成(Reorganize)または再構築(Rebuild)をするべきかどうかの判断の指標とすることができる。
例えば、以下のようなクエリで再構成(Reorganize)のクエリ、再構築(Rebuild)のクエリ、スキーマ名、テーブル名、インデックス名、インデックスID、オブジェクト内のパーティション番号、断片化の割合、インデックスページまたはデータページの合計数を取得することができる。
avg_fragmentation_in_percent > 10 を指定することで、断片化率が 10 % 以上のインデックスを取得している。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | SELECT 'ALTER INDEX [' + C.name + '] ON [' + D.name + '].[' + B.name + '] REORGANIZE' AS ReorganizeCmd , 'ALTER INDEX [' + C.name + '] ON [' + D.name + '].[' + B.name + '] REBUILD' AS RebuildCmd , D.name AS schemaname , B.name AS table_name , C.name AS index_name , C.index_id , A.partition_number , A.avg_fragmentation_in_percent , A.page_count FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS A INNER JOIN sys.objects AS B ON A.object_id = B.object_id INNER JOIN sys.indexes AS C ON A.object_id = C.object_id AND A.index_id = C.index_id INNER JOIN sys.schemas D ON B.schema_id = D.schema_id WHERE B.type = 'U' AND C.index_id > 0 AND A.page_count > 1000 AND A.avg_fragmentation_in_percent > 10 ORDER BY A.avg_fragmentation_in_percent DESC |
インデックスの断片化の状態がわかったら、それによってインデックスを再構成(Reorganize)するか、または再構築(Rebuild)するか決定する。
例えば、10% 以上 30% 未満であれば再構成(Reorganize)、 30% 以上であれば再構築(Rebuild)するとすれば、上記のクエリで再構成のクエリ、または再構築のクエリを生成してそれぞれ実行する。
