SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server 2016 CTP 2.1 の In-Memory OLTP を使ってみる

leave a comment

SQL Server 2016 では In-Memory OLTP (Hekaton) についても様々な機能強化が行われています。
詳細については In-Memory OLTP を参照していただければと。

ざっくりとではありますが機能拡張で簡単に障れる箇所を見てみたいと思います。

■パフォーマンス面


詳細は Scalability / Configuring Storage for Memory-Optimized Tables で。

In-Memory OLTP は SQL Server 2014 で実装されたものですが、メモリ最適化テーブルを使用するための要件 / メモリ最適化ファイル グループ には以下のような記載があります。

  • データベース内の持続性のあるすべてのテーブルのメモリ内サイズの合計は 250 GB を超えないようにする必要があります。
  • このリリースのインメモリ OLTP は、2 または 4 ソケットおよび 60 未満のコアを備えたシステムで適切に機能するように設定されています。
  • メモリ最適化ファイル グループは FILESTREAM ファイル グループに基づいていますが、次の違いがあります。

SQL Server 2014 では各データベースの持続性のあるテーブルの最大サイズは 256 GB程度が上限だったのですが、これが 2TB に拡張されており、メモリ最適化テーブルに格納できるデータサイズが大幅に拡張されています。

また、CPU についても 2 または 4 ソケットの 60 未満の CPU コアの環境での動作を想定しての動作となっていました。
SQL Server 2014 では、オフラインチェックポイントスレッドが 1 つの生成されており、これがスケールのネックになっていたようなのですが、SQL Server 2016 ではメモリ最適化ファイルグループのコンテナー単位で、オフラインチェックポイントスレッドが作成されるようになりスケールのネックがなくなっているようです。

また、ストレージの管理が FILESTREAM の仕組みからは分離されるようになりました。
SQL Server 2014 では、$FSOG / GUID のフォルダーに対して永続化ファイルの作成が行われていましたが、SQL Server 2016 では、$HKv2 というフォルダーに対して、HKCKP ファイルで生成が行われるようになっており、従来の仕組みからの変化が確認できます。

 

■TDE のサポート


SQL Server 2014 では、メモリ最適化テーブルが作成されているデータベースに対して、透過的データ暗号化 (TDE) を設定することはできたのですが、以下のようなメッセージが表示されていました。

警告: DURABILITY を SCHEMA_AND_DATA とするメモリ最適化テーブルを少なくとも 1 つ含んだデータベースに対して暗号化が有効にされました。これらのメモリ最適化テーブル内のデータは暗号化されません。

SQL Server 2016 では、永続データのチェックポイントファイルも対象として TDE の設定ができるようになり、上記のメッセージは表示されなくなりました。

 

■ALTER のサポート


詳細は Altering Memory-Optimized Tables / Altering Natively Compiled Stored Procedures に記載されていますが、メモリ最適化テーブルとネイティブコンパイルストアドプロシージャで ALTER 文がサポートされるようになりました。

これにより定義変更が以前より柔軟に実施できるようになります。
# SQL Server 2014 では定義変更は削除して再作成する必要があったため、テーブルに対して実行する場合はデータの対比を検討する必要がありました。

SQL Server 2016 では以下のような ALTER 文がサポートされるようになりました。

USE DemoDB
GO
IF OBJECT_ID('dbo.sample_memoryoptimizedtable','U') IS NOT NULL
    DROP TABLE dbo.sample_memoryoptimizedtable
GO
CREATE TABLE dbo.sample_memoryoptimizedtable
(
	c1 int NOT NULL,
	c2 float NOT NULL,
	c3 decimal(10,2) NOT NULL INDEX index_sample_memoryoptimizedtable_c3 NONCLUSTERED (c3),
	c4 int
   CONSTRAINT PK_sample_memoryoptimizedtable PRIMARY KEY NONCLUSTERED (c1),
   INDEX hash_index_sample_memoryoptimizedtable_c2 HASH (c2) WITH (BUCKET_COUNT = 131072)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO
ALTER TABLE sample_memoryoptimizedtable ALTER COLUMN c4 bigint
ALTER TABLE sample_memoryoptimizedtable ADD c5 int
ALTER TABLE sample_memoryoptimizedtable ALTER INDEX hash_index_sample_memoryoptimizedtable_c2 REBUILD WITH (BUCKET_COUNT = 200000)
ALTER TABLE sample_memoryoptimizedtable  ADD INDEX nonclustered_c2 (c2, c3)

 

こちらはテーブル作成後の定義変更ですが、列型や新規の列が追加できるようになっています。

また、インデックスの設定もできるようになっており、ハッシュインデックスのバケットカウントの変更や非クラスター化インデックスを追加することができます。

# 現状、ハッシュインデックスの追加が出来なさそうでしたが。

ネイティブコンパイルされたストアドプロシージャについても ALTER で定義変更が可能です。

USE DemoDB
GO
IF OBJECT_ID('dbo.Procedure_Name','P') IS NOT NULL
   DROP PROCEDURE dbo.Procedure_Name
GO
ALTER PROCEDURE dbo.Procedure_Name
	@p1 int = 0,
	@p2 int = 0
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(
 TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'Japanese'
)
 SELECT @p1, @p2
END
GO
EXECUTE dbo.Procedure_Name 1, 2
GO
ALTER PROCEDURE dbo.Procedure_Name
	@p1 int = 0,
	@p2 nchar = 0
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(
 TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'Japanese'
)
 SELECT @p1, @p2
END
GO

今までの取り回しの難しさがこれで少しは緩和されるかもしれないですね。

 

■MARS のサポート


サポートされるようになったようです。

Using Multiple Active Result Sets (MARS)

使ってないので紹介のみで。

 

■ネイティブコンパイルストアドプロシージャ内でユーザー定義関数の呼び出し


詳細は Determining if a Table or Stored Procedure Should Be Ported to In-Memory OLTP で。

以下のようなネイティブコンパイルストアドプロシージャを作成しようとしてみます。

USE DemoDB
GO
IF OBJECT_ID('NC_PROC1','P') IS NOT NULL
   DROP PROCEDURE NC_PROC1
GO
IF OBJECT_ID('FN_Add','FN') IS NOT NULL
	DROP FUNCTION FN_Add
GO
CREATE FUNCTION dbo.FN_Add
(
	@p1 int
)
RETURNS int
WITH SCHEMABINDING
AS
BEGIN
	RETURN @p1 + 1
END
GO
CREATE PROCEDURE dbo.NC_PROC1
	@p1 nvarchar(100)
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(
 TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'Japanese'
)
 SELECT dbo.FN_Add(@p1)
END
GO
EXEC dbo.NC_PROC1 1

 

SQL Server 2014 では、ネイティブコンパイルストアドプロシージャ内に、ユーザー定義関数を使用することができませんでした。

メッセージ 12313、レベル 16、状態 65、プロシージャ NC_PROC1、行 33

ユーザー定義関数は、ネイティブ コンパイル ストアド プロシージャ ではサポートされません。

SQL Server 2016 ではユーザー定義関数がサポートされるようになりしたので、上記のクエリを実行してもエラーにならなくなります。

 

■照合順序の制限の緩和


SQL Server 2016 ではメモリ最適化テーブルの照合順序の制限が緩和されています。

Collations and Code Pages

SQL Server 2014 では照合順序に以下のような制限がありました。

  • *_BIN2 照合順序を使用しない文字の列のインデックスは、メモリ最適化テーブルのインデックス ではサポートされていません。
  • コード ページが 1252 以外の照合順序を使用するデータ型 char(n) および varchar(n) は、メモリ最適化テーブル ではサポートされません。

そのため以下のようなテーブルは作成できませんでした。

IF OBJECT_ID('HekatonTest', 'U') IS NOT NULL
	DROP TABLE HekatonTest
CREATE TABLE dbo.HekatonTest(
	Col1 int NOT NULL PRIMARY KEY NONCLUSTERED ,
	Col2 nchar(79) COLLATE Latin1_General_100_CI_AS NOT NULL ,
	Col3 char(100) COLLATE Latin1_General_100_CI_AS NOT NULL ,
	INDEX ix_Col2 NONCLUSTERED(Col2)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO
IF OBJECT_ID('HekatonTest', 'U') IS NOT NULL
	DROP TABLE HekatonTest
CREATE TABLE dbo.HekatonTest(
	Col1 int NOT NULL PRIMARY KEY NONCLUSTERED ,
	Col2 nchar(79) COLLATE Latin1_General_BIN2 NOT NULL,
	Col3 char(100) COLLATE Japanese_XJIS_100_CI_AS NOT NULL,
	INDEX ix_Col2 NONCLUSTERED(Col2)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)

 

 

最初のテーブル作成では、nchar 型の c2 に対してインデックスを作成していますが、照合順序が「BIN2」ではなく「CI_AS」を使用しているためエラーとなります。

次のテーブル作成では、Col3 の文字型に対して、「Japansese」を指定していますが、文字列型では、「コードページ 1252」(Latin1) を使用する必要があるためこちらもエラーとなります。

SQL Server 2016 では、この照合順序の制限が緩和されているため、どちらの CREATE TABLE も実行することができます。

 

■ネイティブコンパイルされたストアドプロシージャの SQL サポートの拡張


詳細は Supported Constructs on Natively Compiled Stored Procedures から確認していただくと良いのですが、ネイティブコンパイルされたストアドプロシージャの SQL サポートの範囲が拡張されています。

2014 では OUTER JOIN や UNION がサポートされていなかったのですが、これらもサポートされるようになっています。

IF OBJECT_ID('NC_PROC1','P') IS NOT NULL
   DROP PROCEDURE NC_PROC1
GO
CREATE PROCEDURE dbo.NC_PROC1
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(
 TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'Japanese'
)
 SELECT 1
 UNION
 SELECT 2
END
GO
IF OBJECT_ID('NC_PROC1','P') IS NOT NULL
   DROP PROCEDURE NC_PROC1
GO
CREATE PROCEDURE dbo.NC_PROC1
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(
 TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'Japanese'
)
	SELECT Tmp.Col1,Tmp2.Col1 FROM (VALUES(1)) AS Tmp(Col1)
	LEFT JOIN
	(SELECT Col1 FROM (VALUES(1)) AS Tmp(Col1)) AS Tmp2
	ON
	Tmp.Col1 = Tmp2.Col1
END
GO

上記のストアドプロシージャの作成は SQL Server 2014 ではエラーになるのですが、SQL Server 2016 では作成することができます。

 

■並列クエリの実行


Scalability / ALTER DATABASE Compatibility Level (Transact-SQL) を確認していただくとよいかと思いますが、メモリ最適化テーブルでの並列クエリの実行がサポートされるようになりました。

これについては互換性レベルが 130 (SQL Server 2016) を指定しておく必要があるのですが以下のようなクエリでテストをすることができます。

IF OBJECT_ID('ORDERS', 'U') IS NOT NULL
	DROP TABLE ORDERS
CREATE TABLE [dbo].[ORDERS](
	[O_ORDERKEY] [int] NOT NULL PRIMARY KEY NONCLUSTERED ,
	[O_CUSTKEY] [int] NOT NULL,
	[O_ORDERSTATUS] [nchar](1) NOT NULL,
	[O_TOTALPRICE] [decimal](15, 2) NOT NULL,
	[O_ORDERDATE] [date] NOT NULL,
	[O_ORDERPRIORITY] [nchar](15) NOT NULL,
	[O_CLERK] [nchar](15) NOT NULL,
	[O_SHIPPRIORITY] [int] NOT NULL,
	[O_COMMENT] [nvarchar](79) NOT NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
USE [master]
GO
ALTER DATABASE [DemoDB] SET COMPATIBILITY_LEVEL = 120
GO
USE [DemoDB]
DBCC FREEPROCCACHE
GO
SELECT O_CUSTKEY,COUNT(*) FROM ORDERS GROUP BY O_CUSTKEY ORDER BY O_CUSTKEY DESC
USE [master]
GO
ALTER DATABASE [DemoDB] SET COMPATIBILITY_LEVEL = 130
GO
USE [DemoDB]
DBCC FREEPROCCACHE
GO
SELECT O_CUSTKEY,COUNT(*) FROM ORDERS GROUP BY O_CUSTKEY ORDER BY O_CUSTKEY DESC

 

上が互換性レベル 120 / 下が互換性レベル 130 のクエリですが、並列プランが使用されていることが確認できますね。

image

 

列ストアインデックス同様、In-Memory OLTP に関しても様々な拡張が行われており、2014 の時よりは使いやすくなっていますね。

Share

Written by Masayuki.Ozawa

7月 5th, 2015 at 6:21 pm

Posted in SQL Server

Tagged with ,

Leave a Reply