Memory Optimized Table を作成する際には以下のようなクエリでテーブルを作成します。
# CREATE TABLE のサンプルは Creating a Memory Optimized Table に記載されています。
| CREATE TABLE MemTable ( Col1 int NOT NULL, Col2 nchar(450) COLLATE Japanese_XJIS_100_BIN2 NOT NULL INDEX NCIX_MemTable_col2 HASH (Col2) WITH (BUCKET_COUNT = 1024), Col3 int, CONSTRAINT PK_MemTable PRIMARY KEY NONCLUSTERED HASH (Col1) WITH (BUCKET_COUNT = 100) ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA) GO |
Memory Optimized Table を使用するためには WITH 句 で指定をする必要があります。
WITH 句の詳細については CREATE TABLE (SQL Server) に記載されています。
レコードは 8,060 バイトにする必要があり、LOB は使用することができません。
また、文字列をインデックスの列に含めるときは BIN2 を設定する必要があり、非 Unicode 文字列の場合は CP 1252 (Latin1_General_100_BIN2 のような設定) を使用する必要があります。
# 照合順序はインデックスに含める際ということをご指摘いただきその通りでしたm(_ _)m ネイティブコンパイルストアドの場合も意識する必要があるようでしたので、この辺はきちんと理解しないといけなさそうです。
インデックスについては PRIMARY KEY を含め最大で 8 個のインデックスを設定することができ、インデックスのキー項目のサイズは 900 バイト以内に設定をする必要があります。
また、インデックスを設定する列には NOT NULL を付与する必要があります。
# NULL 許容列に対してのインデックスがサポートされていません。
テーブルの制約については、Supported Data Types や SQL Server In-Memory OLTP Internals Overview for CTP1 を確認するとまとまっています。
テーブル作成の最大の特徴としては 3 点あるかなと思っており、1 点目が BUCKET_COUNT です。
# 詳細は Memory-Optimized Tables and Indexes に記載されています。
Memory Optimized Table ではハッシュインデックスによりインデックスが作成されます。
そのインデックスはバケットに格納されるため、ハッシュインデックスの作成時にはバケットのサイズ (BUCKET_COUNT) を指定する必要があります。
バケットのサイズは格納可能なインデックス領域となるかと思いますので、この辺は性能にも影響してくるかと思います。
1 バケットで 8 バイト使用され、設定した値は 2 の累乗に切り上げられます。
1 バケットは 8 バイトのメモリが消費され、バケットカウントを高くするとメモリの消費量が多くなります。
Memory Optimized Table の行ヘッダにも 8 バイト × インデックス数の領域が確保されるため 8 バイトがポインタのサイズとなっているのでしょうね。
# 複数列をインデックスに設定してもバケットは 8 バイトとなるようです。
バケットカウントを 1,000,000 で設定してテーブルを作成したとします。
| CREATE TABLE MemTable ( Col1 int NOT NULL, Col2 nchar(450) COLLATE Japanese_XJIS_100_BIN2 NOT NULL, Col3 int NOT NULL, CONSTRAINT PK_MemTable PRIMARY KEY NONCLUSTERED HASH (Col1) WITH (BUCKET_COUNT = 1000000) ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA) GO |
この場合は 2 累乗に切り上げられ、1,048,576 が設定され、1,048,576 * 8 Byte = 8MB の領域が確保されることになります。
それでは実際に確認をしてみたいと思います。
ハッシュインデックスの情報に関しては sys.dm_db_xtp_hash_index_stats から確認をすることができます。
以下が取得した結果になります。
設定した値から切り上げられていることが確認できますね。
バケットカウントの残りの状態も確認ができるようになっており、データを追加するとバケットカウントは減っていきます。
1,000,000 行 INSERT した際の空き状況がこちらです。
922,572 バケット消費されていることが確認できます。追加した行数と完全に一致ではありませんが、近い値は使用されているようですね。この辺は検索時の速度にも影響してくると思いますのでチューニングのポイントになりそうです。
次にバケットで使用しているメモリサイズを見ていきたいと思います。
今回のバケットの設定であれば、8 MB メモリが消費されていることになります。
これを確認するためには sys.dm_db_xtp_table_memory_stats を使用します。
この Indexes 関連の列を確認することで、どれだけのメモリをハッシュインデックスで消費しているかを確認することができます。
以下はデータは入っていない状態ですが、バケットカウント分のメモリ (8MB) が確保されていることが確認できますね。不必要にサイズを大きくするとデータが未使用でもメモリが確保されることになりますのでサイズに関してはどれだけのデータを格納するかとの兼ね合いで考慮する必要が出てくるかと。
この情報はテーブル単位での取得となりますので、複数のインデックスを設定した場合には合算値として集計されることになります。
以下はバケットのサイズを 1,000,000 にしたインデックスを作成した状態のものになりますが、8MB × 2 で 16MB 確保されていることが確認できます。
個別に取得したい場合には sys.dm_db_xtp_memory_consumers を使用する必要がありそうです。
これらの DMV はデータが使用しているメモリの使用状況を確認することもできますので Memory Optimized Table でどれだけのメモリを使用しているかを確認する際には頻繁に使いそうですね。
なお、SSMS の標準レポートとして [Memory Usage By Memory Optimized Objects] が追加されており、メモリの使用状況に関してはこちらからも確認できます。
2 点目の特徴としては永続化 (DURABILITY) の設定でしょうか。
Memory Optimized Table では永続化するデータの指定が SCHEMA_AND_DATA と SCHEMA_ONLY の 2 種類から選択することができるようになっています。
SCHEMA_AND_DATA はスキーマとデータを永続化するための設定となり、この際書き込まれた内容はログやチェックポイントファイルのデータファイルに書き込みが行われます。
SCHEMA_ONLY の場合にはスキーマの情報は永続化されますがデータに関してはメモリのみに書き込みが行われチェックポイントファイルには書き込まれません。
そのため、データに関してはサービスの再起動とともに消える非永続化されたデータとなります。
単純な速度に関してはログやチェックポイントファイルに書き込みが行われない分 SCHEMA_ONLY のほうが高速です。
# SCHEMA_AND_DATA で COMMIT をしないで書き込んでいるときと同等の速度になっているかと。
この辺はデータの特性によって使い分けていく必要が出てきそうですね。
Memory Optimized Table をセッション DB に使う際に永続化しないで高速に処理をしたり、他のテーブル内のデータを一時的にキャッシュさせたい時などは SCHEMA_ONLY のテーブルを使うとよさそうですね。
# tempdb に一時テーブルを作るのではなく Memory Optimized Table を一時テーブルとして使うというようなシナリオも出てくるのでしょうか。
3 点目の特徴としてはテーブル定義の変更になります。
Memory Optimized Table では ALTER TABLE がサポートされていません。
テーブルの定義を変更しようとすると以下のエラーになります。
| Msg 10770, Level 16, State 16, Line 14 The operation ‘ALTER TABLE’ is not supported with memory optimized tables. |
Memory Optimized Table ではテーブル定義の変更をする場合には、テーブルを再作成する必要があります。
このテーブル定義にはインデックスも含まれているため、インデックスの追加 / 変更を後から実施することはできません。インデックスの設定はテーブル作成時に実施する必要があります。
CREATE INDEX (Transact-SQL) や ALTER INDEX (Transact-SQL) ではハッシュインデックスの操作がサポートされていません。
1 点目の特徴として紹介したバケットのサイズに関しても後から変更することができませんので最初に格納されるデータを見越して設定する必要があります。
また、レスポンスが悪化したからと言ってインデックスを追加することができませんので、テーブル定義を作成する段階でどの列を使用して検索を行うのかを十分に検討する必要があります。
Memory Optimized Table を使う場合には初期の定義が重要となってきますので、十分に吟味をしたうえで作成するのがポイントとなりそうですね。
いちからはじめる Memory Optimized Table その 1
いちからはじめる Memory Optimized Table その 2
いちからはじめる Memory Optimized Table その 3 ←今回の投稿