SE の雑記

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

large value types out of row と Textpointer と BLOB Inline Data

one comment

SQL Server 2005 以降では BLOB を格納する際のデータ型は text / ntext / image ではなく、varchar(max) / nvarchar(max) / varbinary(max) が推奨となっています。

型の変更だけでなく、[large value types out of row] というテーブルオプションも追加されています。

今回はこのテーブルオプションについて見ていきたいと思います。

■デフォルトの状態の BLOB データの格納


まずは、デフォルトの状態 (large value types out of row が OFF) で BLOB のデータがどのように格納されるかを見ていきたいと思います。

使用するテーブルはこちら。

CREATE TABLE [dbo].[Table_1](
    [Col1] [int] IDENTITY(1,1) NOT NULL,
    [Col2] [varchar](max) NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
    [Col1] ASC
)
) ON [PRIMARY]

 

int の主キーと BLOB の列を持つシンプルな構造のテーブルです。

このテーブルに以下のクエリでデータを INSERT します。

INSERT Table_1 (Col2) VALUES(REPLICATE(N’A’, 10))

これで準備は完了です。
ページ情報を確認してみたいと思います。

DBCC IND(N’TEST’, N’dbo.Table_1′, 1)
DBCC TRACEON (3604)
DBCC PAGE(N’TEST’, 1, 432, 3)
DBCC TRACEOFF

432 ページの情報を確認していますが、このページ番号は DBCC IND を実行し、取得したものを設定します。

取得したページの情報がこちらになります。

Slot 0 Offset 0x60 Length 25

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 25                   
Memory Dump @0x00000000301CA060

0000000000000000:   30000800 01000000 02004c01 00190041 41414141 †0………L….AAAAA
0000000000000014:   41414141 41†††††††††††††††††††††††††††††††††††AAAAA 

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

Col1 = 1                           

Col2 = [BLOB Inline Data] Slot 0 Column 2 Offset 0xf Length 10 Length (physical) 10

00000000301BF920:   41414141 41414141 4141††††††††††††††††††††††††AAAAAAAAAA

Slot 0 Offset 0x0 Length 0 Length (physical) 0

デフォルトの状態では、 [BLOB Inline Data] (行内レコード) として、データ行に BLOB の列が直接格納されていることが確認できます。

これは DBCC IND の結果からも確認することができます。
今回使用しているテーブルは IAM (PageType = 10) と データページ (PageType = 1) で構成されていることが実行結果からわかります。
# PagePID が 432 から変わってしまっているのは、データを消してから画面キャプチャを取得したためです…。
image

1 ページのサイズは 8KB と決まっていますので、それを超えるデータに関しては、行外データとして LOB ストレージ領域に格納がされます。
# オプションを設定しないと、8,000 バイトを超えるデータを格納できないというわけではありません。
8KB を超える文字列を INSERT 下場合の結果がこちらになります。
データページ以外に LOB データ (PageType = 3) のページが追加されていることが確認できますね。
image

この場合のページの情報は以下のようになります。

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

Col1 = 1                           

Col2 = [BLOB Inline Root] Slot 0 Column 2 Offset 0xf Length 36 Length (physical) 36

Level = 0                           Unused = 255                        UpdateSeq = 1
TimeStamp = 1724121088              Type = 4                           
Link 0

Size = 8040                         RowId = (1:473:0)                  
Link 1

Size = 8164                         RowId = (1:472:0)                  

Slot 0 Offset 0x0 Length 0 Length (physical) 0

行内データのときとは異なり、[BLOB Inline Root] という種別になっています。
ページ内に LOB のデータがおさまらない場合は、ページのポインタが列に格納されます

■large value types out of row を有効化


それでは、[large value types out of row] を有効にしてみます

sp_tableoption ‘dbo.Table_1’, ‘large value types out of row’, ‘ON’

 

この状態で先ほどと同じ INSERT のクエリを実行してデータを挿入し、ページの情報を取得してみます。
image

先ほどと異なり、ページ内に収まるデータでも [LOB data] のページが存在していることが確認できます。
それでは、データページである [288] ページの情報を確認してみます。

Slot 0 Offset 0x60 Length 31

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 31                   
Memory Dump @0x000000003376A060

0000000000000000:   30000800 01000000 0200b401 001f8000 00112700 †0………´…….’.
0000000000000014:   00000018 01000001 000000††††††††††††††††††††††………..

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

Col1 = 1                           

Col2 = [Textpointer] Slot 0 Column 2 Offset 0xf Length 16 Length (physical) 16

TextTimeStamp = 655425536           RowId = (1:280:0)                  

Slot 0 Offset 0x0 Length 0 Length (physical) 0

 

[Textpointer] として、LOB へのポインターが格納されていることが確認できますね。

[large value types out of row] を有効にすると、LOB は行内データではなく行外に格納されるようになりますのでこのようなデータ格納となります。

 

行内にデータがあるばあいは、対象の行だけを検索すれば必要な結果を返すことができますので、検索時の取得コストは少なくて済みますが、データの密度としては低いものになります。
行外に出した場合は、LOB は行内には含まれませんので、1 ページ内に複数のレコードが格納できるようになりますが、LOB のデータを取得しようとした場合は必ずポインタを経由して他のページを読む必要がありますので検索時の取得コストが高くなります。(最低 2 ページ読む必要がありますので)

使い分けはケースバイケースになるとは思うのですが、LOB についてぼんやりと考えていたらこのような設定があったので少しまとめてみました。

Share

Written by Masayuki.Ozawa

4月 19th, 2011 at 8:28 pm

Posted in SQL Server

Tagged with

One Response to 'large value types out of row と Textpointer と BLOB Inline Data'

Subscribe to comments with RSS or TrackBack to 'large value types out of row と Textpointer と BLOB Inline Data'.

  1. […] value types out of row の有無による検索への影響 以前、large value types out of row と Textpointer と BLOB Inline Data という投稿で少し書いたのですが、このオプションは8,000 バイト以下の […]

Leave a Reply