SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

DBCC IND で確認するインデックスの再構成と再構築の違い その 1

leave a comment

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] に違いが出てきます。
あまりにも断片化が発生していると再構成の並び替えに時間がかかり、再構築のほうが早い場合もあったりします。

再構成と再構築の違いは領域の再利用だけでなく、エクステントの並び替えでの動作に関しても違いがあります。
これに関しては次回の投稿でまとめたいと思います。

Share

Written by Masayuki.Ozawa

11月 10th, 2009 at 2:12 pm

Posted in SQL Server

Leave a Reply