今朝、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]( GO CREATE TABLE [dbo].[TextPointer]( EXEC sp_tableoption N’dbo.TextPointer’, ‘large value types out of row’ , ‘ON’ |
テーブル定義は [InRow] [TextPointer] ともに同じですが、[TextPointer] テーブルでは、[large value types out of row] を有効 (ON) にしていますので LOB はデータページにはポインターが保存され、実際のデータは行外データとしてデータページ外に保存されます。
このテーブルに以下のクエリでデータを挿入します。
# データ件数は同じです。
INSERT INTO InRow(Col2, Col3) VALUES (NEWID(), REPLICATE(‘A’, 3000)) |
実際のデータの格納状況を SSMS の [テーブルごとのディスク使用量] のレポートから確認をしてみます。
同じレコード数が格納されていますね。
TextPointer のほうが [データ (KB)] が多いのは、InRow は 1 行のサイズが [レコード] ですんでいるのが、TextPointer では[レコード + 行ポインタ + 行外データ] という構成になるためだと思います。
それでは、DBCC IND を使ってページ割り当てを確認してみます。
[InRow] テーブルのクラスター化インデックスは[In-row data] (LOB は行内データ) で構成されていますが、[TextPointer] テーブルは [LOB data] (LOB は行外データ] となっていることが確認できます。
それでは、環境の準備ができましたので設定の有無による検索への影響を見ていきたいと思います。
以前の投稿では
LOB のデータを取得しようとした場合は必ずポインタを経由して他のページを読む必要がありますので検索時の取得コストが高くなります。
というように書きました。
逆を言うと、LOB のデータを取得しない場合は読み込むページに差が出るということなのですよね。
ということで以下のクエリを実行して、キャッシュされるページ数の差を見ていきたいと思います。
DBCC DROPCLEANBUFFERS DBCC DROPCLEANBUFFERS |
最初のバッチ (GO まで) を実行した際のデータページのキャッシュ状況がこちらです。
今回はクラスター化インデックスのみを付与していますので、クエリの実行時には [Clusterd Index Scan] (データページの全件スキャン) が行われます。
行内データの場合は 1 ページに格納しているデータ内に、LOB が含まれていますのでページ密度が低い状態となります。
SQL Server のメモリ上へのデータロードはページ単位で実行されますので、検索列に含んでいない Col3 もメモリ上にロードされた形になります。
# ロードはされているが結果には返されないので、不必要な項目を含めないことはネットワークを流れるデータ量の観点からはとても重要です。
行外データの場合は、データページに LOB は含まれていませんので、LOB の列を除いた検索をすれば、データページの検索だけですみます。
LOB を行外データにした場合はページ内のデータ密度は高いですので、読み込まれるページ数は少なくなります。
この時の実行プランは以下のようになっています。
どちらの実行プランも同じですね。
行外データに設定していても LOB が必要になる場合は、もちろん読み込まれるページ数は増えます。
DBCC DROPCLEANBUFFERS |
LOB を頻繁に使用しないのであれば、行外データにしたほうが効率が良いことがありそうですね。
フォーラムを見てかなり勉強になりました。
まだまだ修行不足ですね(汗)