SE の雑記

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

一意にならない列にクラスター化インデックスを設定した場合の uniqueifier 列について

leave a comment

今日は、一意にならない列にクラスター化インデックスを設定した際の挙動についてみていきたいと思います。
今回は SQL Server "Denali" で実施していますが、SQL Server 2008 以降であれば、今回のクエリは修正せずに動作すると思います。
# SQL Server 2005 以前は、DECLARE やインクリメントの部分を修正いただければ動くと思います。

SQL Server Management Studio (SSMS) の、テーブル デザイナーで主キー (Primary Key) を作成すると、設定したキーが [クラスタ化インデックス] となります。
image

これはテーブル デザイナーの image をクリックすると以下のクエリが実行されてテーブルが作成されるためです。

CREATE TABLE dbo.Table_1
    (
    Col1 int NOT NULL,
    Col2 char(800) NULL,
    Col3 datetime NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Table_1 ADD CONSTRAINT
    PK_Table_1 PRIMARY KEY CLUSTERED
    (
    Col1
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

主キー制約 (PRIMARY KEY 制約) が CLUSTERD を指定して作成されているため、主キーの列がクラスター化 インデックスとして作成されます。

作成されるキーを確認してみると [Create As Clustered] が [Yes] となっているのが確認できます。
image

特に意識をしないでテーブルデザイナーで主キーを設定すると [一意になる列に対して、クラスター化インデックスが設定される] ことになります。

■一意制約を確認

まずは主キーを設定したことによる一意制約 (ユニーク制約) を確認してみたいと思います。
以下のクエリを実行して、数件のデータを INSERT します。
# テーブルは上述のクエリで作成しています。

SET NOCOUNT ON
GO

DECLARE @i int = 1
WHILE (@i <= 10)
BEGIN
INSERT INTO [Table_1]
VALUES (
@i,
NEWID(),
GETDATE()
)
SET @i += 1
END

このようにデータが格納されました。
image

それでは、1 行と同じデータをキーとする (Col1の値が同じ) データを INSERT してみます。

INSERT INTO [Table_1]
VALUES (
1,
NEWID(),
GETDATE()
)

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint ‘PK_Table_1’. Cannot insert duplicate key in object ‘dbo.Table_1’. The duplicate key value is (1).
The statement has been terminated.

主キー制約により同一のキーを持つデータが INSERT できないためエラーとなります。
今回のテーブルは Col1 に対してクラスター化インデックスを設定していますので [クラスター化インデックスは一意になる] ということが保障されていますね。

■インデックスについての前段


クラスター化インデックスは [データとインデックスの一体型] のインデックスとなります。

クラスタ化 / 非クラスタ化インデックスは [B ツリー] (Baranced Tree) 構造となっています。

image

頻繁に目にする絵としてはこのような形式でしょうか。
インデックスは木構造 (ツリー構造) として管理がされています。

非クラスタ化インデックスの場合、リーフノード (最下層のデータページ) にはインデックスのデータが格納されますが、クラスター化インデックスの場合は、リーフノードはデータページとなり、実際のデータが格納されます。

先ほど作ったテーブルはクラスター化インデックスだけでしたので、Col2 に非クラスター化インデックスを設定してみたいと思います。
設定は以下のクエリで実施しました。

CREATE NONCLUSTERED INDEX NCL_Table_1_Col2 ON dbo.Table_1
    (
    Col2
    )  ON [PRIMARY]
GO

インデックスの設定状況を見てみます。

SELECT
    OBJECT_NAME(i.object_id) AS [object_name],
    i.name AS [index_name],
    i.index_id,
    i.type_desc,
    sc.name AS [column_name]
FROM
    sys.indexes AS [i]
    LEFT JOIN
    sys.index_columns AS [ic]
    ON
    ic.object_id = i.object_id
    AND
    ic.index_id = i.index_id
    LEFT JOIN
    sys.columns AS [sc]
    ON
    sc.column_id = ic.column_id
    AND
    sc.object_id = ic.object_id
WHERE
    i.object_id = OBJECT_ID(N’dbo.Table_1′)

image

2 つのインデックスが [Table_1] に設定されていることが確認できました。

先ほどはテーブルに 10 件のデータしか INSERT しませんでしたが、ループの回数を増やし、50 件のデータを INSERT します。
INSERT が終了したら、[DBCC IND] を使って各インデックスのページ割り当て状況を確認してみたいと思います。

まずは、クラスター化インデックス (index_id : 1) の情報を取得してみます。

DBCC IND(N’TEST’, N’dbo.Table_1′, 1)

image

取得した結果が上記の画像となります。
クラスター化インデックスは、

  • PageType : 10 (IAM ページ)
  • PageType : 1 (Data ページ)
  • PageType : 2 (Index ページ)

の 3 種類で構成されていることが確認できます。
クラスター化インデックスでは Data ページ (実際のデータ) のページが含まれています。

それでは、非クラスター化インデックス (indexid : 2) の情報も取得してみます。

DBCC IND(N’TEST’, N’dbo.Table_1′, 2)

image

非クラスター化インデックスの場合は

  • PageType : 10 (IAM ページ)
  • PageType : 2 (Index ページ)

の 2 種類で構成されていますね。

非クラスター化インデックスは [データとインデックスの分離型] のインデックスとなります。
そのため、Index ページで構成され、データページに関しては保持していない状態となります。

 

■一意になるクラスター化インデックスのページ情報


今回は、一意にならないクラスター化インデックスについてまとめていこうと思っていますが、その前に比較対象となる一意になるクラスター化インデックスのインデックス情報とページ情報についてみていきたいと思います。

ページ情報は [DBCC PAGE] を使用することで確認することができます。
# DBCC PAGE でページ情報を取得するために、[トレースフラグ 3604] を設定しています。WITH TABLERESULTS 指定している場合はトレースフラグがなくてもページ情報をとれそうですが。

DBCC TRACEON(3604)
DBCC PAGE(N’TEST’, 1, 174, 3)  WITH TABLERESULTS
DBCC TRACEOFF(3604)

 

DBCC PAGE の形式は以下のようになります。

dbcc PAGE ( {‘dbname’ | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])

[pagenum] に情報を取得したいページ番号を指定します。
DBCC IND を実行した際に、クラスター化インデックスの Index ページ (PageType : 2) は 174 ページ (PagePID = 174) ということが確認できていますので pagenum には 174 を指定しています。
printopt で 3 で行データを個別に出力できるようにしています。

実行した結果がこちらになります。
image

上段がページ情報、下段がインデックス情報となっています。
# WITH TABLERESULTS を指定しない場合は、Message として上段のページ情報が出力されます。

クラスター化インデックスは [Col1] を指定していますので、Index ページのツリーが Col1 で構成されていることが確認できます。

非クラスター化インデックスの Index ページでも同様の情報を取得してみたいと思います。

DBCC TRACEON(3604)
DBCC PAGE(N’TEST’, 1, 171, 3)  WITH TABLERESULTS
DBCC TRACEOFF(3604)

非クラスター化インデックスの場合は以下のような情報が取得できます。
image

非クラスター化インデックスの場合、インデックスの項目として含めている [Col2] 以外にも [Col1] が含まれていることが確認できます。

非クラスター化インデックスで以下のような検索を行った場合、このような実行プランが使用されます。

SELECT
    *
FROM
    [Table_1]
WHERE
    [Col2] = ‘0DFA7CF6-2B61-4341-869A-95ECE30436FC’

image

image
[Col1] [Col2] は非クラスター化インデックスである [NCL_Table_1_Col2] の Index ページに含まれていますが、[Col3] に関しては非クラスター化インデックスには含まれていません。
そのため、非クラスター化インデックスで取得した [Col1] を使用して、[Key Lookup] を行い [Col3] のデータを取得したものを結果として返しています。

SELECT で返す対象を [Col1], [Col2] のみとすると、非クラスター化インデックスの Index ページだけで情報が足りますので、実行プランが変わってきます。

SELECT
    [Col1],
    [Col2]
FROM
    [Table_1]
WHERE
    [Col2] = ‘0DFA7CF6-2B61-4341-869A-95ECE30436FC’

image

ついでですのでクラスター化インデックスの Data ページも DBCC PAGE で取得してみたいと思います。

DBCC TRACEON(3604)
DBCC PAGE(N’TEST’, 1, 168, 3) WITH TABLERESULTS
DBCC TRACEOFF(3604)

クラスター化インデックスの Data ページ には実際のデータ (86 ~ 88 行目) が格納されていることが確認できます。
image

非クラスター化インデックスの場合は Index ページにハッシュ値 (KeyHashValue) を持っていましたが、クラスター化インデックスの場合は、Deta ページにハッシュ値が格納されていることもここから確認ができます。

■一意にならない列にクラスター化インデックスを指定した場合


ここからが今回の投稿の本題になります。

BOL には以下のような記載があります。
# [クラスタ化インデックスの設計ガイドライン] からの引用になります。

UNIQUE プロパティを指定せずにクラスタ化インデックスが作成された場合、データベース エンジンにより、4 バイトの uniqueifier 列が自動的にテーブルに追加されます。必要があれば、各キーを一意にするため、データベース エンジンにより自動的に uniqueifier 値が行に追加されます。この列とその値は、内部的に使用されるもので、ユーザーが参照したりアクセスすることはできません。

UNIQUE プロパティを指定せずに (一意にならない) クラスター化インデックスが作成された場合についての記述があります。
一意にならない列を使用してクラスター化インデックスを作成した場合 [uniqueifier] が内部的に付与されると記載されていますね。

[Table_2] として以下のテーブルを作成してみました。

CREATE TABLE dbo.Table_2
    (
    Col1 int NOT NULL,
    Col2 int NOT NULL,
    Col3 char(800) NULL,
    Col4 datetime NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Table_2 ADD CONSTRAINT
    PK_Table_2 PRIMARY KEY NONCLUSTERED
    (
    Col1
    ) ON [PRIMARY]

GO
CREATE CLUSTERED INDEX CL_Table_2_Col2 ON dbo.Table_2
    (
    Col2
    ) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX NCL_Table_2_Col3 ON dbo.Table_2
    (
    Col3
    ) ON [PRIMARY]
GO

image

[Table_1] と近い構成ではあるのですが、列を一つ増やし主キー (Col1) とは異なる列 (Col2) にクラスター化インデックスを作成しています。

インデックスの情報がこちらになります。
# Table_1 に対して実行したインデックス情報の取得を Table_2 に対して実行した結果です。
image

クラスター化インデックス (またはヒープ) の index_id は 1 となりますので、以降の index_id が主キー、非クラスター化インデックスで使用されています。

このテーブルに対してデータを INSERT してます。

SET NOCOUNT ON
GO

DECLARE @i int = 1
WHILE (@i <= 50)
BEGIN
INSERT INTO [Table_2]
VALUES (
@i,
@i,
NEWID(),
GETDATE()
)
SET @i += 1
END

途中までの結果ですがこのようなデータが格納されました。
image

それでは、次にインデックス情報を確認してみたいと思います。

DBCC IND(N’TEST’, N’dbo.Table_2′, 1)

image

DBCC IND(N’TEST’, N’dbo.Table_2′, 2)

image

DBCC IND(N’TEST’, N’dbo.Table_2′, 3)

image

それでは、作成したテーブルのクラスター化インデックスの Index ページを確認してみたいと思います。

DBCC TRACEON(3604)
DBCC PAGE(N’TEST’, 1, 147, 3) WITH TABLERESULTS
DBCC TRACEOFF(3604)

image

一意になる列にクラスター化インデックスを設定した時とは異なり、[UNIQUIFIER (key)] という列が追加されていることが確認できます。
これが BOL に記載されている [uniqueifier 列] になります。

次に、非クラスター化インデックスも見てみたいと思います。

DBCC TRACEON(3604)
DBCC PAGE(N’TEST’, 1, 145, 3) WITH TABLERESULTS
DBCC TRACEOFF(3604)

image

非クラスター化インデックスにも [UNIQUIFIER (key)] が格納されていることが確認できます。

主キーに設定した非クラスター化インデックスにも追加がされています。

DBCC TRACEON(3604)
DBCC PAGE(N’TEST’, 1, 142, 3) WITH TABLERESULTS
DBCC TRACEOFF(3604)

image

今回はどの列の [UNIQUIFIER] も [0] となっています。
これは、クラスター化インデックスに設定している値が重複していないためこのような設定となっています。
ループの中で主キーと同じ [@i] を使用して INSERT をしていますので、データは一意になっています。

データを一度 Truncate Table して、今度は以下のクエリでデータを INSERT してみます。

SET NOCOUNT ON
GO

Truncate Table Table_2
GO

DECLARE @i int = 1
WHILE (@i <= 50)
BEGIN
INSERT INTO [Table_2]
VALUES (
@i,
@i % 10,
NEWID(),
GETDATE()
)
SET @i += 1
END

先ほどとは異なり、 [Col2] で重複したデータが発生しています。
image

それでは、DBCC IND でインデックス情報を取得して、ページの情報を確認したいと思います。
# DBCC IND の結果の並び順は先ほどと同じです。
image
image
image

重複するデータを作った場合の、クラスター化インデックスの Index ページ情報がこちらになります。

DBCC TRACEON(3604)
DBCC PAGE(N’TEST’, 1, 126, 3) WITH TABLERESULTS
DBCC TRACEOFF(3604)

image

先ほどと異なり、[UNIQUIFIER (key)] に [0] 以外のデータが出現していることが確認できます。
他の 2 つのインデックスの Index ページについても確認をしてみます。
image
image

どちらも 、[UNIQUIFIER (key)] に [0] 以外の値が発生していますね。

ちょっと趣向を変えてデータを SELECT する際のクエリを以下に変更して実行してみます。

SELECT
    sys.fn_PhysLocFormatter(%%physloc%%),
    *
FROM
    [Table_2]

image

[sys.fn_PhysLocFormatter(%%physloc%%)] は SQL Server 2008 以降で追加された Undocumentd な関数なのですが、この関数を使用するとどのデータがどのページ / スロットに格納されているかを確認することができます。
# たしか SQL Server 2005 以前では使えなかったはずです。

それでは、[Col2] が [0] という同一の値になっている非クラスター化インデックスのページ (Page No : 89) のページ情報を DBCC PAGE で確認してみます。
# 結果をコピーしやすくするため、[WITH TABLERESULTS] を外しています。

DBCC TRACEON(3604)
DBCC PAGE(N’TEST’, 1, 89, 3)
DBCC TRACEOFF(3604)

 

実行結果から実際の行データを抜粋したものがこちらになります。

Slot 0 Column 0 Offset 0x0 Length 4 Length (physical) 0
UNIQUIFIER = 0                     

Slot 0 Column 2 Offset 0x4 Length 4 Length (physical) 4
Col2 = 0
                           

Slot 0 Column 1 Offset 0x8 Length 4 Length (physical) 4
Col1 = 10                          

Slot 0 Column 3 Offset 0xc Length 800 Length (physical) 800
Col3 = 1D87A04C-2AA2-44A3-8625-2BFDF5D21089   

Slot 0 Column 4 Offset 0x32c Length 8 Length (physical) 8
Col4 = 2011-01-07 21:17:51.630     

Slot 0 Offset 0x0 Length 0 Length (physical) 0
KeyHashValue = (460583f31a98)      

—————-

Slot 1 Column 0 Offset 0x33b Length 4 Length (physical) 4
UNIQUIFIER = 1                     

Slot 1 Column 2 Offset 0x4 Length 4 Length (physical) 4
Col2 = 0                           

Slot 1 Column 1 Offset 0x8 Length 4 Length (physical) 4
Col1 = 20                          

Slot 1 Column 3 Offset 0xc Length 800 Length (physical) 800
Col3 = 83023F4B-2EF3-4403-8EF2-031048C6EE0B Col4 = 2011-01-07 21:17:51.633     

Slot 1 Offset 0x0 Length 0 Length (physical) 0
KeyHashValue = (bb0d06c12baa)      

—————-

Slot 2 Column 0 Offset 0x33b Length 4 Length (physical) 4
UNIQUIFIER = 2                     

Slot 2 Column 2 Offset 0x4 Length 4 Length (physical) 4
Col2 = 0                           

Slot 2 Column 1 Offset 0x8 Length 4 Length (physical) 4
Col1 = 30                          

Slot 2 Column 3 Offset 0xc Length 800 Length (physical) 800
Col3 = 418F5949-5825-489B-90FF-FA3B5C18ECF7

Slot 2 Column 4 Offset 0x32c Length 8 Length (physical) 8
Col4 = 2011-01-07 21:17:51.640     

Slot 2 Offset 0x0 Length 0 Length (physical) 0
KeyHashValue = (5b39284eef16)      

—————-

Slot 3 Column 0 Offset 0x33b Length 4 Length (physical) 4
UNIQUIFIER = 3                     

Slot 3 Column 2 Offset 0x4 Length 4 Length (physical) 4
Col2 = 0                           

Slot 3 Column 1 Offset 0x8 Length 4 Length (physical) 4
Col1 = 40                          

Slot 3 Column 3 Offset 0xc Length 800 Length (physical) 800
Col3 = 83B5162B-CF09-4CDE-87AC-F0DF2370A92C

Slot 3 Column 4 Offset 0x32c Length 8 Length (physical) 8
Col4 = 2011-01-07 21:17:51.647     

Slot 3 Offset 0x0 Length 0 Length (physical) 0
KeyHashValue = (a27543d90a1a)      

—————-

Slot 4 Column 0 Offset 0x33b Length 4 Length (physical) 4
UNIQUIFIER = 4                     

Slot 4 Column 2 Offset 0x4 Length 4 Length (physical) 4
Col2 = 0                           

Slot 4 Column 1 Offset 0x8 Length 4 Length (physical) 4
Col1 = 50                          

Slot 4 Column 3 Offset 0xc Length 800 Length (physical) 800
Col3 = 491C7945-9054-4874-B33C-F9744357ACDB

Slot 4 Column 4 Offset 0x32c Length 8 Length (physical) 8
Col4 = 2011-01-07 21:17:51.670     

Slot 4 Offset 0x0 Length 0 Length (physical) 0
KeyHashValue = (9a507450666f)      
Slot 5 Offset 0x60 Length 823

—————-

Slot 5 Column 0 Offset 0x0 Length 4 Length (physical) 0
UNIQUIFIER = 0                     

Slot 5 Column 2 Offset 0x4 Length 4 Length (physical) 4
Col2 = 1                           

Slot 5 Column 1 Offset 0x8 Length 4 Length (physical) 4
Col1 = 1                           

Slot 5 Column 3 Offset 0xc Length 800 Length (physical) 800
Col3 = 74626C0F-6CF9-42A5-A5BB-5BC2BEE380B0

Slot 5 Column 4 Offset 0x32c Length 8 Length (physical) 8
Col4 = 2011-01-07 21:17:51.623     

Slot 5 Offset 0x0 Length 0 Length (physical) 0
KeyHashValue = (de42f79bc795) 

—————-

Slot 6 Column 0 Offset 0x33b Length 4 Length (physical) 4
UNIQUIFIER = 1                     

Slot 6 Column 2 Offset 0x4 Length 4 Length (physical) 4
Col2 = 1                           

Slot 6 Column 1 Offset 0x8 Length 4 Length (physical) 4
Col1 = 11                          

Slot 6 Column 3 Offset 0xc Length 800 Length (physical) 800
Col3 = 23E37BB6-CACD-49A6-A3C1-8B5AB19CF1E9

Slot 6 Column 4 Offset 0x32c Length 8 Length (physical) 8
Col4 = 2011-01-07 21:17:51.630     

Slot 6 Offset 0x0 Length 0 Length (physical) 0
KeyHashValue = (e2338e2f4a9f)    

[Col2] (クラスター化インデックス) の値が重複しているものは [UNIQUIFIER] に異なる値が設定され、値が一意になっていることが確認できますね。
Length が 4 となっていることから BOL に書かれている [4 バイト] というところも確認することができます。

実行プランとしてはパッと見た感じでは一意になる場合と変わらないみたいですね。
image
image

一意にならない列にクラスター化インデックスを作成することは可能ですが、内部的には[4 バイトの UNIQUIFIER列] を使って、一意になっていることがページ情報から確認することができたかと思います。
# 一意になっていないと、非クラスター化インデックスからデータページにリンクする際のオーバーヘッドがあるため、内部的に一意にしているのだと思いますが。

このあたりの内容ですが、実は MCM (Microsoft Certified Master) SQL Server 2008  の対象範囲になっているようです。
Microsoft Certified Master on Microsoft SQL Server 2008

今回の内容は、MCM の勉強教材の Data Structures からちょっと抜粋したものになります。
SQL Server 2008 Microsoft Certified Master (MCM) Readiness Videos

かなり興味深い内容が目白押しなので、面白い内容があったらちょくちょくブログにまとめていきたいと思います。
# SQLCLR や XML は無理ですが…。

Written by masayuki.ozawa

1月 7th, 2011 at 5:53 pm

Posted in MCM,SQL Server

Tagged with ,

Leave a Reply

*