SQL Server 2012 CTP2 では、範囲検索に使用できるレンジインデックスが使用できるようになりました。
細かな仕様までは調べられていないのですが、通常のハッシュインデックスと比較して、どの程度メモリ使用量に差が出るのかを見てみました。
基本的なガイドラインに関しては Guidelines for Using Indexes on Memory-Optimized Tables に。
CTP2 のメモリ最適化テーブル (Memory Optimized Table : Hekaton) ですが、2 種類のインデックスを利用することができます。
There are two types of memory-optimized indexes:
Nonclustered hash indexes, which are made for point lookups. For more information about hash indexes, see Memory-Optimized Tables and Indexes.
Nonclustered indexes, which are made for range scans and ordered scans.
非クラスター化ハッシュインデックスは特定データをピンポイントで参照するときには最適なインデックスとなり、日クラスター化インデックスは範囲検索やオーダー検索に適しているものとなってきます。
# 適切なソート順を指定していればソートコストを抑えられそうですね。
テーブル作成時の定義としては以下のような形ですね。
非クラスター化ハッシュインデックス
CREATE TABLE MemTable ( c1 nvarchar(36) COLLATE Japanese_XJIS_100_BIN2 NOT NULL, c2 nvarchar(36) COLLATE Japanese_XJIS_100_BIN2 NOT NULL, c3 nvarchar(36) COLLATE Japanese_XJIS_100_BIN2 NOT NULL, INDEX IX_MemTable_c1 NONCLUSTERED HASH (c1) WITH (BUCKET_COUNT= 1048576) ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY) GO
非クラスター化インデックス
CREATE TABLE MemTable ( c1 nvarchar(36) COLLATE Japanese_XJIS_100_BIN2 NOT NULL, c2 nvarchar(36) COLLATE Japanese_XJIS_100_BIN2 NOT NULL, c3 nvarchar(36) COLLATE Japanese_XJIS_100_BIN2 NOT NULL, INDEX IX_MemTable_c1 NONCLUSTERED (c1 ASC) ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY) GO
ハッシュインデックスの場合にはバケットを指定しますが、ハッシュインデックスを使わない場合は、バケットの指定が不要となります。
また、ハッシュインデックスの場合は、列のソート順を指定することができませんでしたが、ハッシュインデックスでない場合には列のソート順を指定することができるようになります。
これらのインデックスでは構成がかなり違っているようでメモリの消費量が変わってきます。
ハッシュインデックスの場合にはバケットカウントによって、インデックスの消費メモリが変わってきます。
バケットカウントに 1,048,576 を指定した場合、1 バケットは 8 バイトですので、
- 1,048,576 × 8 = 8,388,608 = 8MB
となります。
メモリの使用量等は以下の DMV から確認ができます。
SELECT * FROM sys.dm_db_xtp_hash_index_stats WHERE object_id = OBJECT_ID(N'MemTable') SELECT * FROM sys.dm_db_xtp_index_stats WHERE object_id = OBJECT_ID(N'MemTable') SELECT * FROM sys.dm_db_xtp_memory_consumers WHERE object_id = OBJECT_ID(N'MemTable') SELECT * FROM sys.dm_db_xtp_table_memory_stats WHERE object_id = OBJECT_ID(N'MemTable')
結果がこちら。
ハッシュインデックスの場合はテーブル作成時に指定したバケットカウントによってインデックスのサイズが決まりますので、200,000 件 (216 Bytes/Record) のデータを入れても、インデックスのメモリ使用量は (memory_used_by_indexs_kb) は 8,192KB (8MB) となっています。
それでは、同様のレコードを非クラスター化インデックスで試してみます。
こちらの場合は、 20,860KB (20MB) となっています。
今回のレコードですが 1 列は 72 バイトですので、レコード数で単純に考えると、14,400,000 インデックスが設定されている列に対して、14MB 程度は使っていることになります。
非クラスター化インデックスの場合は、列に対してのデータサイズは意識したほうがよさそうな感じですね。
先ほどは 1 列に対して設定をしていましたが 2 列含めるようにしてみます。
CREATE TABLE MemTable ( c1 nvarchar(36) COLLATE Japanese_XJIS_100_BIN2 NOT NULL, c2 nvarchar(36) COLLATE Japanese_XJIS_100_BIN2 NOT NULL, c3 nvarchar(36) COLLATE Japanese_XJIS_100_BIN2 NOT NULL, INDEX IX_MemTable_c1 NONCLUSTERED HASH (c1, c2) WITH (BUCKET_COUNT= 1048576) ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY) GO CREATE TABLE MemTable ( c1 nvarchar(36) COLLATE Japanese_XJIS_100_BIN2 NOT NULL, c2 nvarchar(36) COLLATE Japanese_XJIS_100_BIN2 NOT NULL, c3 nvarchar(36) COLLATE Japanese_XJIS_100_BIN2 NOT NULL, INDEX IX_MemTable_c1 NONCLUSTERED (c1 ASC, c2 ASC) ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY) GO
ハッシュインデックスについては列数ではなくバケットカウントが影響するので先ほどとサイズは変わりません。
非クラスター化インデックスの場合は、38,996KB (39MB) となり倍程度になっていますね。
このことから設定する列の数の影響を受けていそうです。
Hekaton はロック / ラッチフリーモデルなので、単純な B-tree 構造ではないと思いますが。
非クラスター化インデックスしかないメモリ最適化テーブルとディスクベースのヒープテーブルで同様のクエリを実行した場合の実行プランの違いがこちらです。
ヒープの場合は、インデックスに含まれていない項目を参照しているので RID Lookup が発生していますが、メモリ最適化テーブルの場合は発生していませんでした。
どの様なインデックス構造になっているか、興味がわきますね。