SE の雑記

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

large value types out of row の設定による検索への影響

leave a comment

今朝、TechNet の SQL Server フォーラムを眺めていたところ以下のトピックがありました。
LOB(ラージオブジェクト)データを含むレコードのインデックスについて

このトピックでサヴロウさんの回答がとても参考になったので自分でもさっそく検証してみました。

■large value types out of row の有無による検索への影響


以前、large value types out of row と Textpointer と BLOB Inline Data という投稿で少し書いたのですが、このオプションは8,000 バイト以下の LOB レコードを行内に保存するか、行外に保存するかを指定するためのオプションになります。

今回はテスト用に以下の 2 つのテーブルを用意しました。

CREATE TABLE [dbo].[InRow](
    [Col1] [int] IDENTITY(1,1) NOT NULL,
    [Col2] [uniqueidentifier] NULL,
    [Col3] [nvarchar](max) NULL,
CONSTRAINT [PK_InRow] PRIMARY KEY CLUSTERED
(
    [Col1] ASC
)) ON [PRIMARY]

GO

CREATE TABLE [dbo].[TextPointer](
    [Col1] [int] IDENTITY(1,1) NOT NULL,
    [Col2] [uniqueidentifier] NULL,
    [Col3] [nvarchar](max) NULL,
CONSTRAINT [PK_TextPointer] PRIMARY KEY CLUSTERED
(
    [Col1] ASC
)) ON [PRIMARY]

EXEC sp_tableoption N’dbo.TextPointer’, ‘large value types out of row’ , ‘ON’
GO

テーブル定義は [InRow] [TextPointer] ともに同じですが、[TextPointer] テーブルでは、[large value types out of row] を有効 (ON) にしていますので LOB はデータページにはポインターが保存され、実際のデータは行外データとしてデータページ外に保存されます。

このテーブルに以下のクエリでデータを挿入します。
# データ件数は同じです。

INSERT INTO InRow(Col2, Col3) VALUES (NEWID(), REPLICATE(‘A’, 3000))
GO 100000
INSERT INTO TextPointer(Col2, Col3) VALUES (NEWID(), REPLICATE(‘A’, 3000))
GO 100000

実際のデータの格納状況を SSMS の [テーブルごとのディスク使用量] のレポートから確認をしてみます。
image

同じレコード数が格納されていますね。
TextPointer のほうが [データ (KB)] が多いのは、InRow は 1 行のサイズが  [レコード] ですんでいるのが、TextPointer では[レコード + 行ポインタ + 行外データ] という構成になるためだと思います。

それでは、DBCC IND を使ってページ割り当てを確認してみます。
image

[InRow] テーブルのクラスター化インデックスは[In-row data] (LOB は行内データ) で構成されていますが、[TextPointer] テーブルは [LOB data] (LOB は行外データ] となっていることが確認できます。

それでは、環境の準備ができましたので設定の有無による検索への影響を見ていきたいと思います。

以前の投稿では

LOB のデータを取得しようとした場合は必ずポインタを経由して他のページを読む必要がありますので検索時の取得コストが高くなります。

というように書きました。

逆を言うと、LOB のデータを取得しない場合は読み込むページに差が出るということなのですよね。

ということで以下のクエリを実行して、キャッシュされるページ数の差を見ていきたいと思います。

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SELECT Col1, Col2 FROM InRow
GO

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SELECT Col1, Col2 FROM TextPointer
GO

最初のバッチ (GO まで) を実行した際のデータページのキャッシュ状況がこちらです。
image

今回はクラスター化インデックスのみを付与していますので、クエリの実行時には [Clusterd Index Scan] (データページの全件スキャン) が行われます。
image

行内データの場合は 1 ページに格納しているデータ内に、LOB が含まれていますのでページ密度が低い状態となります。
SQL Server のメモリ上へのデータロードはページ単位で実行されますので、検索列に含んでいない Col3 もメモリ上にロードされた形になります。
# ロードはされているが結果には返されないので、不必要な項目を含めないことはネットワークを流れるデータ量の観点からはとても重要です。

それでは、2つめのバッチを実行してみます。
image

行外データの場合は、データページに LOB は含まれていませんので、LOB の列を除いた検索をすれば、データページの検索だけですみます。
LOB を行外データにした場合はページ内のデータ密度は高いですので、読み込まれるページ数は少なくなります。
この時の実行プランは以下のようになっています。
image

どちらの実行プランも同じですね。

行外データに設定していても LOB が必要になる場合は、もちろん読み込まれるページ数は増えます。

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SELECT * FROM TextPointer
GO

image

LOB を頻繁に使用しないのであれば、行外データにしたほうが効率が良いことがありそうですね。

フォーラムを見てかなり勉強になりました。
まだまだ修行不足ですね(汗)

Share

Written by Masayuki.Ozawa

6月 22nd, 2011 at 8:30 am

Posted in SQL Server

Tagged with

Leave a Reply