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

아티클
08/21/2023

적용 대상:SQL Server Azure SQL DatabaseAzure SQL Managed Instance

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

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

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

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

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

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

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

개체 유형

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

메모리 최적화 테이블
- 내구성 = SCHEMA_ONLY
메모리 최적화 테이블 변수
- 인라인이 아닌 두 단계로 선언해야 합니다.
  - CREATE TYPE my_type AS TABLE ...; 다음
  - 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로 변환하는 단계는 다음과 같습니다.

기존의 디스크 내 테이블과 마찬가지로 dbo.soGlobalB 테이블을 한 번 만듭니다.
Transact-SQL에서 ##tempGlobalB 테이블 만들기를 제거합니다. 테이블을 만들면 제공되는 컴파일 오버헤드를 방지하기 위해 런타임 시가 아닌 배포 시 메모리 최적화 테이블을 만드는 것이 중요합니다.
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 코드에서:

Transact-SQL 문의 임시 테이블에 대한 모든 참조를 새 메모리 최적화 테이블로 변경합니다.
- 이전: #tempSessionC
- 신규: dbo.soSessionC
CREATE TABLE #tempSessionC 이전 세션에서 삽입한 테이블 내용에 세션이 동일한 session_id 노출되지 않도록 코드DELETE FROM dbo.soSessionC의 문을 로 바꿉니다. 테이블을 만들면 제공되는 컴파일 오버헤드를 방지하기 위해 런타임 시가 아닌 배포 시 메모리 최적화 테이블을 만드는 것이 중요합니다.
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 코드 샘플의 필수 구성 요소입니다.

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

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

편집된 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 자세한 내용은 다음을 참조하세요.

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

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

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

필수 구성 요소: 이전 섹션에서 FILEGROUP T-SQL을 이미 실행해야 합니다.
다음 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 버전에서 발생하지 않았습니다.