SE の雑記

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

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

leave a comment

前回は再構成と再構築でページの再利用方法が異なるということを DBCC IND で確認しました。
今回はエクステントの並び替えの違いについて確認をしてみたいと思います。
# 最初にスクリプトを書いてから何日か経ちましたので、今回の投稿には使用するスクリプトを記載してみました。

[前段]

– ページ –

SQL Server のデータはページに格納されていきます。
1 ページは 8KB で構成されており、ひとつのページに格納できるデータは 8KB となります。
# それ以上格納したい場合は BLOB を使用しますが今回は通常のデータを対象とします。

1 ページに格納できるデータは同一のテーブルのデータになります。
そのため、1 ページに複数のテーブルのデータが格納されるということはありません。

image

– エクステント –

ページはエクステントという単位で管理されています。
エクステントは 8 ページで構成され、ページに空きが無くなった場合は、ページ単位で領域を増やすのではなく、
エクステント単位 (8KB * 8 = 64 KB)で増やしていきます。

image

– 単一エクステントと混合エクステント –

エクステントには以下の 2 種類があります。

  1. 単一エクステント
  2. 混合エクステント

8 ページで構成されるエクステントですが、使用されているページが一つのオブジェクトで構成される場合と、
複数のオブジェクトが含まれた形で構成される場合があります。
前者を単一エクステント、後者を混合エクステントと呼びます。

image?

最初は単一エクステントで構成され、8 ページまで拡張されたら、混合エクステントに切り替わります。

[本題]?

再構成と再構築ではページの並び替えだけでなく、エクステントの並び替えにも差があります。

この違いについて DBCC IND を使用して確認していきたいと思います。
この確認は単一エクステントになるようにして確認をした方がわかりやすいので、トレースフラグ [1118] を設定して
確認をしています。
# このトレースフラグは混合エクステントを使用しないようにするためのトレースフラグです。

今回はエクステントの並び替えの検証をしたいので検証用のテーブルは 2 つ作成します。
# 1 レコードで 1 ページ使うように nchar を 4000 で設定しています。

– テーブルの作成スクリプト –

DROP TABLE [dbo].[Tbl1]
DROP TABLE [dbo].[Tbl2]

CREATE TABLE [dbo].[Tbl1](
??? [Col1] [uniqueidentifier] NOT NULL,
??? [Col2] [nchar](4000) NULL,
CONSTRAINT [PK_Tbl1] PRIMARY KEY CLUSTERED
(
??? [Col1] ASC
)WITH (PAD_INDEX? = OFF, STATISTICS_NORECOMPUTE? = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS? = ON, ALLOW_PAGE_LOCKS? = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Tbl2](
??? [Col1] [uniqueidentifier] NOT NULL,
??? [Col2] [nchar](4000) NULL,
CONSTRAINT [PK_Tbl2] PRIMARY KEY CLUSTERED
(
??? [Col1] ASC
)WITH (PAD_INDEX? = OFF, STATISTICS_NORECOMPUTE? = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS? = ON, ALLOW_PAGE_LOCKS? = ON) ON [PRIMARY]
) ON [PRIMARY]

?

テーブルの作成が完了したらデータを挿入します。

– テーブルの挿入スクリプト –

TRUNCATE TABLE [dbo].[Tbl1]
TRUNCATE TABLE [dbo].[Tbl2]

DECLARE @i int = 1
WHILE (@i <= 16)
BEGIN
??? INSERT INTO [dbo].[Tbl1] VALUES(NEWID(), NCHAR(@i))
??? INSERT INTO [dbo].[Tbl2] VALUES(NEWID(), NCHAR(@i))
??? SET @i +=1
END

?

この状態で DBCC IND を実行してページの情報を確認してみます。

– DBCC IND のスクリプト –

— DBCC IND の実行結果を格納するテーブル変数の定義
DECLARE @Tbl TABLE(
PageFID int,
PagePID int,
IAMFID int,
IAMPID int,
ObjectID int,
IndexId int,
PartitionNumber bigint,
PartitionID bigint,
iam_chain_type sysname,
PageType int,
IndexLevel int,
NextPageFID int,
NextPagePID int,
PrevPageFID int,
PrevPagePID int
)

— テーブル変数に DBCC IND の実行結果を格納
— 1 を指定することでデータページ (ヒープまたはクラスタ化インデックス) を対象とする
INSERT INTO @Tbl EXEC (‘DBCC IND(N”WORK”,N”dbo.Tbl1”, 1)’)
INSERT INTO @Tbl EXEC (‘DBCC IND(N”WORK”,N”dbo.Tbl2”, 1)’)

— DBCC IND の結果から必要な列を取得
SELECT
??? ROW_NUMBER() OVER (ORDER BY [PagePID] ASC) AS [No],
??? FILE_NAME([PageFID]) AS [FILEName],
??? [PagePID],
??? OBJECT_NAME([ObjectID]) AS [ObjectName],
??? [name],
??? [PageType],
??? [IndexLevel],
??? [NextPagePID],
??? [PrevPagePID]
FROM
??? @Tbl
??? LEFT JOIN
??? [sys].[indexes]
??? ON
??? [@Tbl].[ObjectID] = [sys].[indexes].[object_id]
??? AND
??? [@Tbl].[IndexId] = [sys].[indexes].[index_id]
WHERE
??? [PageType] = 1
ORDER BY
??? [PagePID] ASC

?

– 結果 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
ALTER INDEX [PK_Tbl2] ON [dbo].[Tbl2] 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
ALTER INDEX [PK_Tbl2] ON [dbo].[Tbl2] 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 としてロックされているデータの取り扱いの違いについても確認していきたいと思います。

Share

Written by Masayuki.Ozawa

11月 12th, 2009 at 2:32 pm

Posted in SQL Server

Leave a Reply