ここ数日は SQL Server の基礎から勉強しなおそうと思い、インデックスの再構築 / 再構成について調べていました。
データの更新を行っているとデータページの断片化が発生し、断片化解消のためにインデックスの再構築 (REBUILD)、
再構成 (REORGANIZE) が必要となります。
ページの連続性が確認できないかなと思い、いろいろと本を眺めていたところ、[DBCC IND] があったなと思い、
少し試してみました。
# SQL Server の投稿に関しては SQL Server 2008 で検証をしていきたいと思います。
まずはテスト用として以下のテーブルを作成しました。
CREATE TABLE [dbo].[Tbl1]( |
GUID と NCHAR で構成された単純なテーブルです。
このテーブルに以下の SQL でデータを挿入して、インデックスを再構築して断片化を解消した状態にします。
— テーブルにデータを挿入 — 主キーが GUID のため、データ挿入後は断片化が発生しているためクラスタ化インデックスを再構築 |
?
この状態で DBCC IND を実行して、ページの連続性を確認してみました。
— DBCC IND の実行結果を格納するテーブル変数の定義 — テーブル変数に DBCC IND の実行結果を格納 — DBCC IND の結果から必要な列を取得 |
?
以下の実行結果が取得できます。
– 結果 1 –
No | FILEName | PagePID | ObjectName | name | PageType | IndexLevel | NextPagePID | PrevPagePID |
1 | WORK | 3328 | Tbl1 | PK_Tbl1 | 1 | 0 | 3336 | 0 |
2 | WORK | 3336 | Tbl1 | PK_Tbl1 | 1 | 0 | 3337 | 3328 |
3 | WORK | 3337 | Tbl1 | PK_Tbl1 | 1 | 0 | 3338 | 3336 |
4 | WORK | 3338 | Tbl1 | PK_Tbl1 | 1 | 0 | 3339 | 3337 |
5 | WORK | 3339 | Tbl1 | PK_Tbl1 | 1 | 0 | 3340 | 3338 |
6 | WORK | 3340 | Tbl1 | PK_Tbl1 | 1 | 0 | 3341 | 3339 |
7 | WORK | 3341 | Tbl1 | PK_Tbl1 | 1 | 0 | 3342 | 3340 |
8 | WORK | 3342 | Tbl1 | PK_Tbl1 | 1 | 0 | 3343 | 3341 |
9 | WORK | 3343 | Tbl1 | PK_Tbl1 | 1 | 0 | 3344 | 3342 |
10 | WORK | 3344 | Tbl1 | PK_Tbl1 | 1 | 0 | 3345 | 3343 |
11 | WORK | 3345 | Tbl1 | PK_Tbl1 | 1 | 0 | 3346 | 3344 |
12 | WORK | 3346 | Tbl1 | PK_Tbl1 | 1 | 0 | 0 | 3345 |
?
ここで確認したい内容が [NextPagePID] と [PrepPagePID] です。
この値が対象のページに対しての前後のページ ID となります。
クラスタ化インデックスの再構築後の状態を取得していますので、データの連続性が保たれています。
この状態でクラスタ化インデックスを更新して断片化を発生させます。
UPDATE [dbo].[Tbl1] SET [Col1] = NEWID() |
再度、DBCC IND を実行してページの情報を取得します。
– 結果 2 –
No | FILEName | PagePID | ObjectName | name | PageType | IndexLevel | NextPagePID | PrevPagePID |
1 | WORK | 3328 | Tbl1 | PK_Tbl1 | 1 | 0 | 3330 | 0 |
2 | WORK | 3329 | Tbl1 | PK_Tbl1 | 1 | 0 | 3332 | 3331 |
3 | WORK | 3330 | Tbl1 | PK_Tbl1 | 1 | 0 | 3331 | 3328 |
4 | WORK | 3331 | Tbl1 | PK_Tbl1 | 1 | 0 | 3329 | 3330 |
5 | WORK | 3332 | Tbl1 | PK_Tbl1 | 1 | 0 | 3336 | 3329 |
6 | WORK | 3333 | Tbl1 | PK_Tbl1 | 1 | 0 | 3337 | 3336 |
7 | WORK | 3334 | Tbl1 | PK_Tbl1 | 1 | 0 | 3335 | 3337 |
8 | WORK | 3335 | Tbl1 | PK_Tbl1 | 1 | 0 | 3338 | 3334 |
9 | WORK | 3336 | Tbl1 | PK_Tbl1 | 1 | 0 | 3333 | 3332 |
10 | WORK | 3337 | Tbl1 | PK_Tbl1 | 1 | 0 | 3334 | 3333 |
11 | WORK | 3338 | Tbl1 | PK_Tbl1 | 1 | 0 | 3347 | 3335 |
12 | WORK | 3339 | Tbl1 | PK_Tbl1 | 1 | 0 | 3441 | 3347 |
13 | WORK | 3340 | Tbl1 | PK_Tbl1 | 1 | 0 | 3443 | 3442 |
14 | WORK | 3341 | Tbl1 | PK_Tbl1 | 1 | 0 | 3444 | 3443 |
15 | WORK | 3342 | Tbl1 | PK_Tbl1 | 1 | 0 | 3445 | 3444 |
16 | WORK | 3343 | Tbl1 | PK_Tbl1 | 1 | 0 | 3446 | 3445 |
17 | WORK | 3344 | Tbl1 | PK_Tbl1 | 1 | 0 | 3456 | 3447 |
18 | WORK | 3345 | Tbl1 | PK_Tbl1 | 1 | 0 | 3457 | 3348 |
19 | WORK | 3346 | Tbl1 | PK_Tbl1 | 1 | 0 | 0 | 3458 |
20 | WORK | 3347 | Tbl1 | PK_Tbl1 | 1 | 0 | 3339 | 3338 |
21 | WORK | 3348 | Tbl1 | PK_Tbl1 | 1 | 0 | 3345 | 3456 |
22 | WORK | 3440 | Tbl1 | PK_Tbl1 | 1 | 0 | 3442 | 3441 |
23 | WORK | 3441 | Tbl1 | PK_Tbl1 | 1 | 0 | 3440 | 3339 |
24 | WORK | 3442 | Tbl1 | PK_Tbl1 | 1 | 0 | 3340 | 3440 |
25 | WORK | 3443 | Tbl1 | PK_Tbl1 | 1 | 0 | 3341 | 3340 |
26 | WORK | 3444 | Tbl1 | PK_Tbl1 | 1 | 0 | 3342 | 3341 |
27 | WORK | 3445 | Tbl1 | PK_Tbl1 | 1 | 0 | 3343 | 3342 |
28 | WORK | 3446 | Tbl1 | PK_Tbl1 | 1 | 0 | 3447 | 3343 |
29 | WORK | 3447 | Tbl1 | PK_Tbl1 | 1 | 0 | 3344 | 3446 |
30 | WORK | 3456 | Tbl1 | PK_Tbl1 | 1 | 0 | 3348 | 3344 |
31 | WORK | 3457 | Tbl1 | PK_Tbl1 | 1 | 0 | 3459 | 3345 |
32 | WORK | 3458 | Tbl1 | PK_Tbl1 | 1 | 0 | 3346 | 3459 |
33 | WORK | 3459 | Tbl1 | PK_Tbl1 | 1 | 0 | 3458 | 3457 |
断片化の発生により、ページ数が倍以上に増えています。
PagePID 3338 のレコードを見てみると、次のページが [3347] となっています。
他にも前後のページが連続していないレコードがちらほらと見受けられます。
この状態が断片化が発生している状態ですね。
ALTER INDEX ~ REBUILD を実行して、ページを確認してみます。
– 結果 3 –
No | FILEName | PagePID | ObjectName | name | PageType | IndexLevel | NextPagePID | PrevPagePID |
1 | WORK | 3464 | Tbl1 | PK_Tbl1 | 1 | 0 | 3472 | 0 |
2 | WORK | 3472 | Tbl1 | PK_Tbl1 | 1 | 0 | 3473 | 3464 |
3 | WORK | 3473 | Tbl1 | PK_Tbl1 | 1 | 0 | 3474 | 3472 |
4 | WORK | 3474 | Tbl1 | PK_Tbl1 | 1 | 0 | 3475 | 3473 |
5 | WORK | 3475 | Tbl1 | PK_Tbl1 | 1 | 0 | 3476 | 3474 |
6 | WORK | 3476 | Tbl1 | PK_Tbl1 | 1 | 0 | 3477 | 3475 |
7 | WORK | 3477 | Tbl1 | PK_Tbl1 | 1 | 0 | 3478 | 3476 |
8 | WORK | 3478 | Tbl1 | PK_Tbl1 | 1 | 0 | 3479 | 3477 |
9 | WORK | 3479 | Tbl1 | PK_Tbl1 | 1 | 0 | 3480 | 3478 |
10 | WORK | 3480 | Tbl1 | PK_Tbl1 | 1 | 0 | 3481 | 3479 |
11 | WORK | 3481 | Tbl1 | PK_Tbl1 | 1 | 0 | 3482 | 3480 |
12 | WORK | 3482 | Tbl1 | PK_Tbl1 | 1 | 0 | 0 | 3481 |
結果 1 と同じレコード数となり、ページ番号も連続しています。
断片化が解消されているのがページ数とページの連続性から確認ができますね。
DBCC IND を使用すると、インデックスの再構築と再構成の動作の違いを確認することも可能です。
こちらについては次回投稿したいと思います。
# 結果 2 と結果 3 ではPagePID が変わっているのですが、これがインデックスの再構築と再構成の違いでもあります。