そろそろ SQL Server 2014 CTP1 の目玉機能である Hekaton (Memory-optimized tables) を紹介してみたいと思います。今回は概要レベルでの機能紹介を。
BOL では In-Memory OLTP (In-Memory Optimization) に記載されています。
Database Watch（2013年6月版） でも紹介されていましたので日本語の情報としてはこちらが参考になるかと。
SQL Server MVP のおださんが db tech showcase 大阪2013 Hekaton セッションメモ で大阪で開催されたセッションのメモを公開してくださっているのでこちらも勉強になります。
SQL Server Blog でも紹介されていますのでこちらも合わせてチェックしておきたいですね。
SQL Server 2014 In-Memory Technologies: Blog Series Introduction
Getting Started with SQL Server 2014 In-Memory OLTP
ホワイトペーパーはこちらに
SQL Server In-Memory OLTP Internals Overview for CTP1
SQL Server 2014 では In-Memory OLTP の機能として、Hekaton と呼ばれていた機能が Memory-Optimized Tables が搭載されています。
使用するためにはいくつかの手順がありますので今回の機能紹介では使用できるまでの流れを紹介したいと思います。
Contents
■MEMORY OPTIMIZED DATA 用のファイルグループを作成
最初に MEMORY OPTIMIZED DATA 用のファイルグループを作成する必要があります。
In-Memory OLTP ではログファイルは通常のデータベースのものと共有する形となりますが、データ領域に関しては専用のファイルグループにファイルストリームベースで格納をしていきます。
データベースのプロパティに [MEMORY OPTIMIZED DATA] のファイルグループがありますので、最初にファイルグループを設定します。
ファイルグループを作成したら、データベースの領域として作成したファイルグループを指定します。
# この際にどこのディレクトリにファイルを置くかを指定します。
| USE [master] GO ALTER DATABASE [TEST] ADD FILEGROUP [InMemoryFG] CONTAINS MEMORY_OPTIMIZED_DATA GO ALTER DATABASE [TEST] ADD FILE ( NAME = N’TEST_Inmemory’, FILENAME = N’E:InMemoryTEST_Inmemory’ ) TO FILEGROUP [InMemoryFG] GO |
設定したファイルグループには以下のようにファイルが格納されます。
# Hekaton のデータを担保するための Data File と Delta File が格納されます。
■Memory Optimized Table の作成
ファイルグループの作成ができたら Memory Optimized Table を作成します。
Memory Optimized Table の作成はテーブルを右クリックして [New] から作成することができます。
現状は GUI を使用してテーブルは作成できないため、テーブルを作成するためのテンプレートが表示される形となっています。
細かな構文の説明はほかの投稿にしようと思いますので今回は基本的な構文の紹介を。
Memory Optimized Table は以下のようなクエリでテーブルを作成します。
# CREATE TABLE (SQL Server) に Memory Optimized Table 用の構文が記載されています。
| USE TEST GO–Drop table if it already exists. IF OBJECT_ID(‘dbo.MemTable’,’U’) IS NOT NULL DROP TABLE dbo.MemTable GO CREATE TABLE dbo.MemTable ( Col1 int NOT NULL, Col2 int INDEX IX_MemTable_Col2 HASH (Col2) WITH (BUCKET_COUNT = 1024) NOT NULL, Col3 nvarchar(100) COLLATE Japanese_XJIS_100_BIN2 NOT NULL, Col4 char(100) COLLATE Latin1_General_100_BIN2 ?? CONSTRAINT PK_MemTable PRIMARY KEY NONCLUSTERED HASH (Col1) WITH (BUCKET_COUNT = 1024), INDEX IX_MemTable_Col3_2 HASH (Col3,Col2) WITH (BUCKET_COUNT = 1024) ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA) GO |
使用できるデータ型には制限があり文字列型をハッシュインデックスに含める場合には照合順序にも注意する必要があります。
# インデックスに含める文字列には BIN2 の照合順序を設定、非 Unicode 文字列の場合はnon-1252 code pageはサポートされないという点もあります。
Memory Optimized Table のインデックスはハッシュインデックスを使用しており、ハッシュインデックスを格納するためのバケットカウント (1 バケット = 8 バイト) を指定する必要があります。
# バケットカウントは任意の数値を設定できますが、内部的には 2 の累乗に切り上げられるようです。
DURABILITY を SCHEMA_ONLY にすることでデータの永続化を行わないテーブルとしても作成することができます。
テーブルの作成は以上で完了です。
Memory Optimize Table は作成後のテーブル定義の変更はインデックスの追加を含めできないようですので、テーブル定義を変更する場合には再作成する必要があるのも注意点かと。
■T-SQL を使用した操作
Memory Optimized Table は通常の T-SQL (Transact SQL) を使用して操作することが可能です。
| SET NOCOUNT ON GO DELETE FROM MemTable GODECLARE @i int SELECT @i = (SELECT COUNT(*) FROM MemTable) WHILE (@i <= 10000) BEGIN INSERT INTO MemTable VALUES(@i, @i+ 1, NEWID(),NEWID()) SET @i += 1 END GO UPDATE MemTable SET Col2 = Col1 + 2 GO SELECT TOP 100 * FROM MemTable GO |
実際の実行結果がこちらです。
Memory Optimized Table は通常のインデックス構造のテーブルのように B-Tree では管理されていません。
また、クラスター化インデックスのような列の格納順序を制御するようなものはありませんので、特定の列で並びかれられたデータが必要となる場合には ORDER BY 句が必須かと。
Truncate Table は機能的な制約として実行することはできませんが。
■ネイティブコンパイルされたストアドプロシージャ
先ほどは T-SQL で操作をしましたが Memory Optimized Table で最適なパフォーマンスを出すためにネイティブコンパイルされたストアドプロシージャを使用することができます。
CREATE PROCEDURE (Transact-SQL) にネイティブコンパイルされたストアドプロシージャの構文が記載されています。
作成する際にはいろいろと制約があるのですが、先ほど使っていた T-SQL のクエリを以下のようにしてネイティブコンパイルされたストアドプロシージャとして作成してみます。
# NEWID が使用できない、文字列で使用できるコードページや型変換の制約で、文字列の格納は簡略化してしまっています。
| CREATE PROCEDURE usp_NativeComplieProc WITH EXECUTE AS OWNER, SCHEMABINDING, NATIVE_COMPILATION AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N’us_english’)DELETE FROM dbo.MemTable DECLARE @i int SELECT @i = COUNT(*) FROM dbo.MemTable WHILE (@i <= 10000) BEGIN INSERT INTO dbo.MemTable VALUES(@i, @i+ 1 , N’TEST’ ,NULL) SET @i += 1 END UPDATE dbo.MemTable SET Col2 = Col1 + 2 SELECT TOP 100 Col1,Col2,Col3,Col4 FROM dbo.MemTable ORDER BY Col1 END GO |
実行するとネイティブコンパイルされたストアドプロシージャが作成されます。
今回のクエリは単純なものなのでそれほど速度差は出ませんが、それでもネイティブコンパイルされたストアドプロシージャのほうが早めに実行できると思います。
■ロックモデル
Memory Optimized Table? はロック / ラッチフリーのモデルが採用されており、更新系の処理は楽観的ロックにより制御が行われています。
# 基本のトランザクションモデルも SNAPSHOT となります。
更新系の処理が競合した際の動作として、以下のクエリを複数のセッションで実行してみます。
| BEGIN TRAN UPDATE MemTable WITH (SNAPSHOT) SET Col3 = ‘TEST2’ WHERE Col1 = 1 |
SQL Server のデフォルトの分離レベルは [READ COMMITTED] ですが、Memory Optimized Table でサポートされているのは、SNAPSHOT / REPEATABLE READ / SERIALIZABLE になり、デフォルトの状態ですと以下のエラーになるため、SNAPSHOT を設定しています。
| Msg 41368, Level 16, State 0, Line 2 Accessing memory optimized tables using the READ COMMITTED isolation level is supported only for autocommit transactions. It is not supported for explicit or implicit transactions. Provide a supported isolation level for the memory optimized table using a table hint, such as WITH (SNAPSHOT). |
この場合、二つ目のセッションで実行したクエリでは以下のようなエラーが発生します。
| Msg 41302, Level 16, State 110, Line 2 The current transaction attempted to update a record that has been updated since this transaction started. The transaction was aborted. Msg 3998, Level 16, State 1, Line 1 Uncommittable transaction is detected at the end of the batch. The transaction is rolled back. The statement has been terminated. |
Memory Optimized Table では、後から更新されたものに関してはエラーとなります。
以前、勉強会で Hekaton のお話を聞いた際には、このあたりの仕組みは Check&Update というチップセット命令を使用して更新をした際に、自分の更新が状態として適切かを確認したうえで変更するようになっているとのことでした。
■ネイティブコンパイル
Hekaton の Memory Optimized Table と Native Complie されたストアドプロシージャは自動で C のソースが作成されコンパイルされます。
自動生成されたコードは確認することができ、SQL Server のデータベースのデータファイルのデフォルトの格納ディレクトリの配下の xtp ディレクトリの内容で確認することができます。
Memory Optimize Table と Native Compile Stored Procedure に関しては自動でネイティブコードが生成されて最適化されているのでしょうね。
テーブルに関しては作成時やサービスの起動時、ストアドプロシージャに関しては初回実行時にコンパイルが行われていそうです。
初回コンパイルのオーバーヘッドだけは意識しておくとよさそうですね。
データの構造やファイル書き込みなどまだまだ書きたいことはたくさんありますが、今回は概要ということでこのあたりで。