메모리 내 OLTP의 초기 영역 조사

  • 아티클

적용 대상:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

이 문서는 Microsoft SQL Server 및 Azure SQL Database의 메모리 내 OLTP 성능 기능의 기본 사항을 알아보기 위해 서두르는 개발자를 위한 것입니다.

메모리 내 OLTP의 경우 이 문서에서는 다음을 제공합니다.

  • 기능에 대한 간략한 설명입니다.
  • 기능을 구현하는 핵심 코드 샘플입니다.

SQL Server 및 SQL Database는 메모리 내 기술을 지원하는 데 약간의 변형만 있습니다.

야생에서 일부 블로거는 헤카톤으로 메모리 내 OLTP를 참조합니다.

메모리 내 기능의 이점

SQL Server는 많은 애플리케이션 시스템의 성능을 크게 향상시킬 수 있는 메모리 내 기능을 제공합니다. 가장 똑바른 고려 사항은 이 섹션에서 설명합니다.

OLTP(온라인 트랜잭션 처리) 기능

많은 수의 SQL INSERT를 동시에 처리해야 하는 시스템은 OLTP 기능에 적합한 후보입니다.

  • 벤치마크는 메모리 내 기능을 채택하여 속도 향상을 5배에서 20배 더 빠르게 달성할 수 있음을 보여 줍니다.

Transact-SQL 계산을 많이 처리하는 시스템도 적합합니다.

  • 무거운 계산 전용 저장 프로시저는 최대 99배 더 빠르게 실행할 수 있습니다.

나중에 메모리 내 OLTP의 성능 향상에 대한 데모를 제공하는 다음 문서를 참조할 수 있습니다.

운영 분석을 위한 기능

메모리 내 분석은 일반적으로 GROUP BY 절을 포함하여 트랜잭션 데이터를 집계하는 SQL SELECT를 나타냅니다. columnstore라는 인덱스 형식은 운영 분석의 핵심입니다.

다음 두 가지 주요 시나리오가 있습니다.

  • Batch Operational Analytics 는 업무 시간 이후에 또는 트랜잭션 데이터의 복사본이 있는 보조 하드웨어에서 실행되는 집계 프로세스를 나타냅니다.
  • 실시간 운영 분석은 업무 시간 및 트랜잭션 워크로드에 사용되는 기본 하드웨어에서 실행되는 집계 프로세스를 나타냅니다.

현재 문서에서는 분석이 아닌 OLTP에 중점을 둡니다. columnstore 인덱스가 분석을 SQL로 가져오는 방법에 대한 자세한 내용은 다음을 참조하세요.

columnstore

훌륭한 블로그 게시물의 시퀀스는 여러 관점에서 columnstore 인덱스를 우아하게 설명합니다. 대부분의 게시물은 columnstore에서 지원하는 실시간 운영 분석의 개념에 대해 추가로 설명합니다. 이 게시물은 2016년 3월 Microsoft의 프로그램 관리자인 Sunil Agarwal이 작성했습니다.

실시간 운영 분석

  1. 메모리 내 기술을 사용한 실시간 운영 분석
  2. 실시간 운영 분석 - NCCI(비클러스터형 columnstore 인덱스) 개요
  3. 실시간 운영 분석: SQL Server 2016에서 NCCI(비클러스터형 클러스터형 columnstore 인덱스)를 사용하는 간단한 예제
  4. 실시간 운영 분석: SQL Server 2016의 DML 작업 및 NCCI(비클러스터형 columnstore 인덱스)
  5. 실시간 운영 분석: 필터링된 NCCI(비클러스터형 columnstore 인덱스)
  6. 실시간 운영 분석: NCCI(비클러스터형 columnstore 인덱스)에 대한 압축 지연 옵션
  7. 실시간 운영 분석: NCCI 및 성능을 사용하는 압축 지연 옵션
  8. 실시간 운영 분석: 메모리 최적화 테이블 및 columnstore 인덱스

columnstore 인덱스 조각 모음

  1. REORGANIZE 명령을 사용한 Columnstore 인덱스 조각 모음
  2. REORGANIZE의 columnstore 인덱스 병합 정책

데이터의 대량 가져오기

  1. 클러스터형 열 저장소: 대량 로드
  2. 클러스터형 columnstore 인덱스: 데이터 로드 최적화 - 최소 로깅
  3. 클러스터형 columnstore 인덱스: 데이터 부하 최적화 - 병렬 대량 가져오기

메모리 내 OLTP의 기능

메모리 내 OLTP의 기본 기능을 살펴보겠습니다.

메모리 최적화 테이블

CREATE TABLE 문의 T-SQL 키워드(keyword) MEMORY_OPTIMIZED 디스크가 아닌 활성 메모리에 테이블을 만드는 방법입니다.

메모리 액세스에 최적화된 테이블 은 활성 메모리에 포함될 뿐 아니라 디스크에도 보조 복사본이 있습니다.

  • 디스크 복사는 서버 또는 데이터베이스를 종료한 후 다시 시작한 후 일상적인 복구를 위한 것입니다. 이 메모리 및 디스크의 중복성은 사용자와 코드에서 완전히 숨겨집니다.

고유하게 컴파일된 모듈

CREATE PROCEDURE 문의 T-SQL 키워드(keyword) NATIVE_COMPILATION 고유하게 컴파일된 저장 프로시저를 만드는 방법입니다. 온라인에서 데이터베이스를 순환할 때마다 네이티브 프로시저를 처음 사용할 때 T-SQL 문이 기계어 코드로 컴파일됩니다. T-SQL 지침은 더 이상 모든 명령의 느린 해석을 견딜 수 없습니다.

  • 네이티브 컴파일 결과가 해석된 기간의 1/100인 기간을 확인했습니다.

네이티브 모듈은 메모리 최적화 테이블만 참조할 수 있으며 디스크 기반 테이블을 참조할 수 없습니다.

고유하게 컴파일된 모듈에는 다음 세 가지 유형이 있습니다.

Azure SQL Database의 가용성

메모리 내 OLTP 및 Columnstore는 Azure SQL Database에서 사용할 수 있습니다. 자세한 내용은 SQL Database의 메모리 내 기술을 사용하여 성능 최적화를 참조하세요.

1. 호환성 수준 >= 130 확인

이 섹션의 앞부분에 나오는 번호가 지정된 일련의 섹션에서는 메모리 내 OLTP 기능을 구현하는 데 사용할 수 있는 Transact-SQL 구문을 보여 줍니다.

먼저 데이터베이스의 호환성 수준을 적어도 130 이상으로 설정하는 것이 중요합니다. 다음은 현재 데이터베이스에 설정된 현재 호환성 수준을 보여 주는 T-SQL 코드입니다.

SELECT d.compatibility_level
    FROM sys.databases as d
    WHERE d.name = Db_Name();

다음은 필요한 경우 수준을 업데이트하는 T-SQL 코드입니다.

ALTER DATABASE CURRENT
    SET COMPATIBILITY_LEVEL = 130;

2. 스냅샷으로 상승

트랜잭션에 디스크 기반 테이블과 메모리 최적화 테이블이 모두 포함되어 있을 경우 컨테이너 간 트랜잭션이라고 합니다. 이러한 트랜잭션에서는 트랜잭션의 메모리 최적화 부분이 SNAPSHOT이라는 트랜잭션 격리 수준에서 작동해야 합니다.

컨테이너 간 트랜잭션에서 메모리 최적화 테이블에 대해 이 수준을 안정적으로 적용하려면 다음 T-SQL을 실행하여 데이터베이스 설정을 변경합니다.

ALTER DATABASE CURRENT
    SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;

3. 최적화된 FILEGROUP 만들기

Microsoft SQL Server에서 메모리 최적화 테이블을 만들려면 먼저 CONTAINS MEMORY_OPTIMIZED_DATA 선언하는 FILEGROUP을 만들어야 합니다. FILEGROUP이 데이터베이스에 할당됩니다. 자세한 내용은 다음을 참조하십시오.

Azure SQL Database에서는 이러한 FILEGROUP을 만들 필요가 없으며 만들 수 없습니다.

다음 샘플 T-SQL 스크립트는 메모리 내 OLTP에 대한 데이터베이스를 사용하도록 설정하고 모든 권장 설정을 구성합니다. SQL Server 및 Azure SQL Database( enable-in-memory-oltp.sql)에서 작동합니다.

MEMORY_OPTIMIZED_DATA 파일 그룹이 있는 데이터베이스에 대해 모든 SQL Server 기능이 지원되는 것은 아닙니다. 제한 사항에 대한 자세한 내용은 메모리 내 OLTP에 대한 지원되지 않는 SQL Server 기능을 참조하세요.

4. 메모리 최적화 테이블 만들기

중요한 Transact-SQL 키워드(keyword) 키워드(keyword) MEMORY_OPTIMIZED.

CREATE TABLE dbo.SalesOrder
    (
        SalesOrderId   integer   not null   IDENTITY
            PRIMARY KEY NONCLUSTERED,
        CustomerId   integer    not null,
        OrderDate    datetime   not null
    )
        WITH
            (MEMORY_OPTIMIZED = ON,
            DURABILITY = SCHEMA_AND_DATA);

메모리 최적화 테이블에 대한 Transact-SQL INSERT 및 SELECT 문은 일반 테이블과 동일합니다.

메모리 액세스에 최적화된 테이블에 대한 ALTER TABLE

ALTER TABLE... ADD/DROP은 메모리 최적화 테이블 또는 인덱스에서 열을 추가하거나 제거할 수 있습니다.

  • CREATE INDEX 및 DROP INDEX는 메모리 최적화 테이블에 대해 실행할 수 없습니다. ALTER TABLE을 사용합니다. ... 대신 ADD/DROP INDEX입니다.
  • 자세한 내용은 메모리 최적화 테이블 변경을 참조 하세요.

메모리 최적화 테이블 및 인덱스 계획

5. 고유하게 컴파일된 저장 프로시저 만들기(네이티브 프로시저)

중요한 키워드(keyword) NATIVE_COMPILATION.

CREATE PROCEDURE ncspRetrieveLatestSalesOrderIdForCustomerId  
        @_CustomerId   INT  
        WITH  
            NATIVE_COMPILATION,  
            SCHEMABINDING  
    AS  
    BEGIN ATOMIC  
        WITH  
            (TRANSACTION ISOLATION LEVEL = SNAPSHOT,
            LANGUAGE = N'us_english')  
      
        DECLARE @SalesOrderId int, @OrderDate datetime;
      
        SELECT TOP 1  
                @SalesOrderId = s.SalesOrderId,  
                @OrderDate    = s.OrderDate  
            FROM dbo.SalesOrder AS s  
            WHERE s.CustomerId = @_CustomerId  
            ORDER BY s.OrderDate DESC;  
      
        RETURN @SalesOrderId;  
    END;

키워드 SCHEMABINDING은 기본 프로시저가 먼저 삭제되지 않으면 기본 프로시저에서 참조하는 테이블을 삭제할 수 없음을 의미합니다. 자세한 내용은 네이티브 컴파일 저장 프로시저 만들기를 참조하세요.

메모리 최적화 테이블에 액세스하기 위해 고유하게 컴파일된 저장 프로시저를 만들 필요가 없습니다. 기존 저장 프로시저 및 임시 일괄 처리에서 메모리 최적화 테이블을 참조할 수도 있습니다.

6. 기본 프로시저 실행

테이블을 두 개의 데이터 행으로 채웁니다.

INSERT into dbo.SalesOrder  
        ( CustomerId, OrderDate )  
    VALUES  
        ( 42, '2013-01-13 03:35:59' ),
        ( 42, '2015-01-15 15:35:59' );

고유하게 컴파일된 저장 프로시저에 대한 EXECUTE 호출은 다음과 같습니다.

DECLARE @LatestSalesOrderId int, @mesg nvarchar(128);
      
EXECUTE @LatestSalesOrderId =  
    ncspRetrieveLatestSalesOrderIdForCustomerId 42;
      
SET @mesg = CONCAT(@LatestSalesOrderId,  
    ' = Latest SalesOrderId, for CustomerId = ', 42);
PRINT @mesg;

실제 PRINT 출력은 다음과 같습니다.

-- 2 = Latest SalesOrderId, for CustomerId = 42

설명서와 다음 단계에 대한 가이드

위의 일반 예제에서는 메모리 내 OLTP의 고급 기능을 학습할 수 있는 기반을 제공합니다. 다음 섹션은 알아야 할 수 있는 특별한 고려 사항과 각 항목에 대한 세부 정보를 볼 수 있는 위치에 대한 가이드입니다.

메모리 내 OLTP 기능이 훨씬 더 빠르게 작동하는 방법

다음 하위 섹션에서는 향상된 성능을 제공하기 위해 메모리 내 OLTP 기능이 내부적으로 작동하는 방식을 간략하게 설명합니다.

메모리 최적화 테이블의 성능 향상 방법

이중 특성: 메모리 최적화 테이블에는 활성 메모리의 표현과 하드 디스크의 다른 표현의 이중 특성이 있습니다. 각 트랜잭션은 테이블의 두 표현 방식으로 모두 커밋됩니다. 트랜잭션은 훨씬 더 빠른 활성 메모리 표현에 대해 작동합니다. 메모리 최적화 테이블은 디스크에 비해 활성 메모리 속도가 더 빨라집니다. 또한 활성 메모리의 민첩성이 높아질수록 속도에 최적화된 고급 테이블 구조가 실용화됩니다. 고급 구조도 페이지가 없으므로 래치와 스핀 잠금의 오버헤드 및 경합을 방지합니다.

잠금 없음: 메모리 최적화 테이블은 데이터 무결성과 동시성 및 높은 처리량의 경쟁 목표에 대해 낙관적인 접근 방식을 사용합니다. 트랜잭션 중에 테이블은 업데이트된 데이터 행의 버전에 잠금을 배치하지 않습니다. 이렇게 하면 일부 대용량 시스템에서 경합이 크게 감소할 수 있습니다.

행 버전: 잠금 대신 메모리 최적화 테이블은 tempdb가 아닌 테이블 자체에 업데이트된 행의 새 버전을 추가합니다. 원래 행은 트랜잭션이 커밋될 때까지 유지됩니다. 트랜잭션 중에 다른 프로세스는 행의 원래 버전을 읽을 수 있습니다.

  • 디스크 기반 테이블의 경우 행 버전이 여러 개 만들어 지면 행 버전은 tempdb에 임시로 저장됩니다.

로깅 감소: 업데이트된 행의 이전 및 이후 버전이 메모리 최적화 테이블에 보관됩니다. 행 쌍은 일반적으로 로그 파일에 기록되는 많은 정보를 제공합니다. 이렇게 하면 시스템에서 로그에 더 적은 정보 및 덜 자주 쓸 수 있습니다. 그러나 트랜잭션 무결성이 보장됩니다.

네이티브 프로시지의 성능 향상 방법

일반 해석 저장 프로시저를 고유하게 컴파일된 저장 프로시저로 변환하면 런타임에 실행할 명령 수가 크게 줄어듭니다.

메모리 내 기능의 상충 관계

전산학에서 일반적인 경우와 마찬가지로 메모리 내 기능을 통해 제공되는 성능 향상은 절충안입니다. 더 나은 기능은 기능의 추가 비용보다 더 가치 있는 이점을 제공합니다. 다음에서 상충 관계에 대한 포괄적인 지침을 확인할 수 있습니다.

이 섹션의 나머지 부분에서는 몇 가지 주요 계획 및 절전 고려 사항을 나열합니다.

메모리 최적화 테이블의 장차

메모리 예측: 메모리 최적화 테이블에서 사용할 활성 메모리의 양을 예측해야 합니다. 컴퓨터 시스템에 메모리 최적화 테이블을 호스트할 수 있는 충분한 메모리 용량이 있어야 합니다. 자세한 내용은 다음을 참조하십시오.

큰 테이블 분할: 많은 활성 메모리에 대한 수요를 충족하는 한 가지 방법은 큰 테이블을 핫 최근 데이터 행을 저장하는 메모리 내 파트와 콜드 레거시 행을 저장하는 디스크의 다른 부분(예: 완전히 배송되고 완료된 판매 주문)으로 분할하는 것입니다. 이 분할은 디자인 및 구현 단계에서 수동으로 처리됩니다. 참조

네이티브 프로시지의 절상

  • 고유하게 컴파일된 저장 프로시저는 디스크 기반 테이블에 액세스할 수 없습니다. 기본 프로시저는 메모리 최적화 테이블에만 액세스할 수 있습니다.
  • 서버 또는 데이터베이스가 가장 최근에 다시 온라인 상태가 된 후 네이티브 프로시어가 처음으로 실행되면 네이티브 프로시던트를 한 번 다시 컴파일해야 합니다. 이 작업으로 인해 기본 프로시저 실행이 시작되기 전에 지연이 발생합니다.

메모리 최적화 테이블에 대한 고급 고려 사항

메모리 최적화 테이블의 인덱스는 기존 디스크 테이블 의 인덱스와는 다른 방식으로 다릅니다. 해시 인덱스는 메모리 최적화 테이블에서만 사용할 수 있습니다.

계획 중인 메모리 최적화 테이블 및 인덱스에 대해 활성 메모리가 충분한지 확인할 계획을 세워야 합니다. 참조

메모리 최적화 테이블은 DURABILITY = SCHEMA_ONLY 사용하여 선언할 수 있습니다.

  • 이 구문은 데이터베이스를 오프라인으로 전환할 때 메모리 최적화 테이블의 모든 데이터를 카드 시스템에 지시합니다. 테이블 정의만 유지됩니다.
  • 데이터베이스를 다시 온라인 상태로 전환하면 메모리 최적화 테이블이 활성 메모리로 다시 로드되고 데이터가 비어 있습니다.
  • 수천 개의 행이 관련된 경우 SCHEMA_ONLY 테이블이 tempdb의 #temporary 테이블 보다 더 효율적일 수 있습니다.

테이블 변수는 메모리 최적화로 선언할 수도 있습니다. 참조

네이티브 컴파일 모듈에 대한 고급 고려 사항

Transact-SQL을 통해 사용할 수 있는 고유하게 컴파일된 모듈 유형은 다음과 같습니다.

고유하게 컴파일된 UDF(사용자 정의 함수)는 해석된 UDF보다 빠르게 실행됩니다. UDF를 사용하여 고려해야 할 몇 가지 사항은 다음과 같습니다.

  • T-SQL SELECT에서 UDF를 사용하는 경우 UDF가 반환된 행마다 한 번씩 항상 호출됩니다.
    • UDF는 인라인으로 실행되지 않고 항상 호출됩니다.
    • 컴파일된 구분은 모든 UDF에 내재된 반복 호출의 오버헤드보다 덜 중요합니다.
    • 하지만 실용적인 수준에서 UDF 호출의 오버헤드가 허용되는 경우도 많습니다.

테스트 데이터 및 네이티브 UDF의 성능에 대한 설명은 다음을 참조하세요.

메모리 최적화 테이블에 대한 설명서 가이드

메모리 최적화 테이블에 대한 특별한 고려 사항을 설명하는 다른 문서를 참조하세요.

기본 프로시저에 대한 설명서 가이드

다음 문서 및 그 목차에 나오는 하위 문서에서는 네이티브 컴파일 저장 프로시저에 대해 자세히 설명합니다.

메모리 내 OLTP를 사용하여 얻을 수 있는 성능 향상을 보여 주는 코드를 제공하는 문서는 다음과 같습니다.