都内で働くSEの技術的なひとりごと / Technical soliloquy of System Engineer working in Tokyo

都内でサラリーマンやってます。SQL Server を中心とした (2023年からは Azure も。) マイクロソフト系(たまに、OSS系などマイクロソフト以外の技術も...)の技術的なことについて書いています。日々の仕事の中で、気になったことを技術要素関係なく気まぐれに選んでいるので記事内容は開発言語、インフラ等ばらばらです。なお、当ブログで発信、発言は私個人のものであり、所属する組織、企業、団体等とは何のかかわりもございません。ブログの内容もきちんと検証して使用してください。英語の勉強のため、英語の

SQL Server の構造について記事にしてみる - その3 ( インデックスの構造を深堀してみる ) -

前回の内容をおさらいしてみる

 SQL Server の構造について記事にしてみる - その2 ( 前回の 『 インデックスの基礎知識 』 を実際のテーブルで実験してみる ) - - 都内で働くSEの技術的なひとりごと では、クラスタ化インデックス、非クラスタ化インデックス、付加列に関する検証を行いました。

今回のやることを説明してみる

 B+Tree インデックスの構造を Undocumented な関数、コマンドを使用して詳細に調べていきたいと思います。

使用する環境を説明してみる

 Microsot Azure 上で 動作している Windows Server 2012 R2、SQL Server 2014 で動作確認をします。データベースは、AdventureWorks2012 上の Sales.SalesOrderHeader テーブルを使用します。

では早速、説明してみる

 前回は、下記の SQLSQL Server Management Studio で実行しました。

SELECT [テーブル名] = o.name,
       [インデックスの深さ] = dips.index_depth,
       [インデックスレベル] = dips.index_level,
       [インデックスのタイプ] = dips.index_type_desc,
       [レコード数] = dips.record_count,
       [ページ数] = dips.page_count
FROM sys.dm_db_index_physical_stats ( DB_ID(),
                                      OBJECT_ID('[Sales].[SalesOrderHeader]'),
           NULL,
           NULL,
           'DETAILED') dips
JOIN sys.objects o on o.object_id =
 dips.object_id where dips.index_type_desc = 'CLUSTERED INDEX'

 これだけでは、階層の深さ、ページ数などしかわかりません。ページ間の関連などの情報やノード間の関係など詳細な情報はまったくわかりません。詳細な情報を得るには、undocumented なコマンドを使用する必要があります。

Undocumented なコマンドで、詳細を調査してみる

 インデックスの情報を調査するには、DBCC IND を使用します、と言いたいところですが、SQL Server 2012 より、DBCC IND の代替手段として、sys.dm_db_database_page_allocations が提供されています。しかし、この関数も今のところ undocumented です。

select database_id,
object_id,
index_id,
partition_id,
rowset_id,
allocation_unit_id,
allocation_unit_type,
allocation_unit_type_desc,
data_clone_id,
clone_state,
clone_state_desc,
extent_file_id,
extent_page_id,
allocated_page_iam_file_id,
allocated_page_iam_page_id,
allocated_page_file_id,
allocated_page_page_id,
is_allocated,
is_iam_page,
is_mixed_page_allocation,
page_free_space_percent,
page_type,
page_type_desc,
page_level,
next_page_file_id,
next_page_page_id,
previous_page_file_id,
previous_page_page_id,
is_page_compressed,
has_ghost_records
FROM sys.dm_db_database_page_allocations( DB_ID(),
OBJECT_ID('Sales.SalesOrderHeader'),
-1,
NULL,
'DETAILED')

f:id:koogucc11:20140816144511p:plain

 図1:sys.dm_db_database_page_allocations の実行結果

 DBCC IND で得られる結果より、多くの情報が得られるようです。よくわからない列もありますね。インデックス ( B+Tree ) の構造を得るために、最小限の列に絞ってみます。

select [ページID] = allocated_page_page_id,
[ページタイプ] = page_type,
[ページタイプ詳細] = page_type_desc,
[ページレベル(Index_Level)] = page_level,
[次ページのページID] = next_page_page_id,
[前ページのページID] = previous_page_page_id
FROM sys.dm_db_database_page_allocations( DB_ID(),
OBJECT_ID('Sales.SalesOrderHeader'),
NULL,
NULL,
'DETAILED')
Where index_id =1

f:id:koogucc11:20140817093046p:plain

図2: sys.dm_db_database_page_allocations の実行結果

  図3 から ページID:6858 がルートページであることが判断できます。

f:id:koogucc11:20140817114643p:plain

図3:ルートページ

 ルートページ( ページID:6858 ) の詳細下記のコマンドを SQL Server Management Studio で実行します。図4の結果から、中間ノードは、ページID:6856,6857だということが判断できます。また、SalesOrderID の分布は、72338未満は ページID:6856で、72338以上はページID:6857 に格納されていることが判断できます。

DBCC TRACEON(3604)
DBCC PAGE(N'AdventureWorks2012',1,6858,3) WITH TABLERESULTS
DBCC TRACEOFF(3604)

f:id:koogucc11:20140817115914p:plain

図4:DBCC PAGE の実行結果

 ページID:6856,6857 に同様のコマンドを実行してみましょう。まず、ページID:6856 を実行してみます。

DBCC TRACEON(3604)
DBCC PAGE(N'AdventureWorks2012',1,6856,3) WITH TABLERESULTS
DBCC TRACEOFF(3604)

f:id:koogucc11:20140817130152p:plain

図5:ページID:6856の実行結果 

 ページID:6857 を実行してみます。

DBCC TRACEON(3604)
DBCC PAGE(N'AdventureWorks2012',1,6857,3) WITH TABLERESULTS
DBCC TRACEOFF(3604)

f:id:koogucc11:20140817130226p:plain

図6:ページID:6856の実行結果

 これらの結果を元に、SalesOrderHeader テーブルのクラスタ化インデックスを図示すると図7のようになります。

f:id:koogucc11:20140817235011p:plain

 図7:SalesOrderHeader のクラスタ化インデックス

検索時に、どんな感じでB+Tree が使われるのか説明してみる

 下記のクエリを発行してみましょう。

SELECT * FROM Sales.SalesOrderHeader Where SalesOrderID = 43800

 各ページの検索方法は、図8のようになります。

f:id:koogucc11:20140817234846p:plain

 図8:インデックスの検索方法

 図8の内容を詳細に説明してみましょう。ページID:6858 の内容は図9の通りです。Where 句 に指定された SalesOrderID と SalesOrder(key)  を比較します。43800は、72338未満であるため、ページID:6856 が選択されます。

f:id:koogucc11:20140817154526p:plain

図9:ページID:6858のページ内容

 ページID:6856 の内容は図10の通りです。Where 句 に指定された SalesOrderID と SalesOrder(key)  を比較します。43800は、43794以上43841未満であるため、ページID:6827に選択されます。

f:id:koogucc11:20140817155122p:plain

図10:ページID:6856のページ内容

 ページID:6827 の内容は図11の通りです。実際にデータが含まれているのが確認できます。

f:id:koogucc11:20140817155542p:plain

図11:ページID:6827のページ内容

次回何をするのかまたまた宣言してみる

 今回までの説明で、インデックスがどのような構造なのかどのように使用されるのかは大体理解できたかと思います。次回はページに関して詳しく説明したいと思います。