Memory-Optimized テーブル変数

2017-07-14

OLTP では、メモリ最適化テーブル (効率的なデータアクセス用) とネイティブコンパイルストアドプロシージャ (効率的なクエリ処理とビジネスロジック実行用) に加えて In-Memory、3 番目の種類のオブジェクト (メモリ最適化テーブル型) が導入されます。メモリ最適化テーブル型を使用して作成されたテーブル変数は、メモリ最適化テーブル変数です。

メモリ最適化テーブル変数は、ディスクベースのテーブル変数と比較して次の利点を提供します。

変数はメモリにのみ格納されます。メモリ最適化テーブルの種類では、メモリ最適化テーブルに使用されるのと同じメモリ最適化アルゴリズムとデータ構造が使用されるため、データアクセスの効率が向上します。特に、変数がネイティブコンパイルストアドプロシージャで使用される場合です。
メモリ最適化テーブル変数では、tempdb 使用率はありません。テーブル変数は tempdb に格納されず、tempdb 内のリソースは使用されません。

メモリ最適化テーブル変数の一般的な使用シナリオは次のとおりです。

ネイティブコンパイルストアドプロシージャでの中間結果の格納と、複数のクエリに基づく単一の結果セットの作成。
ネイティブコンパイルストアドプロシージャと解釈されたストアドプロシージャにテーブル値パラメーターを渡す。
ディスクベースのテーブル変数や、場合によってはストアドプロシージャ内でローカルな #temp テーブルを置き換える。これは、システムで tempdb の競合が多い場合に特に便利です。
テーブル変数を使用すると、ネイティブコンパイルストアドプロシージャのカーソルをシミュレートできます。これは、ネイティブコンパイルストアドプロシージャのサーフェス領域の制限を回避するのに役立ちます。

メモリ最適化テーブルと同様に、SQL Server はメモリ最適化テーブルの種類ごとに DLL を生成します。 (コンパイルは、メモリ最適化テーブル変数の作成に使用される場合ではなく、メモリ最適化テーブルの種類が作成されるときに呼び出されます)。この DLL には、インデックスにアクセスし、テーブル変数からデータを取得するための関数が含まれています。テーブル型に基づいてメモリ最適化テーブル変数を宣言すると、テーブルの種類に対応するテーブルとインデックス構造のインスタンスがユーザーセッションに作成されます。テーブル変数は、ディスクベースのテーブル変数と同じ方法で使用できます。テーブル変数に行を挿入、更新、削除したり、Transact-SQL クエリで変数を使用したりできます。また、テーブル値パラメーター (TVP) として、ネイティブコンパイルストアドプロシージャと解釈ストアドプロシージャに変数を渡すこともできます。

次の例は、AdventureWorks ベースの In-Memory OLTP サンプル (SQL Server 2014 In-Memory OLTP サンプル) のメモリ最適化テーブルの種類を示しています。

CREATE TYPE Sales.SalesOrderDetailType_inmem
   AS TABLE
(
   OrderQty         smallint   NOT NULL,
   ProductID        int        NOT NULL,

   SpecialOfferID   int        NOT NULL
      INDEX  IX_SpecialOfferID  NONCLUSTERED,

   LocalID          int        NOT NULL,

   INDEX IX_ProductID HASH (ProductID)
      WITH ( BUCKET_COUNT = 8 )
)
WITH ( MEMORY_OPTIMIZED = ON );

このサンプルでは、メモリ最適化テーブルの種類の構文はディスクベースのテーブルの種類に似ていますが、次の例外があります。

MEMORY_OPTIMIZED=ON は、テーブルの種類がメモリ最適化されていることを示します。
型には少なくとも 1 つのインデックスが必要です。メモリ最適化テーブルと同様に、ハッシュインデックスと非クラスター化インデックスを使用できます。

ハッシュインデックスの場合、バケット数は、予想される一意のインデックスキーの数の約 1 ~ 2 倍にする必要があります。詳細については、「ハッシュインデックスの正しいバケット数の決定」を参照してください。
メモリ最適化テーブルのデータ型と制約の制限は、メモリ最適化テーブルの種類にも適用されます。たとえば、SQL Server 2014 では既定の制約がサポートされていますが、check 制約はサポートされていません。

メモリ最適化テーブルと同様に、メモリ最適化されたテーブル変数

並列プランはサポートされません。
メモリに収まる必要があり、ディスクリソースを使用しないでください。

ディスクベースのテーブル変数は tempdb に存在します。メモリ最適化テーブル変数はユーザーデータベースに存在します (ただし、ストレージは消費されず、復旧されません)。

インライン構文を使用してメモリ最適化テーブル変数を作成することはできません。ディスクベースのテーブル変数とは異なり、最初に型を作成する必要があります。

テーブル値パラメーター

次のサンプルスクリプトは、メモリ最適化テーブル型 Sales.SalesOrderDetailType_inmemとしてのテーブル変数の宣言、変数への 3 つの行の挿入、および変数を TVP として Sales.usp_InsertSalesOrder_inmemに渡す方法を示しています。

DECLARE @od Sales.SalesOrderDetailType_inmem,  
  @SalesOrderID uniqueidentifier,  
  @DueDate datetime2 = SYSDATETIME()  
  
INSERT @od (LocalID, ProductID, OrderQty, SpecialOfferID) VALUES  
  (1, 888, 2, 1),  
  (2, 450, 13, 1),  
  (3, 841, 1, 1)  
  
EXEC Sales.usp_InsertSalesOrder_inmem  
  @SalesOrderID = @SalesOrderID,  
  @DueDate = @DueDate,  
 @OnlineOrderFlag = 1,  
  @SalesOrderDetails = @od

メモリ最適化テーブル型は、ストアドプロシージャのテーブル値パラメーター (TVP) の型として使用でき、ディスクベースのテーブルの種類や TVP とまったく同じクライアントから参照できます。そのため、メモリ最適化 TVP を使用したストアドプロシージャの呼び出しとネイティブコンパイルストアドプロシージャの呼び出しは、ディスクベースの TVP を使用した解釈されたストアドプロシージャの呼び出しとまったく同じように動作します。

#temp テーブル置換

次の例は、ストアドプロシージャに対してローカルな #temp テーブルの代わりに、メモリ最適化テーブルの種類とテーブル変数を示しています。

-- Using SQL procedure and temp table  
CREATE TABLE #tempTable (c INT NOT NULL PRIMARY KEY NONCLUSTERED)  
  
CREATE PROCEDURE sqlProc  
AS  
BEGIN  
  TRUNCATE TABLE #tempTable  
  
  INSERT #tempTable VALUES (1)  
  INSERT #tempTable VALUES (2)  
  INSERT #tempTable VALUES (3)  
  SELECT * FROM #tempTable  
END  
GO  
  
-- Using natively compiled stored procedure and table variable  
CREATE TYPE TT AS TABLE (c INT NOT NULL PRIMARY KEY NONCLUSTERED)  
GO  
  
CREATE PROCEDURE NCSPProc  
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  
AS  
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')  
  DECLARE @tableVariable TT  
  INSERT @tableVariable VALUES (1)  
  INSERT @tableVariable VALUES (2)  
  INSERT @tableVariable VALUES (3)  
  SELECT c FROM @tableVariable  
END  
GO

単一の結果セットの作成

次の例は、ネイティブコンパイルストアドプロシージャ内の複数のクエリに基づいて中間結果を格納し、1 つの結果セットを作成する方法を示しています。このサンプルでは、共用体 SELECT c1 FROM dbo.t1 UNION SELECT c1 FROM dbo.t2を計算しています。

CREATE DATABASE hk  
GO  
ALTER DATABASE hk ADD FILEGROUP hk_mod CONTAINS MEMORY_OPTIMIZED_DATA  
ALTER DATABASE hk ADD FILE( NAME = 'hk_mod' , FILENAME = 'c:\data\hk_mod') TO FILEGROUP hk_mod;  
  
USE hk  
GO  
  
CREATE TYPE tab1 AS TABLE (c1 INT NOT NULL, INDEX idx NONCLUSTERED(c1)) WITH (MEMORY_OPTIMIZED = ON)  
  
CREATE TABLE dbo.t1 (c1 INT NOT NULL, INDEX idx NONCLUSTERED(c1)) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)  
CREATE TABLE dbo.t2 (c1 INT NOT NULL, INDEX idx NONCLUSTERED(c1)) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)  
  
INSERT INTO dbo.t1 VALUES (1), (2)  
INSERT INTO dbo.t2 VALUES (3), (4)  
GO  
  
CREATE PROCEDURE dbo.p1  
  WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  
  AS  
  BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english' )  
  
    DECLARE @t dbo.tab1  
    INSERT @t (c1)  
    SELECT c1 FROM dbo.t1;  
  
    INSERT @t (c1)  
    SELECT c1 FROM dbo.t2;  
  
    SELECT c1 FROM @t;  
  END  
GO  
  
EXEC dbo.p1  
GO

テーブル変数のメモリ消費量

テーブル変数のメモリ消費量は、非クラスター化インデックスを除き、メモリ最適化テーブルと似ています。非クラスター化インデックスを使用してメモリ最適化テーブル変数に多数の行を挿入し、インデックスキーが大きい場合、これらのテーブル変数は不均衡な量のメモリを使用します。大きなテーブル変数の非クラスター化インデックスでは、非クラスター化インデックスがテーブルに挿入された同じ数の行 (インデックスページ内のより多くのメモリ) に対して必要なメモリよりも、比例して多くのメモリが必要です。

テーブル変数のメモリは、データベースの Resource Governor リソースプールから取得されます。

メモリ最適化テーブルとは異なり、テーブル変数によって消費されるメモリ (削除された行を含む) は、テーブル変数がスコープ外になると解放されます。

メモリは、データベースの単一の PGPOOL メモリコンシューマーの一部として考慮されます。

こちらもご覧ください

Transact-SQL によるインメモリ OLTP のサポート