SE の雑記

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

DBCC IND を使用したページの連続性の確認

leave a comment

ここ数日は SQL Server の基礎から勉強しなおそうと思い、インデックスの再構築 / 再構成について調べていました。
データの更新を行っているとデータページの断片化が発生し、断片化解消のためにインデックスの再構築 (REBUILD)、
再構成 (REORGANIZE) が必要となります。

ページの連続性が確認できないかなと思い、いろいろと本を眺めていたところ、[DBCC IND] があったなと思い、
少し試してみました。
# SQL Server の投稿に関しては SQL Server 2008 で検証をしていきたいと思います。

まずはテスト用として以下のテーブルを作成しました。

CREATE TABLE [dbo].[Tbl1](
    [Col1] [uniqueidentifier] NOT NULL,
    [Col2] [nchar](400) 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]

GUID と NCHAR で構成された単純なテーブルです。
このテーブルに以下の SQL でデータを挿入して、インデックスを再構築して断片化を解消した状態にします。

— テーブルにデータを挿入
DECLARE @i int = 1
WHILE (@i <= 100)
BEGIN
    INSERT INTO [dbo].[Tbl1] VALUES(NEWID(), NCHAR(@i))
    SET @i +=1
END

— 主キーが GUID のため、データ挿入後は断片化が発生しているためクラスタ化インデックスを再構築
ALTER INDEX [PK_Tbl1] ON [dbo].[Tbl1] REBUILD

 

この状態で 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)’)

— 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 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 が変わっているのですが、これがインデックスの再構築と再構成の違いでもあります。

Written by masayuki.ozawa

11月 9th, 2009 at 2:54 pm

Posted in SQL Server

Leave a Reply

*