Memory-Optimized 테이블 변수

메모리 최적화 테이블(효율적인 데이터 액세스를 위해)과 고유하게 컴파일된 저장 프로시저(효율적인 쿼리 처리 및 비즈니스 논리 실행) 외에도 In-Memory OLTP는 세 번째 종류의 개체인 메모리 최적화 테이블 형식을 도입합니다. 메모리 최적화 테이블 형식을 사용하여 만든 테이블 변수는 메모리 최적화 테이블 변수입니다.

메모리 최적화 테이블 변수는 디스크 기반 테이블 변수와 비교할 때 다음과 같은 이점을 제공합니다.

변수는 메모리에만 저장됩니다. 메모리 최적화 테이블 형식은 특히 변수가 고유하게 컴파일된 저장 프로시저에서 사용되는 경우 메모리 최적화 테이블에 사용되는 동일한 메모리 최적화 알고리즘 및 데이터 구조를 사용하기 때문에 데이터 액세스가 더 효율적입니다.
메모리 최적화 테이블 변수를 사용하면 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~2배여야 합니다. 자세한 내용은 해시 인덱스에 대한 올바른 버킷 수 확인(Determining the Correct Bucket Count)을 참조하세요.
메모리 최적화 테이블에 대한 데이터 형식 및 제약 조건 제한은 메모리 최적화 테이블 형식에도 적용됩니다. 예를 들어 SQL Server 2014 기본 제약 조건은 지원되지만 CHECK 제약 조건은 지원되지 않습니다.

메모리 최적화 테이블과 마찬가지로 메모리 최적화 테이블 변수는,

병렬 계획을 지원하지 않습니다.
메모리에 맞고 디스크 리소스를 사용하지 않아야 합니다.

디스크 기반 테이블 변수는 tempdb에 있습니다. 메모리 최적화 테이블 변수는 사용자 데이터베이스에 있지만 스토리지를 사용하지 않으며 복구되지 않습니다.

인라인 구문을 사용하여 메모리 최적화 테이블 변수를 만들 수 없습니다. 디스크 기반 테이블 변수와 달리 먼저 형식을 만들어야 합니다.

Table-Valued 매개 변수

다음 샘플 스크립트는 테이블 변수를 메모리 최적화 테이블 형식 Sales.SalesOrderDetailType_inmem으로 선언하고, 변수에 세 개의 행을 삽입하고, 변수를 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

단일 결과 집합 만들기

다음 샘플에서는 기본적으로 컴파일된 저장 프로시저에서 여러 쿼리를 기반으로 중간 결과를 저장하고 단일 결과 집합을 만드는 방법을 보여 줍니다. 샘플은 합집합 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 메모리 소비자의 일부로 간주됩니다.

또한 참조하십시오

메모리 내 OLTP에 대한 Transact-SQL 지원

Last updated on 2017-07-14