前回は再構成と再構築でページの再利用方法が異なるということを DBCC IND で確認しました。
今回はエクステントの並び替えの違いについて確認をしてみたいと思います。
# 最初にスクリプトを書いてから何日か経ちましたので、今回の投稿には使用するスクリプトを記載してみました。
[前段]
– ページ –
SQL Server のデータはページに格納されていきます。
1 ページは 8KB で構成されており、ひとつのページに格納できるデータは 8KB となります。
# それ以上格納したい場合は BLOB を使用しますが今回は通常のデータを対象とします。
1 ページに格納できるデータは同一のテーブルのデータになります。
そのため、1 ページに複数のテーブルのデータが格納されるということはありません。
– エクステント –
ページはエクステントという単位で管理されています。
エクステントは 8 ページで構成され、ページに空きが無くなった場合は、ページ単位で領域を増やすのではなく、
エクステント単位 (8KB * 8 = 64 KB)で増やしていきます。
– 単一エクステントと混合エクステント –
エクステントには以下の 2 種類があります。
- 単一エクステント
- 混合エクステント
8 ページで構成されるエクステントですが、使用されているページが一つのオブジェクトで構成される場合と、
複数のオブジェクトが含まれた形で構成される場合があります。
前者を単一エクステント、後者を混合エクステントと呼びます。
最初は単一エクステントで構成され、8 ページまで拡張されたら、混合エクステントに切り替わります。
[本題]?
再構成と再構築ではページの並び替えだけでなく、エクステントの並び替えにも差があります。
この違いについて DBCC IND を使用して確認していきたいと思います。
この確認は単一エクステントになるようにして確認をした方がわかりやすいので、トレースフラグ [1118] を設定して
確認をしています。
# このトレースフラグは混合エクステントを使用しないようにするためのトレースフラグです。
今回はエクステントの並び替えの検証をしたいので検証用のテーブルは 2 つ作成します。
# 1 レコードで 1 ページ使うように nchar を 4000 で設定しています。
– テーブルの作成スクリプト –
DROP TABLE [dbo].[Tbl1] CREATE TABLE [dbo].[Tbl1]( CREATE TABLE [dbo].[Tbl2]( |
?
テーブルの作成が完了したらデータを挿入します。
– テーブルの挿入スクリプト –
TRUNCATE TABLE [dbo].[Tbl1] DECLARE @i int = 1 |
?
この状態で DBCC IND を実行してページの情報を確認してみます。
– DBCC IND のスクリプト –
— DBCC IND の実行結果を格納するテーブル変数の定義 — テーブル変数に DBCC IND の実行結果を格納 — DBCC IND の結果から必要な列を取得 |
?
– 結果 1 –
No | FILEName | PagePID | ObjectName | name | PageType | IndexLevel | NextPagePID | PrevPagePID |
1 | WORK | 848 | Tbl1 | PK_Tbl1 | 1 | 0 | 871 | 868 |
2 | WORK | 850 | Tbl1 | PK_Tbl1 | 1 | 0 | 851 | 871 |
3 | WORK | 851 | Tbl1 | PK_Tbl1 | 1 | 0 | 880 | 850 |
4 | WORK | 852 | Tbl1 | PK_Tbl1 | 1 | 0 | 869 | 866 |
5 | WORK | 853 | Tbl1 | PK_Tbl1 | 1 | 0 | 868 | 0 |
6 | WORK | 854 | Tbl1 | PK_Tbl1 | 1 | 0 | 865 | 880 |
7 | WORK | 855 | Tbl1 | PK_Tbl1 | 1 | 0 | 870 | 867 |
8 | WORK | 856 | Tbl2 | PK_Tbl2 | 1 | 0 | 873 | 0 |
9 | WORK | 858 | Tbl2 | PK_Tbl2 | 1 | 0 | 879 | 861 |
10 | WORK | 859 | Tbl2 | PK_Tbl2 | 1 | 0 | 863 | 874 |
11 | WORK | 860 | Tbl2 | PK_Tbl2 | 1 | 0 | 877 | 878 |
12 | WORK | 861 | Tbl2 | PK_Tbl2 | 1 | 0 | 858 | 873 |
13 | WORK | 862 | Tbl2 | PK_Tbl2 | 1 | 0 | 874 | 872 |
14 | WORK | 863 | Tbl2 | PK_Tbl2 | 1 | 0 | 876 | 859 |
15 | WORK | 864 | Tbl1 | PK_Tbl1 | 1 | 0 | 866 | 865 |
16 | WORK | 865 | Tbl1 | PK_Tbl1 | 1 | 0 | 864 | 854 |
17 | WORK | 866 | Tbl1 | PK_Tbl1 | 1 | 0 | 852 | 864 |
18 | WORK | 867 | Tbl1 | PK_Tbl1 | 1 | 0 | 855 | 869 |
19 | WORK | 868 | Tbl1 | PK_Tbl1 | 1 | 0 | 848 | 853 |
20 | WORK | 869 | Tbl1 | PK_Tbl1 | 1 | 0 | 867 | 852 |
21 | WORK | 870 | Tbl1 | PK_Tbl1 | 1 | 0 | 0 | 855 |
22 | WORK | 871 | Tbl1 | PK_Tbl1 | 1 | 0 | 850 | 848 |
23 | WORK | 872 | Tbl2 | PK_Tbl2 | 1 | 0 | 862 | 888 |
24 | WORK | 873 | Tbl2 | PK_Tbl2 | 1 | 0 | 861 | 856 |
25 | WORK | 874 | Tbl2 | PK_Tbl2 | 1 | 0 | 859 | 862 |
26 | WORK | 875 | Tbl2 | PK_Tbl2 | 1 | 0 | 878 | 876 |
27 | WORK | 876 | Tbl2 | PK_Tbl2 | 1 | 0 | 875 | 863 |
28 | WORK | 877 | Tbl2 | PK_Tbl2 | 1 | 0 | 0 | 860 |
29 | WORK | 878 | Tbl2 | PK_Tbl2 | 1 | 0 | 860 | 875 |
30 | WORK | 879 | Tbl2 | PK_Tbl2 | 1 | 0 | 888 | 858 |
31 | WORK | 880 | Tbl1 | PK_Tbl1 | 1 | 0 | 854 | 851 |
32 | WORK | 888 | Tbl2 | PK_Tbl2 | 1 | 0 | 872 | 879 |
?
Tbl1 が使用しているページを太字にしています。
今回は必ず単一エクステントが使用されるようにしていますので、交互にデータを挿入してもページがエクステント内では
連続した形で格納されます。
1~7 / 8 ~ 14 / 15 ~ 22 / 23 ~ 30 / 31 / 32 の 6 エクステントで構成がされています。
エクステント内のページは連続していますが、交互にデータを挿入しているのでエクステントが連続していません。
エクステントの断片化が発生している状態ですね。
ではこの状態で、インデックスの再構成 (REORGANIZE / INDEXDEFRAG) を実行してみます。
– インデックスの再構成のスクリプト –
ALTER INDEX [PK_Tbl1] ON [dbo].[Tbl1] REORGANIZE |
?
再構成が終了したら DBCC IND を実行してページの状態を確認します。
– 結果 2 –
No | FILEName | PagePID | ObjectName | name | PageType | IndexLevel | NextPagePID | PrevPagePID |
1 | WORK | 848 | Tbl1 | PK_Tbl1 | 1 | 0 | 850 | 0 |
2 | WORK | 850 | Tbl1 | PK_Tbl1 | 1 | 0 | 851 | 848 |
3 | WORK | 851 | Tbl1 | PK_Tbl1 | 1 | 0 | 852 | 850 |
4 | WORK | 852 | Tbl1 | PK_Tbl1 | 1 | 0 | 853 | 851 |
5 | WORK | 853 | Tbl1 | PK_Tbl1 | 1 | 0 | 854 | 852 |
6 | WORK | 854 | Tbl1 | PK_Tbl1 | 1 | 0 | 855 | 853 |
7 | WORK | 855 | Tbl1 | PK_Tbl1 | 1 | 0 | 864 | 854 |
8 | WORK | 856 | Tbl2 | PK_Tbl2 | 1 | 0 | 858 | 0 |
9 | WORK | 858 | Tbl2 | PK_Tbl2 | 1 | 0 | 859 | 856 |
10 | WORK | 859 | Tbl2 | PK_Tbl2 | 1 | 0 | 860 | 858 |
11 | WORK | 860 | Tbl2 | PK_Tbl2 | 1 | 0 | 861 | 859 |
12 | WORK | 861 | Tbl2 | PK_Tbl2 | 1 | 0 | 862 | 860 |
13 | WORK | 862 | Tbl2 | PK_Tbl2 | 1 | 0 | 863 | 861 |
14 | WORK | 863 | Tbl2 | PK_Tbl2 | 1 | 0 | 872 | 862 |
15 | WORK | 864 | Tbl1 | PK_Tbl1 | 1 | 0 | 865 | 855 |
16 | WORK | 865 | Tbl1 | PK_Tbl1 | 1 | 0 | 866 | 864 |
17 | WORK | 866 | Tbl1 | PK_Tbl1 | 1 | 0 | 867 | 865 |
18 | WORK | 867 | Tbl1 | PK_Tbl1 | 1 | 0 | 868 | 866 |
19 | WORK | 868 | Tbl1 | PK_Tbl1 | 1 | 0 | 869 | 867 |
20 | WORK | 869 | Tbl1 | PK_Tbl1 | 1 | 0 | 870 | 868 |
21 | WORK | 870 | Tbl1 | PK_Tbl1 | 1 | 0 | 871 | 869 |
22 | WORK | 871 | Tbl1 | PK_Tbl1 | 1 | 0 | 880 | 870 |
23 | WORK | 872 | Tbl2 | PK_Tbl2 | 1 | 0 | 873 | 863 |
24 | WORK | 873 | Tbl2 | PK_Tbl2 | 1 | 0 | 874 | 872 |
25 | WORK | 874 | Tbl2 | PK_Tbl2 | 1 | 0 | 875 | 873 |
26 | WORK | 875 | Tbl2 | PK_Tbl2 | 1 | 0 | 876 | 874 |
27 | WORK | 876 | Tbl2 | PK_Tbl2 | 1 | 0 | 877 | 875 |
28 | WORK | 877 | Tbl2 | PK_Tbl2 | 1 | 0 | 878 | 876 |
29 | WORK | 878 | Tbl2 | PK_Tbl2 | 1 | 0 | 879 | 877 |
30 | WORK | 879 | Tbl2 | PK_Tbl2 | 1 | 0 | 888 | 878 |
31 | WORK | 880 | Tbl1 | PK_Tbl1 | 1 | 0 | 0 | 871 |
32 | WORK | 888 | Tbl2 | PK_Tbl2 | 1 | 0 | 0 | 879 |
?
ページの配置状況は何も変わっていないですね。
では次に、再構築 (REBUILD) を実行してみます。
– インデックスの再構築 –
ALTER INDEX [PK_Tbl1] ON [dbo].[Tbl1] REBUILD |
?
再構築が終了したら DBCC IND を実行します。
– 結果 3 –
No | FILEName | PagePID | ObjectName | name | PageType | IndexLevel | NextPagePID | PrevPagePID |
1 | WORK | 896 | Tbl1 | PK_Tbl1 | 1 | 0 | 904 | 0 |
2 | WORK | 904 | Tbl1 | PK_Tbl1 | 1 | 0 | 905 | 896 |
3 | WORK | 905 | Tbl1 | PK_Tbl1 | 1 | 0 | 906 | 904 |
4 | WORK | 906 | Tbl1 | PK_Tbl1 | 1 | 0 | 907 | 905 |
5 | WORK | 907 | Tbl1 | PK_Tbl1 | 1 | 0 | 908 | 906 |
6 | WORK | 908 | Tbl1 | PK_Tbl1 | 1 | 0 | 909 | 907 |
7 | WORK | 909 | Tbl1 | PK_Tbl1 | 1 | 0 | 910 | 908 |
8 | WORK | 910 | Tbl1 | PK_Tbl1 | 1 | 0 | 911 | 909 |
9 | WORK | 911 | Tbl1 | PK_Tbl1 | 1 | 0 | 912 | 910 |
10 | WORK | 912 | Tbl1 | PK_Tbl1 | 1 | 0 | 913 | 911 |
11 | WORK | 913 | Tbl1 | PK_Tbl1 | 1 | 0 | 914 | 912 |
12 | WORK | 914 | Tbl1 | PK_Tbl1 | 1 | 0 | 915 | 913 |
13 | WORK | 915 | Tbl1 | PK_Tbl1 | 1 | 0 | 916 | 914 |
14 | WORK | 916 | Tbl1 | PK_Tbl1 | 1 | 0 | 917 | 915 |
15 | WORK | 917 | Tbl1 | PK_Tbl1 | 1 | 0 | 918 | 916 |
16 | WORK | 918 | Tbl1 | PK_Tbl1 | 1 | 0 | 0 | 917 |
17 | WORK | 944 | Tbl2 | PK_Tbl2 | 1 | 0 | 952 | 0 |
18 | WORK | 952 | Tbl2 | PK_Tbl2 | 1 | 0 | 953 | 944 |
19 | WORK | 953 | Tbl2 | PK_Tbl2 | 1 | 0 | 954 | 952 |
20 | WORK | 954 | Tbl2 | PK_Tbl2 | 1 | 0 | 955 | 953 |
21 | WORK | 955 | Tbl2 | PK_Tbl2 | 1 | 0 | 956 | 954 |
22 | WORK | 956 | Tbl2 | PK_Tbl2 | 1 | 0 | 957 | 955 |
23 | WORK | 957 | Tbl2 | PK_Tbl2 | 1 | 0 | 958 | 956 |
24 | WORK | 958 | Tbl2 | PK_Tbl2 | 1 | 0 | 959 | 957 |
25 | WORK | 959 | Tbl2 | PK_Tbl2 | 1 | 0 | 960 | 958 |
26 | WORK | 960 | Tbl2 | PK_Tbl2 | 1 | 0 | 961 | 959 |
27 | WORK | 961 | Tbl2 | PK_Tbl2 | 1 | 0 | 962 | 960 |
28 | WORK | 962 | Tbl2 | PK_Tbl2 | 1 | 0 | 963 | 961 |
29 | WORK | 963 | Tbl2 | PK_Tbl2 | 1 | 0 | 964 | 962 |
30 | WORK | 964 | Tbl2 | PK_Tbl2 | 1 | 0 | 965 | 963 |
31 | WORK | 965 | Tbl2 | PK_Tbl2 | 1 | 0 | 966 | 964 |
32 | WORK | 966 | Tbl2 | PK_Tbl2 | 1 | 0 | 0 | 965 |
?
ページが並び替えられていますね。
また今回のデータは各テーブルで複数のエクステントが使用されているのですが、再構築後はページ ID が連続しています。
このことからエクステントの並び替え行われていることが確認できます。
インデックスの再構成ではエクステントの並び替えが行われません。
# 再構成は現在使用されているページ内で断片化を解消します。そのためエクステントの並び替えは行われません。
インデックスの再構築はインデックスの再作成に近い処理が実行されるため、エクステントも並び替えられます。
エクステントの断片化を解消するためには再構築が必要となります。
エクステントの断片化の状態は下位互換として残されている [DBCC SHOWCONTIG] でないと見れないみたいなんですよね。
DMV の [sys.dm_db_index_physical_stats] でも断片化の状況は確認できるのですが、リーフレベルの断片化の状態だけで、
エクステントの断片化の状態は出力されていなかったはずです。
# ヒープの場合はページの断片化はないので、エクステントの断片化の状態が出力されます。
[DBCC SHOWCONTIG] でないと割り当てられているエクステントの数も見ることができません。
[sys.dm_db_index_physical_stats] の情報はページを基準に出力されているんですよね。
その 1 / その 2 の 2 回に分けてインデックスの再構成と再構築のデータの並び替えの違いについて投稿してみました。
並び替えの違いだけでなく、ロックされているデータの取り扱いも再構成と再構築では異なります。
2 回の予定だったのですが、その 3 としてロックされているデータの取り扱いの違いについても確認していきたいと思います。