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

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

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

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

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

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

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

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

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

개체 유형

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

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

B. 시나리오: 전역 임시 테이블 바꾸기

메모리 최적화 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);

단계

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

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

C. 시나리오: 세션 임시 테이블 바꾸기

세션 임시 테이블을 바꾸기 위한 준비에는 이전 전역 임시 테이블 시나리오보다 더 많은 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 DEFAULT (@@spid) NOT NULL,
    CONSTRAINT CHK_soSessionC_SpidFilter CHECK (SpidFilter = @@spid),
    INDEX ix_SpidFiler NONCLUSTERED (SpidFilter)
    -- INDEX ix_SpidFilter HASH
    --    (SpidFilter) WITH (BUCKET_COUNT = 64),
)
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));

인라인을 명시적으로 변환

위의 구문은 테이블 변수 인라인 생성이라고 합니다. 인라인 구문은 메모리 최적화를 지원하지 않습니다. 따라서 인라인 구문을 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 AS 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_DATAFILEGROUP이 있어야 합니다.

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

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

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

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

1. 편집된 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 스크립트를 실행합니다.

• GO 5001 문장이 T-SQL을 5,001번 다시 제출하는지 확인하십시오. 횟수를 조정하고 다시 실행할 수 있습니다.

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 인덱스가 좋은 두 번째 선택입니다.

관련 콘텐츠

• In-Memory OLTP 을 위한 샘플 데이터베이스
• Memory-Optimized 개체의 내구성 정의
• 2017년 9월 블로그에 발표된 부적절한 메모리 부족 오류를 제거하기 위한 누적 업데이트
• KB3177312 - SQL Server 2016 빌드 버전