메모리 최적화를 사용한 더 빠른 임시 테이블 및 테이블 변수

적용 대상: SQL Server Azure SQL 데이터베이스 Azure SQL Managed Instance

임시 테이블, 테이블 변수 또는 테이블 반환 매개 변수를 사용하는 경우 메모리 최적화 테이블 및 테이블 변수를 활용하여 성능을 향상시키는 변환을 고려합니다. 코드 변경은 일반적으로 최소화됩니다.

이 문서에서는 다음을 설명합니다.

• 메모리 내로의 변환을 선호하는 시나리오입니다.
• 메모리 내로의 변환을 구현하기 위한 기술 단계입니다.
• 메모리 내로 변환하기 전의 필수 구성 요소입니다.
• 메모리 최적화의 성능 이점을 강조하는 코드 샘플

A. 메모리 최적화 테이블 변수의 기본 사항

메모리 최적화 테이블 변수는 메모리 최적화 테이블에 사용되는 동일한 메모리 최적화 알고리즘 및 데이터 구조를 사용하여 뛰어난 효율성을 제공합니다. 고유하게 컴파일된 모듈 내에서 테이블 변수에 액세스할 때 효율성이 최대화됩니다.

메모리 최적화 테이블 변수:

• 이는 메모리에만 저장되며 디스크에 구성 요소가 없습니다.
• IO 작업이 관여되지 않습니다.
• tempdb 사용률 또는 경합을 포함하지 않습니다.
• 저장된 프로시전에 TVP(테이블 반환 매개 변수)로 전달할 수 있습니다.
• 하나 이상의 인덱스(해시 또는 비클러스터형)가 있어야 합니다.
  • 해시 인덱스의 경우 버킷 수는 예상 고유 인덱스 키 수의 1-2배가 되어야 이상적이지만 버킷 수를 최대 10배까지 고려하는 것이 좋습니다. 자세한 내용은 메모리 액세스에 최적화된 테이블의 인덱스를 참조하세요.

개체 유형

메모리 내 OLTP는 메모리 액세스 최적화 임시 테이블 및 테이블 변수에 사용할 수 있는 다음과 같은 개체를 제공합니다.

• 메모리 최적화 테이블
  • 내구성 = SCHEMA_ONLY
• 메모리 최적화 테이블 변수
  • 인라인이 아닌 두 단계로 선언해야 합니다.
    • CREATE TYPE my_type AS TABLE ...; , then
    • DECLARE @mytablevariable my_type;;

B. 시나리오: 전역 tempdb 바꾸기 ##table

메모리 최적화 SCHEMA_ONLY 테이블이 포함된 전역 임시 테이블을 교체하는 작업은 매우 간단합니다. 가장 큰 변화는 런타임이 아니라 배포 시 테이블을 만드는 것입니다. 컴파일 시간 최적화로 인해 메모리 최적화 테이블을 만드는 데 기존 테이블을 만드는 것보다 오래 걸립니다. 온라인 워크로드의 일부로 메모리 최적화 테이블을 만들고 삭제하면 워크로드의 성능과 Always On 가용성 그룹 보조 및 데이터베이스 복구에 대한 다시 실행 성능에 영향을 줍니다.

다음 전역 임시 테이블이 있다고 가정합니다.

CREATE TABLE ##tempGlobalB  
    (  
        Column1   INT   NOT NULL ,  
        Column2   NVARCHAR(4000)  
    );  

전역 임시 테이블을 다음과 같이 DURABILITY = SCHEMA_ONLY가 선언된 메모리 최적화 테이블로 바꾸는 것이 좋습니다.

CREATE TABLE dbo.soGlobalB  
(  
    Column1   INT   NOT NULL   INDEX ix1 NONCLUSTERED,  
    Column2   NVARCHAR(4000)  
)  
    WITH  
        (MEMORY_OPTIMIZED = ON,  
        DURABILITY        = SCHEMA_ONLY);  

B.1단계

전역 임시 테이블에서 SCHEMA_ONLY로 변환하는 단계는 다음과 같습니다.

1. 기존의 디스크 내 테이블과 마찬가지로 dbo.soGlobalB 테이블을 한 번 만듭니다.
2. TRANSACT-SQL에서 ##tempGlobalB 테이블의 생성을 제거합니다. 테이블을 만들면 제공되는 컴파일 오버헤드를 방지하기 위해 런타임 시가 아닌 배포 시 메모리 최적화 테이블을 만드는 것이 중요합니다.
3. T-SQL에서 ##tempGlobalB의 모든 멘션을 dbo.soGlobalB로 대체합니다.

C. 시나리오: 세션 tempdb 바꾸기 #table

세션 임시 테이블을 바꾸기 위한 준비에는 이전 전역 임시 테이블 시나리오보다 더 많은 T-SQL이 포함됩니다. 다행히 추가 T-SQL이 더 이상 변환을 수행하는 데 필요한 것은 아닙니다.

전역 임시 테이블 시나리오에서는 컴파일 오버헤드를 방지하기 위해 런타임 시가 아닌 배포 시 테이블을 만든다는 점이 가장 큰 변화입니다.

다음 세션 임시 테이블이 있다고 가정합니다.

CREATE TABLE #tempSessionC  
(  
    Column1   INT   NOT NULL ,  
    Column2   NVARCHAR(4000)  
);  

먼저 다음 테이블 반환 함수를 만들어 @@spid를 필터링합니다. 함수는 세션 임시 테이블에서 변환하는 모든 SCHEMA_ONLY 테이블에서 사용할 수 있습니다.

CREATE FUNCTION dbo.fn_SpidFilter(@SpidFilter smallint)  
    RETURNS TABLE  
    WITH SCHEMABINDING , NATIVE_COMPILATION  
AS  
    RETURN  
        SELECT 1 AS fn_SpidFilter  
            WHERE @SpidFilter = @@spid;  

두 번째, SCHEMA_ONLY 테이블을 만들고 테이블에 보안 정책을 만듭니다.

각 메모리 최적화 테이블에는 하나 이상의 인덱스가 있어야 합니다.

• 테이블 dbo.soSessionC의 경우 적절한 BUCKET_COUNT를 계산하면 HASH 인덱스가 더 좋을 수 있습니다. 그러나 이 샘플에서는 NONCLUSTERED 인덱스로 단순화합니다.

CREATE TABLE dbo.soSessionC  
(  
    Column1     INT         NOT NULL,  
    Column2     NVARCHAR(4000)  NULL,  

    SpidFilter  SMALLINT    NOT NULL   DEFAULT (@@spid),  

    INDEX ix_SpidFiler NONCLUSTERED (SpidFilter),  
    --INDEX ix_SpidFilter HASH  
    --    (SpidFilter) WITH (BUCKET_COUNT = 64),  
        
    CONSTRAINT CHK_soSessionC_SpidFilter  
        CHECK ( SpidFilter = @@spid ),  
)  
    WITH  
        (MEMORY_OPTIMIZED = ON,  
            DURABILITY = SCHEMA_ONLY);  
go  
  
  
CREATE SECURITY POLICY dbo.soSessionC_SpidFilter_Policy  
    ADD FILTER PREDICATE dbo.fn_SpidFilter(SpidFilter)  
    ON dbo.soSessionC  
    WITH (STATE = ON);  
go  

세 번째, 일반 T-SQL 코드에서 다음을 수행합니다.

1. Transact-SQL 문의 임시 테이블에 대한 모든 참조를 새 메모리 최적화 테이블로 변경합니다.
   • 이전: #tempSessionC
   • 신규: dbo.soSessionC
2. 코드에서 CREATE TABLE #tempSessionC 문을 DELETE FROM dbo.soSessionC로 바꿔 동일한 session_id로 이전 세션에서 삽입한 테이블 콘텐츠에 세션이 노출되지 않도록 합니다. 테이블을 만들면 제공되는 컴파일 오버헤드를 방지하기 위해 런타임 시가 아닌 배포 시 메모리 최적화 테이블을 만드는 것이 중요합니다.
3. 코드에서 DROP TABLE #tempSessionC 문 제거 - 메모리 크기가 잠재적인 문제인 경우 필요에 따라 DELETE FROM dbo.soSessionC 문을 삽입할 수 있습니다.

D. 시나리오: 테이블 변수가 MEMORY_OPTIMIZED=ON일 수 있습니다.

기존 테이블 변수는 tempdb 데이터베이스의 테이블을 나타냅니다. 훨씬 더 빠른 성능을 위해 테이블 변수를 메모리 최적화할 수 있습니다.

다음은 기존 테이블 변수에 대한 T-SQL입니다. 해당 범위는 일괄 처리 또는 세션이 종료되면 종료됩니다.

DECLARE @tvTableD TABLE  
    ( Column1   INT   NOT NULL ,  
      Column2   CHAR(10) );  

D.1 인라인을 명시적으로 변환

위의 구문은 테이블 변수 인라인 생성이라고 합니다. 인라인 구문은 메모리 최적화를 지원하지 않습니다. 따라서 인라인 구문을 TYPE에 대한 명시적 구문으로 변환해 보겠습니다.

범위: 첫 번째 go로 구분된 일괄 처리에 의해 만들어진 TYPE 정의는 서버를 종료하고 다시 시작한 후에도 유지됩니다. 하지만 첫 번째 go 구분 기호 이후 선언된 테이블 @tvTableC는 다음 go에 도달하고 일괄 처리가 종료될 때까지만 유지됩니다.

CREATE TYPE dbo.typeTableD  
    AS TABLE  
    (  
        Column1  INT   NOT NULL ,  
        Column2  CHAR(10)  
    );  
go  
        
SET NoCount ON;  
DECLARE @tvTableD dbo.typeTableD  
;  
INSERT INTO @tvTableD (Column1) values (1), (2)  
;  
SELECT * from @tvTableD;  
go  

D.2 디스크에서 메모리 최적화로 명시적 변환

메모리 최적화 테이블 변수는 tempdb에 상주하지 않습니다. 메모리 최적화로 인해 속도가 10배 이상 증가하는 경우가 많습니다.

메모리 최적화로의 변환은 한 단계에서만 수행됩니다. 명시적 TYPE 만들기를 다음과 같이 개선하고 다음을 추가합니다.

• 인덱스 즉, 각 메모리 최적화 테이블에는 하나 이상의 인덱스가 있어야 합니다.
• MEMORY_OPTIMIZED = ON.

CREATE TYPE dbo.typeTableD  
    AS TABLE  
    (  
        Column1  INT   NOT NULL   INDEX ix1,  
        Column2  CHAR(10)  
    )  
    WITH  
        (MEMORY_OPTIMIZED = ON);  

완료

E. SQL Server에 대한 필수 구성 요소 FILEGROUP

Microsoft SQL Server에서 메모리 최적화 기능을 사용하려면 데이터베이스에 MEMORY_OPTIMIZED_DATA로 선언된 FILEGROUP이 있어야 합니다.

• Azure SQL Database는 이 FILEGROUP을 만들 필요가 없습니다.

필수 구성 요소: FILEGROUP에 대한 다음 Transact-SQL 코드는 이 문서의 뒷부분에 나오는 긴 T-SQL 코드 샘플의 필수 구성 요소입니다.

1. SSMS.exe 또는 T-SQL을 제출할 수 있는 다른 도구를 사용해야 합니다.
2. 샘플 FILEGROUP T-SQL 코드를 SSMS에 붙여넣습니다.
3. T-SQL을 편집하여 특정 이름 및 디렉터리 경로를 원하는 대로 변경합니다.

• 최종 디렉터리를 제외하고 FILENAME 값의 모든 디렉터리가 선행되어야 합니다.

4. 편집된 T-SQL을 실행합니다.

• 다음 하위 섹션에서 속도 비교 T-SQL을 반복적으로 조정하고 다시 실행하더라도 FILEGROUP T-SQL을 두 번 이상 실행할 필요가 없습니다.

ALTER DATABASE InMemTest2  
    ADD FILEGROUP FgMemOptim3  
        CONTAINS MEMORY_OPTIMIZED_DATA;  
go  
ALTER DATABASE InMemTest2  
    ADD FILE  
    (  
        NAME = N'FileMemOptim3a',  
        FILENAME = N'C:\DATA\FileMemOptim3a'  
                    --  C:\DATA\    preexisted.  
    )  
    TO FILEGROUP FgMemOptim3;  
go  

다음 스크립트는 파일 그룹을 만들고 권장 데이터베이스 설정 enable-in-memory-oltp.sql을 구성합니다.

FILE 및 FILEGROUP의 ALTER DATABASE ... ADD에 대한 자세한 내용은 다음을 참조하세요.

• ALTER DATABASE 파일 및 파일 그룹 옵션(Transact-SQL)
• 메모리 최적화 파일 그룹

F. 속도 향상을 증명하는 빠른 테스트

이 섹션에서는 메모리 최적화 테이블 변수를 사용하여 INSERT-DELETE의 속도 향상을 테스트하고 비교할 수 있는 Transact-SQL 코드를 제공합니다. 코드는 테이블 형식이 메모리 최적화인 전반부를 제외하고 거의 동일한 두 개의 절반으로 구성됩니다.

비교 테스트는 약 7초 동안 지속됩니다. 샘플을 실행하려면:

1. 필수 구성 요소: 이전 섹션에서 FILEGROUP T-SQL을 이미 실행해야 합니다.
2. 다음 T-SQL INSERT-DELETE 스크립트를 실행합니다.

• T-SQL을 5001번 다시 제출하는 'GO 5001' 문을 확인합니다. 횟수를 조정하고 다시 실행할 수 있습니다.

Azure SQL Database에서 스크립트를 실행하는 경우 동일한 지역의 VM에서 실행해야 합니다.

PRINT ' ';
PRINT '---- Next, memory-optimized, faster. ----';

DROP TYPE IF EXISTS dbo.typeTableC_mem;
GO

CREATE TYPE dbo.typeTableC_mem -- !!  Memory-optimized.  
AS TABLE (
    Column1 INT NOT NULL INDEX ix1,
    Column2 CHAR(10)
)
WITH (MEMORY_OPTIMIZED = ON);
GO

DECLARE @dateString_Begin NVARCHAR(64) =
    CONVERT(NVARCHAR(64), GETUTCDATE(), 121);

PRINT CONCAT (
    @dateString_Begin,
    ' = Begin time, _mem.'
);
GO

SET NOCOUNT ON;

DECLARE @tvTableC dbo.typeTableC_mem;-- !!  

INSERT INTO @tvTableC (Column1)
VALUES (1), (2);

INSERT INTO @tvTableC (Column1)
VALUES (3), (4);

DELETE @tvTableC;GO 5001

DECLARE @dateString_End NVARCHAR(64) =
    CONVERT(NVARCHAR(64), GETUTCDATE(), 121);

PRINT CONCAT (
    @dateString_End,
    ' = End time, _mem.'
);
GO

DROP TYPE IF EXISTS dbo.typeTableC_mem;
GO

---- End memory-optimized.  
-------------------------------------------------  
---- Start traditional on-disk.  
PRINT ' ';
PRINT '---- Next, tempdb based, slower. ----';

DROP TYPE IF EXISTS dbo.typeTableC_tempdb;
GO

CREATE TYPE dbo.typeTableC_tempdb -- !!  Traditional tempdb.  
AS TABLE (
    Column1 INT NOT NULL,
    Column2 CHAR(10)
);
GO

DECLARE @dateString_Begin NVARCHAR(64) =
    CONVERT(NVARCHAR(64), GETUTCDATE(), 121);

PRINT CONCAT (
    @dateString_Begin,
    ' = Begin time, _tempdb.'
);
GO

SET NOCOUNT ON;

DECLARE @tvTableC dbo.typeTableC_tempdb;-- !!  

INSERT INTO @tvTableC (Column1)
VALUES (1), (2);

INSERT INTO @tvTableC (Column1)
VALUES (3), (4);

DELETE @tvTableC;GO 5001

DECLARE @dateString_End NVARCHAR(64) =
    CONVERT(NVARCHAR(64), GETUTCDATE(), 121);

PRINT CONCAT (
    @dateString_End,
    ' = End time, _tempdb.'
);
GO

DROP TYPE IF EXISTS dbo.typeTableC_tempdb;
GO

PRINT '---- Tests done. ----';
GO

결과 집합은 다음과 같습니다.

---- Next, memory-optimized, faster. ----  
2016-04-20 00:26:58.033  = Begin time, _mem.  
Beginning execution loop  
Batch execution completed 5001 times.  
2016-04-20 00:26:58.733  = End time, _mem.  

---- Next, tempdb based, slower. ----  
2016-04-20 00:26:58.750  = Begin time, _tempdb.  
Beginning execution loop  
Batch execution completed 5001 times.  
2016-04-20 00:27:05.440  = End time, _tempdb.  
---- Tests done. ----  

G. 활성 메모리 사용량 예측

다음 리소스를 사용하면 메모리 최적화 테이블의 활성 메모리 요구량을 예측할 수 있습니다.

• 메모리 최적화 테이블의 메모리 요구 사항 예측
• 메모리 최적화 테이블의 테이블 및 행 크기: 계산 예제

큰 테이블 변수의 경우 비클러스터형 인덱스는 메모리 최적화 테이블보다 더 많은 메모리를 사용합니다. 행 개수 및 인덱스 키가 클수록 차이가 증가합니다.

메모리 최적화 테이블 변수가 액세스당 하나의 정확한 키 값으로만 액세스되는 경우 해시 인덱스가 비클러스터형 인덱스보다 더 적합할 수 있습니다. 그러나 적절한 BUCKET_COUNT를 예측할 수 없는 경우 NONCLUSTERED 인덱스가 두 번째 좋은 선택입니다.

H. 참고 항목

• 메모리 최적화 테이블

• 메모리 최적화 개체에 대한 내구성 정의

• 2017년 9월 블로그에 발표된 부적절한 메모리 부족 오류를 제거하기 위한 누적 업데이트

  • SQL Server 2016 빌드 버전은 릴리스, 서비스 팩 및 누적 업데이트에 대한 전체 세부 정보를 제공합니다.
  • 이러한 부적절한 오류는 SQL Server의 Enterprise 버전에서 발생하지 않았습니다.