ITコーディネータのシュウです。

先日、妻の実家のある富山のほうに行く機会があり、そこできれいな夕焼けが見れたので、撮った写真です。車に乗っていた娘が、きれいだよ！というので、慌てて車の中から撮ったものです。
そのときも、本当に昼間は暑い日が続いていて、これは大変だと思っていましたが、その後もさらに暑さが増して、今年は本当に危険な暑さになってますよね。埼玉の熊谷では、41.1℃という、国内最高気温を更新するという状況になりました。クーラーが効いていないところでは、本当に大変かと思います。
皆さん、本当にお体には注意して頂きたいと思います。

＜本日の題材＞
インデックス再構築・再構成（SQL Server）

前回、SQL Serverの動的管理ビューを使用することで、パフォーマンスに影響があるクエリーの確認や不足しているインデックスなどを確認できることをブログで取り上げましたが、今回もその続きということで、取り上げてみたいと思います。
データベースはインデックスをうまく設定することで、抽出処理のレスポンスを効果的に向上させることができますが、当初それほど遅くなかった抽出処理が、いつの間にかとても遅くなっていることに気づくことがあります。その原因の一つに、インデックスの断片化というものがあります。

以前、大手企業の分析用のシステムを見ていたときがありました。日々基幹システム側から日次のデータが分析用のデータベースに送られてくるので、削除・登録などの処理を夜間に行って、分析用データベースを本番環境の前日終わり時点のデータにしていました。毎日、大量のトランザクションデータがあるため、夜間に送られてくるデータもかなりのボリュームがあり、夜間バッチ処理も結構な時間がかかっていました。
そして、分析用に作成したクエリーやプロシージャなどの処理が、作成した当初はそれほど遅くなかったのに、とても遅いと感じるようになることがありました。
原因を調べてみると、インデックスの断片化がかなりの割合で発生しており、ページ密度が大きく低下し、読み取りのページ数もその分多くなって、効率が悪くなっていたことが大きな要因でした。
当時は、SQL Server 2000を使っていたので、DBCC SHOWCONTIG コマンドで断片化を確認し、DBCC DBREINDEXコマンドで再構築を行ったり、 DBCC INDEXDEFRAGコマンドで再構成を行ったりということを、不定期に行っていました。

今は、SQL Server のバージョンも上がり、動的管理ビューを使用することで、インデックスの断片化も確認できます。以下がそのSQLの例になります。

SELECT DB_NAME(s.database_id), OBJECT_NAME(s.object_id), s.index_type_desc, i.name, s.page_count,s.fragment_count, s.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID('dbsdb'), NULL, NULL, NULL, 'LIMITED') s
INNER JOIN sys.indexes i ON i.object_id = s.object_id
  and i.index_id = s.index_id
WHERE s.avg_fragmentation_in_percent > 10
ORDER BY avg_fragmentation_in_percent DESC

ここでの、主な項目の意味としては、
page_count                                                 ページ数
fragment_count                                       断片化しているページ数
avg_fragmentation_in_percent      断片化の割合(%)

上記の結果では、断片化の割合が10%以上のものを抽出していますが、結構高い割合のものがあることが確認できます。

また、抽出SQLのdm_db_index_physical_stats 関数の5番目の引数であるスキャンモードには、LIMITED, SAMPLED, DETAILEDの3種類があります。（上記の例は、LIMITED モード）
それぞれのスキャンモードの意味は以下になります。

LIMITED           
        最も高速なモードで、スキャンするページ数は最小

SAMPLED         
        SAMPLED モードでは、インデックスまたはヒープの全ページの 1% のサンプルに基づく統計情報が返されます。インデックスまたはヒープのページが 10,000 ページに満たない場合は、SAMPLED モードの代わりに DETAILED モードが使用されます。
 
DETAILED       
        DETAILED モードではすべてのページがスキャンされ、すべての統計が返されます。

それで、断片化を解消するには、以下の3つの方法があります。1)     インデックスのオフライン再構築
2)     インデックスのオンライン再構築（Enterprise エディション のみで利用可能）
3)     インデックスの再構成

1)     インデックスのオフラインでの再構築のコマンドは以下になります。

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

インデックスの再構築（オフライン）は、新しい領域へインデックスを再作成し、古いインデックスを削除することで、断片化を解消します。したがって、インデックスの再構築中は、インデックス全体がロックされるので、再構築が完了するまでは、ユーザー操作は待ち状態になり、注意が必要です。

例)
ALTER INDEX PK_T_USER_LOG ON dbo.T_USER_LOG REBUILD

再構築の後で、再度このテーブルについての断片化を見てみます。

SELECT DB_NAME(s.database_id), OBJECT_NAME(s.object_id), s.index_type_desc, i.name, s.page_count, s.fragment_count, s.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID('dbsdb'), OBJECT_ID(N'T_USER_LOG'), NULL, NULL, 'LIMITED') s
INNER JOIN sys.indexes i ON i.object_id = s.object_id
  and i.index_id = s.index_id;

上記の結果から、断片化の割合は 0% になり、断片化が解消されたことがわかります。

2)     インデックスのオンライン再構築のコマンドは以下になります。

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

3)     インデックスの再構成のコマンドは以下になります。

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

例)
先ほどの、断片化の一覧で、断片化が35%程度であった、「T_USER_LOG_V6」について、再構成を行ってみます。最初に、現状の断片化を再度確認してみます。

SELECT DB_NAME(s.database_id), OBJECT_NAME(s.object_id), s.index_type_desc, i.name, s.index_id, s.alloc_unit_type_desc, s.page_count, s.fragment_count, s.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID('dbsdb'), OBJECT_ID(N'T_USER_LOG_V6'), NULL, NULL, 'LIMITED') s
INNER JOIN sys.indexes i ON i.object_id = s.object_id
and i.index_id = s.index_id;

再構成を実施してみます。

ALTER INDEX PK_T_USER_LOG_V6 ON dbo.T_USER_LOG_V6 REORGANIZE;

件数が多少多かったこともあり、少し時間がかかった感じがありました。

断片化を再度確認してみます。先ほどと同じSQLを実行してみます。

先ほど35%程度あったところは、2.4% ほどに変わりました。断片化がかなり解消されたことがわかります。

★インデックスの再構築と再構成の違いについて簡単に記します。
〇再構築
・ひとつのトランザクションでインデックスを完全に再作成。
　そのため、キャンセルすればそのトランザクションはロールバ
　ックされ、断片化した状態に戻る。
・インデックスを作り直すので、テーブルのレコード数が増えな
　い限り、大きく処理時間が変わることはない。
・一時的には、断片化した古いインデックスと再作成した新しい
　インデックスがふたつ存在する状況となり、それらを格納する
　ための容量、及びソート用の領域が必要になる。

〇再構成
・処理単位ごと (処理単位はページ) にトランザクションが分割さ
　れていて、リーフページ間でインデックス行を移動させること
　で行を前に詰めて断片化を解消している。
　キャンセルしても、既に完了しているトランザクションの結果
　は維持される。
・前のページに空きがある場合に後ろのページから前のページへ
　行を移動するという処理であるため、断片化の度合が大きいほ
　ど処理時間が長くなる。
・追加の領域は基本的には必要としない。

実際の運用では、上記のような違いを考慮してどちらを選択するかを決定する必要があると思います。

今日は以上まで

にほんブログ村