SQL Server のインデックスのメンテナンスには
- 再構成 (REORGANIZE / DEFRAG)
- 再構築 (REBUILD)
の 2 種類があります。
2 種類の違いを 2 回の投稿に分けてまとめていきたいと思います。
まずは、前回使用した SQL データを挿入して、DBCC IND でページの情報を取得してみました。
# 一度 Truncate でデータをクリアして再挿入しています。
– 結果 1 –
No |
FILEName |
PagePID |
ObjectName |
name |
PageType |
IndexLevel |
NextPagePID |
PrevPagePID |
1 |
WORK |
3976 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
3977 |
0 |
2 |
WORK |
3977 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
3978 |
3976 |
3 |
WORK |
3978 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
3979 |
3977 |
4 |
WORK |
3979 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
3980 |
3978 |
5 |
WORK |
3980 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
3981 |
3979 |
6 |
WORK |
3981 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
3982 |
3980 |
7 |
WORK |
3982 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
3983 |
3981 |
8 |
WORK |
3983 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
3984 |
3982 |
9 |
WORK |
3984 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
3985 |
3983 |
10 |
WORK |
3985 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
3986 |
3984 |
11 |
WORK |
3986 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
3987 |
3985 |
12 |
WORK |
3987 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
0 |
3986 |
?
この状態で、前回使用したクラスタ化インデックス更新の SQL を実行して断片化を発生させます。
– 結果 2 –
No |
FILEName |
PagePID |
ObjectName |
name |
PageType |
IndexLevel |
NextPagePID |
PrevPagePID |
1 |
WORK |
3976 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
3988 |
0 |
2 |
WORK |
3977 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
4042 |
4040 |
3 |
WORK |
3978 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
4044 |
4043 |
4 |
WORK |
3979 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
4046 |
4045 |
5 |
WORK |
3980 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
3989 |
4046 |
6 |
WORK |
3981 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
4049 |
4048 |
7 |
WORK |
3982 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
4051 |
4049 |
8 |
WORK |
3983 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
4052 |
4050 |
9 |
WORK |
3984 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
4055 |
4053 |
10 |
WORK |
3985 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
3990 |
4055 |
11 |
WORK |
3986 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
4056 |
3990 |
12 |
WORK |
3987 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
0 |
4056 |
13 |
WORK |
3988 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
4040 |
3976 |
14 |
WORK |
3989 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
4047 |
3980 |
15 |
WORK |
3990 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
3986 |
3985 |
16 |
WORK |
4040 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
3977 |
3988 |
17 |
WORK |
4041 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
4043 |
4042 |
18 |
WORK |
4042 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
4041 |
3977 |
19 |
WORK |
4043 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
3978 |
4041 |
20 |
WORK |
4044 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
4045 |
3978 |
21 |
WORK |
4045 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
3979 |
4044 |
22 |
WORK |
4046 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
3980 |
3979 |
23 |
WORK |
4047 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
4048 |
3989 |
24 |
WORK |
4048 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
3981 |
4047 |
25 |
WORK |
4049 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
3982 |
3981 |
26 |
WORK |
4050 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
3983 |
4051 |
27 |
WORK |
4051 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
4050 |
3982 |
28 |
WORK |
4052 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
4054 |
3983 |
29 |
WORK |
4053 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
3984 |
4054 |
30 |
WORK |
4054 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
4053 |
4052 |
31 |
WORK |
4055 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
3985 |
3984 |
32 |
WORK |
4056 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
3987 |
3986 |
?
[PagePID] が 3976 ~3990 , 4040 ~ 4056 が使用されているのが確認できます。
この状態で再構成と再構築を実行して違いを確認したいと思います。
[インデックスの再構成]
以下の SQL を実行してインデックスを再構成してみます。
ALTER INDEX [PK_Tbl1] ON [dbo].[Tbl1] REORGANIZE |
再構成後に DBCC IND を実行して、ページの情報を取得します。
– 結果 3 –
No |
FILEName |
PagePID |
ObjectName |
name |
PageType |
IndexLevel |
Nex
tPagePID |
PrevPagePID |
1 |
WORK |
3976 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
3977 |
0 |
2 |
WORK |
3977 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
3978 |
3976 |
3 |
WORK |
3978 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
3979 |
3977 |
4 |
WORK |
3979 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
3980 |
3978 |
5 |
WORK |
3980 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
3981 |
3979 |
6 |
WORK |
3981 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
3982 |
3980 |
7 |
WORK |
3982 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
3983 |
3981 |
8 |
WORK |
3983 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
3984 |
3982 |
9 |
WORK |
3984 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
3985 |
3983 |
10 |
WORK |
3985 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
3986 |
3984 |
11 |
WORK |
3986 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
3987 |
3985 |
12 |
WORK |
3987 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
0 |
3986 |
?
[PagePID] 3976 ~ 3987 を使用して断片化が解消されています。
それでは再度断片化を発生させて、インデックスの再構築をしてみたいと思います。
断片化を発生させたところ、[PagePID] 3976 ~ 3989 , 4064 ~ 4079 が使用されていました。
[インデックスの再構築]
以下の SQL を実行してインデックスを再構築します。
ALTER INDEX [PK_Tbl1] ON [dbo].[Tbl1] REBUILD |
DBCC IND を実行してページの情報を取得します。
– 結果 4 –
No |
FILEName |
PagePID |
ObjectName |
name |
PageType |
IndexLevel |
NextPagePID |
PrevPagePID |
1 |
WORK |
4080 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
4088 |
0 |
2 |
WORK |
4088 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
4089 |
4080 |
3 |
WORK |
4089 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
4090 |
4088 |
4 |
WORK |
4090 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
4091 |
4089 |
5 |
WORK |
4091 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
4092 |
4090 |
6 |
WORK |
4092 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
4093 |
4091 |
7 |
WORK |
4093 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
4094 |
4092 |
8 |
WORK |
4094 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
4095 |
4093 |
9 |
WORK |
4095 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
4096 |
4094 |
10 |
WORK |
4096 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
4097 |
4095 |
11 |
WORK |
4097 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
4098 |
4096 |
12 |
WORK |
4098 |
Tbl1 |
PK_Tbl1 |
1 |
0 |
0 |
4097 |
?
[PagePID] 4080 ~ 4098 を使用して断片化が解消されています。
?
インデックスの再構成と再構築では、既に割り当てられている領域を使用するか、新規に領域を割り当てるかの違いがあります。
インデックスの再構成では既に割り当てられている領域を利用して断片化の解消を行います。
そのため、断片化が発生した際に割り当てられていた [PagePID] と同様のページが使用されています。
断片化解消のために新たなエクステントが割り当てられることがありません。
それに対してインデックスの再構築は新規のページを使用して断片化の解消が行われます。
インデックスの再作成に近い形で断片化の解消が行われていたはずです。
新規のエクステントに断片化解消後のインデックスを作成し、作成が終了したらそれまで使用していた領域を
空きページとして解放するようです。
再構成も再構築も断片化の解消により、未使用になったページは他のオブジェクトでも使用可能なように解放されますが、
既存ページの利用か新規ページの割り当てかの違いがありますので、処理終了後の未使用領域には差が出てきます。
?
再構成は既存のページ内での並び替えですが、再構築は再作成を行います。
そのため、再構成と再構築では処理終了後に使用している [PagePID] に違いが出てきます。
あまりにも断片化が発生していると再構成の並び替えに時間がかかり、再構築のほうが早い場合もあったりします。
再構成と再構築の違いは領域の再利用だけでなく、エクステントの並び替えでの動作に関しても違いがあります。
これに関しては次回の投稿でまとめたいと思います。