SE の雑記

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

Allocation Order Scan について調べてみる

leave a comment

SQL Server の Allocation Order Scan (割り当て順序スキャン) について少し調べてみたいと思います。

技術情報としては以下の情報を参考にさせていただいています。
When can allocation order scans be used?
SQL Server 2008 Fragmentation
FIX が BLOB データを取得するために SQL Server 2005 または SQL Server 2008 で、NOLOCK テーブル ヒントが使用するクエリを実行すると、クエリが非常に遅い実行されます。
A Tale of Two Index Hints
Previously committed rows might be missed if NOLOCK hint is used

■IAM について


Allocation Order Scan を調べる際には IAM を理解する必要があります。

IAM は Index Allocation Map の略でページの割り当て情報を管理するシステムアロケーションページとなります。
Index という名称がついていますがページの割り当ての管理をするためのものですのでヒープでも使用されています。

SQL Server 2012 SP1 以降では、sys.dm_db_database_page_allocations を使用することで IAM のページを確認することができます。

SELECT *
FROM sys.dm_db_database_page_allocations(DB_ID(N’TEST’), OBJECT_ID(N’dbo.HEAPTEST’), 0, NULL, ‘DETAILED’)
WHERE page_type= 10

それ以外のバージョンでは DBCC IND で確認することができます。

DBCC IND(N’TEST’,’dbo.HEAPTEST’, 0)

 

IAM は PageType = 10 ですのでこのページを確認することで IAM の内容を確認することができます。

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

 

ヒープ構造のテーブルの場合は、B-Tree 構造になっていませんので、最初の IAM 読み、そこからデータを検索していく形になります。

このあたりの動作に関しては、A heap of clusters にまとまっています。

 

■Allocation Order Scan


ここからが本題の Allocation Order Scan になります。

Allocation Order Scan はページの割り当て順にスキャンする方式となり、このスキャンの際には IAM が使用されています。

動作をわかりやすく理解するためには NOLOCK / TABLOCK / READ UNCOMMITTED を使用するとよいかと思います。

NOLOCK / TABLOCK /READ UNCOMMITTED を使用した場合の Scan は、Allocation Order Scan を使用してのスキャンとなります。

SELECT TOP 1000 * FROM NOLOCKTEST (READUNCOMMITTED)

image

SELECT TOP 1000 * FROM NOLOCKTEST

image

上記の 2 種類のクエリを実行した際の結果ですが同じになるでしょうか。結論から言うと同じにはなりません。

プランとしては両方とも Clustered Index Scan となっていますが、ロックヒントを設定しているほうに関しては Index Order Scan ではなく Allocation Order Scan が実行されています。

先ほどのクエリの実行結果を比較してみます。
上段がロックヒントを設定しているもの、下段がロックヒントを設定していないものになります。
image

ORDER BY 句を指定していないので厳密な動作としては順序が保障されないということはありますが、ロックヒントを設定していないものについては Col1 に設定されているクラスタ化インデックスの順序で出力がされていますが、ロックヒントを設定しているものに関してはクラスタ化インデックスの順序では出力がされていません。

これが Allocation Order Scan かどうかの差となっています。
先ほどの二つのクエリですが、プランは同一となっていますが [SET STATISTICS IO ON] を有効にして IO 統計の情報を出力するようにしてみます。

テーブル ‘NOLOCKTEST’。スキャン回数 1、論理読み取り数 26、物理読み取り数 0、先行読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB 先行読み取り数 0。
テーブル ‘NOLOCKTEST’。スキャン回数 1、論理読み取り数 35、物理読み取り数 0、先行読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB 先行読み取り数 0。

同一のプランとなっていますが読み取り数に差が出ていますね。
この読み取り数の差はケースバイケースになると思うので今回は少しおいておきますが、同一のプランで同じ件数の読み取りをしているのですが論理読み取り数に差が出ていますね。

Allocation Order Scan の動作を見るために、IAM のページの割り当て状況を見てみたいと思います。
image

IAM による割り当ての確認では、最初は 5544 ページから割り当てられていることが確認できますね。
それでは先ほどのロックヒントを指定したクエリを以下のように書き換えてみます。

SELECT TOP 1000 * FROM NOLOCKTEST (READUNCOMMITTED)
CROSS APPLY
sys.fn_PhysLocCracker(%%physloc%%)

image

IAM による Allocation Order Scan が実行された場合は、IAM からページを取得しているので、5544 ページから読み込まれたことが確認できますね。

Index Order Scan に関しては最初に割り当てられているデータページ (previous_pageがないページ) から読み込みが行われていますので、1 ~ となっています。

この辺のスキャン方法の違いに関しては今まで意識していなかったのでかなり勉強になりました。
ストレージエンジンとクエリの両面から見ないといけないものがいろいろありますね。

Written by masayuki.ozawa

1月 2nd, 2013 at 8:28 pm

Posted in SQL Server

Tagged with

Leave a Reply

*