本記事はFIXERが提供する「cloud.config Tech Blog」に掲載された「SQL ServerでインデックスのB-Tree構造を確認する方法」を再編集したものです。
こんにちは、あおいです。 そろそろ、QQQやSPYDあたりのETFの買い増しでもしようかしら。
さて、SQL Serverにおけるのデータ構造のアーキテクチャーって知ってますか？
以下の記事に分かりやすい概略図が記されています。 SQL Serverにおけるインデックスの再構成と再構築の性能比較 | ZOZO TECH BLOG
テーブルは、ひとつ以上のインデックスから構成されます。インデックスは、ページというデータの集合体でB-Tree構造を形成します。ページとは、8KBの物理的な連続領域で各ページに実際のレコードが格納されています。
SQL Serverにおけるインデックスの再構成と再構築の性能比較 | ZOZO TECH BLOG
と言っても、なんかイメージつきにくいですよね。分かる人にはもう分かると思いますが。 実際のデータの中身を覗けば、イメージがかなり湧くと思います。
そこで、今回はSQL ServerでインデックスのB-Tree構造を確認する方法を紹介したいと思います。
まず、任意のデータベース名とテーブル名を指定した上で、以下のクエリを実行してみてください。
-- インデックスの構造を確認するクエリ
DECLARE @DB_ID int, @Object_ID int
set @DB_ID = DB_ID('データベース名')
set @Object_ID = OBJECT_ID('テーブル名')
SELECT
name, index_id, index_type_desc, index_depth, index_level, page_count, record_count, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (@DB_ID, @Object_ID, NULL , NULL, 'DETAILED') as A
JOIN sys.objects as B with(nolock) on A.object_id = B.object_id
ORDER BY index_id, index_level DESC
インデックスの各階層ごとにページ数やレコード数を取得しています。 今回の場合、クラスター化インデックスが3階層の構造でレコード数が全件5万となっています。
次に、以下のDBCC INDコマンドを実行して、ページの連続性の確認を確認してみます。
-- ツリー構造を確認する -- インデックスで使用しているページIDリストを取得 DBCC IND(データベース名, テーブル名, 1)
全てのページ情報を取得しています。 また、index_levelを確認すると、root(2)ノードのデータはひとつのみで、大半はleaf(0)ノードのデータであることが分かります。
最後に、各階層ごとのページ情報に対してPageIDを指定して確認してみます。DBCC PAGEでページ情報をダンプするには、トレースフラグ 3604 を有効にする必要があります。
-- ツリー構造を確認する -- 各階層ごとのページ情報を取得 DBCC TRACEON(3604) -- rootノードのページ DBCC PAGE(データベース名, 1, ［PageID］, 3) -- branchノードのページ DBCC PAGE(データベース名, 1, ［PageID］, 3) -- leafノードのページ DBCC PAGE(データベース名, 1, ［PageID］, 3) WITH TABLERESULTS DBCC TRACEOFF(3604)
Levelがindex_levelの各階層を示しており、ChildPageIDをもとに各階層をたどると、最も階層が低いリーフノードに実データが格納されていることが分かります。
また、実際にページ情報のダンプ結果とSELECT結果を照らし合わせてみると、レコード情報が一致します。
今回はSQL ServerでインデックスのB-Tree構造を確認する方法を紹介させていただきました。実際に、中身のデータを覗くことで、冒頭で紹介したインデックスのアーキテクチャーの概略図やB-Tree構造の仕組みがイメージしやすくなったかと思います。本記事が少しでも読者の皆様のお役に立てれば幸いです。
あおい／FIXER
「初心者の方にも分かりやすく」をモットーにブログ執筆。TWICEミナペン。普段はC#とSQL Server、たまにPower Platformを触ってます。書籍『Microsoft Power Platformローコード開発[活用]入門 - 現場で使える業務アプリのレシピ集』
