메모리 내 OLTP 개요 및 사용 시나리오

적용 대상: 데이터베이스 Azure SQL Managed Instance Azure SQL SQL Server(지원되는 모든 버전)

In-Memory OLTP는 트랜잭션 처리, 데이터 수집, 데이터 로드 및 일시적인 데이터 시나리오의 성능을 최적화하기 위해 SQL Server 및 SQL Database 사용할 수 있는 최고의 기술입니다. 이 문서에서는 메모리 내 OLTP의 기술 및 사용 시나리오를 알아봅니다. 이 정보를 사용하여 메모리 내 OLTP가 애플리케이션에 적합 한지 확인할 수 있습니다. 이 문서에는 메모리 내 OLTP 개체를 보여 주는 예제, 성능 데모에 대한 참조 및 다음 단계에 사용할 수 있는 리소스에 대한 참조가 포함되어 있습니다.

이 문서에서는 SQL Server 및 SQL Database In-Memory OLTP 기술에 대해 설명합니다. Azure SQL 메모리 내 데이터와 관련된 자세한 내용은 Azure SQL 데이터베이스 및 Azure SQL Managed Instance 및 블로그에서 메모리 내 기술을 사용하여 성능 최적화: Azure SQL Database의 OLTP In-Memory 참조하세요.

메모리 내 OLTP 개요

메모리 내 OLTP는 올바른 워크로드에 대해 상당한 성능 향상을 제공할 수 있습니다. 경우에 따라 고객이 최대 30배의 성능 향상을 얻기도 하지만 성능 향상 정도는 워크로드에 따라 다릅니다.

그렇다면 이러한 성능 향상은 어디에서 이루어지는 것일까요? 기본적으로 In-Memory OLTP는 데이터 액세스 및 트랜잭션 실행을 보다 효율적으로 만들고 동시에 실행되는 트랜잭션 간에 잠금 및 래치 경합을 제거하여 트랜잭션 처리 성능을 향상시킵니다. In-Memory OLTP는 메모리 내이므로 빠르지 않습니다. 메모리 내 데이터에 최적화되어 있으므로 속도가 빠릅니다. 메모리 내 및 높은 동시성 컴퓨팅의 최신 개선 사항을 활용하기 위해 데이터 스토리지, 액세스 및 처리 알고리즘이 처음부터 다시 설계되었습니다.

이제 데이터가 메모리에 있다고 해서 오류가 발생할 때 데이터가 손실되는 것은 아닙니다. 기본적으로 모든 트랜잭션이 완전히 내구적입니다. 즉, SQL Server의 다른 테이블과 동일한 내구성이 보장됩니다. 트랜잭션 커밋의 일부로 모든 변경 내용이 디스크의 트랜잭션 로그에 기록됩니다. 트랜잭션 커밋 후 언제든 오류가 발생한 경우 데이터베이스가 다시 온라인 상태로 전환되면 데이터가 그대로 유지됩니다. 또한 In-Memory OLTP는 Always On 가용성 그룹, Always On SQL Server(장애조치(failover) 클러스터 인스턴스), 백업/복원 등 SQL Server 모든 고가용성 및 재해 복구 기능과 함께 작동합니다.

데이터베이스에서 In-Memory OLTP를 사용하려면 다음 개체 유형 중 하나 이상을 사용합니다.

  • 메모리 액세스에 최적화된 테이블 은 사용자 데이터를 저장하는 데 사용됩니다. 메모리 최적화되도록 할 테이블은 만들 때 선언합니다.
  • 비영구 테이블 은 캐싱 또는 중간 결과 집합의 임시 데이터에 사용됩니다(기존의 임시 테이블 대체). 내구성이 없는 테이블은 DURABILITY=SCHEMA_ONLY 선언된 메모리 최적화 테이블로, 이러한 테이블을 변경해도 IO가 발생하지 않습니다. 따라서 내구성이 중요하지 않은 경우 로그 IO 리소스 소모를 방지할 수 있습니다.
  • 메모리 액세스에 최적화된 테이블 형식 은 TVP(테이블 반환 매개 변수) 및 저장 프로시저의 중간 결과 집합에 사용됩니다. 기존 테이블 형식 대신 사용할 수 있습니다. 메모리 최적화 테이블 형식을 사용하여 선언된 테이블 변수 및 TVP는 비영구 메모리 최적화 테이블의 이점(효율적인 데이터 액세스 및 IO 없음)을 상속합니다.
  • 고유하게 컴파일된 T-SQL 모듈 은 작업을 처리하는 데 필요한 CPU 주기를 줄여 개별 트랜잭션에 소요되는 시간을 더 단축하는 데 사용됩니다. 고유하게 컴파일할 TRANSACT-SQL 모듈은 만들 때 선언합니다. 이 시점에서 고유하게 컴파일할 수 있는 T-SQL 모듈은 저장 프로시저, 트리거 및 사용자 정의 스칼라 함수입니다.

In-Memory OLTP는 SQL Server 및 SQL Database 기본 제공됩니다. 이러한 개체는 기존 개체와 비슷하게 작동하므로 데이터베이스 및 애플리케이션을 최소한의 변경만 하면서 성능 이점을 얻을 수 있는 경우가 많습니다. 뿐만 아니라 메모리 최적화 테이블과 기존 디스크 기반 테이블을 동일한 데이터베이스에서 함께 사용하고 둘 간에 쿼리를 실행할 수 있습니다. 이 문서의 아래쪽에 있는 이러한 각 개체 유형에 대한 예제가 포함된 Transact-SQL 스크립트를 찾을 수 있습니다.

In-Memory OLTP에 대한 사용 시나리오

In-Memory OLTP는 마법의 빠른 단추가 아니며 모든 워크로드에 적합하지 않습니다. 예를 들어 메모리 최적화 테이블은 대부분의 쿼리가 큰 데이터 범위에서 집계를 수행하는 경우 CPU 사용률을 낮추지 않습니다. 해당 시나리오에는 columnstore 인덱스가 도움이 됩니다.

다음은 고객이 In-Memory OLTP로 성공한 시나리오 및 애플리케이션 패턴 목록입니다.

높은 처리량 및 낮은 대기 시간의 트랜잭션 처리

이는 메모리 내 OLTP를 구축한 핵심 시나리오입니다. 개별 트랜잭션에 대해 일관성 있게 낮은 대기 시간으로 대량의 트랜잭션을 지원합니다.

일반적인 워크로드 시나리오는 금융 상품 거래, 스포츠 베팅, 모바일 게임, 광고 전달 등입니다. 확인한 다른 일반적인 패턴은 자주 읽거나 업데이트하는 "카탈로그"입니다. 예를 들어 대용량 파일이 있고 각 파일이 여러 클러스터 노드에 분산된 경우 메모리 최적화 테이블에서 각 파일의 분할 위치에 대한 카탈로그를 작성합니다.

구현 고려 사항

핵심 트랜잭션 테이블, 즉 성능이 가장 중요한 트랜잭션이 있는 테이블에 메모리 최적화 테이블을 사용합니다. 비즈니스 트랜잭션과 관련된 논리 실행을 최적화하려면 고유하게 컴파일된 저장 프로시저를 사용합니다. 데이터베이스에 저장 프로시저로 푸시할 수 있는 논리가 많을수록 메모리 내 OLTP에서 더 많은 이점을 얻을 수 있습니다.

기존 애플리케이션에서 시작하려면 다음을 수행합니다.

  1. 트랜잭션 성능 분석 보고서를 사용하여 마이그레이션할 개체를 식별합니다.
  2. 메모리 최적화네이티브 컴파일 관리자를 사용하여 마이그레이션에 대한 도움을 얻습니다.

IoT(사물 인터넷)를 비롯한 데이터 수집

메모리 내 OLTP는 다양한 원본에서 동시에 많은 양의 데이터를 수집하는 데 유용합니다. SQL Server 데이터에 대한 쿼리를 빠르게 실행하고 실시간 인사이트를 얻을 수 있기 때문에 다른 대상과 비교하여 SQL Server 데이터베이스로 데이터를 수집하는 것이 좋습니다.

다음은 일반적인 애플리케이션 패턴입니다.

  • 센서 판독값 및 이벤트를 수집하여 알림 및 기록 분석을 지원합니다.
  • 동시 읽기 워크로드에 대한 영향을 최소화하면서 여러 원본에서 일괄 처리 업데이트를 관리합니다.

구현 고려 사항

메모리 최적화 테이블을 데이터 수집에 사용합니다. 수집이 업데이트보다 주로 삽입으로 구성되고 메모리 내 OLTP 스토리지의 데이터 공간이 중요한 경우 다음 중 하나를 수행합니다.

  • 작업을 사용하여 클러스터형 columnstore 인덱INSERT INTO <disk-based table> SELECT FROM <memory-optimized table>스가 있는 디스크 기반 테이블에 데이터를 정기적으로 일괄 처리하거나
  • temporal 메모리 최적화 테이블을 사용하여 기록 데이터를 관리합니다. 이 모드에서는 기록 데이터가 디스크에 있으며 데이터 이동이 시스템에서 관리됩니다.

SQL Server 샘플 리포지토리에는 temporal 메모리 최적화 테이블, 메모리 최적화 테이블 형식 및 고유하게 컴파일된 저장 프로시저를 사용하여 센서 데이터의 메모리 내 OLTP 스토리지 공간을 관리하는 동시에 데이터 수집을 가속화하는 스마트 그리드 애플리케이션이 포함되어 있습니다.

캐싱 및 세션 상태

In-Memory OLTP 기술은 SQL Server 또는 Azure SQL 데이터베이스의 데이터베이스 엔진을 세션 상태(예: ASP.NET 애플리케이션) 및 캐싱을 위한 매력적인 플랫폼으로 만듭니다.

ASP.NET 세션 상태는 In-Memory OLTP에 대한 성공적인 사용 사례입니다. 한 고객은 SQL Server를 사용하여 초당 120만 요청을 달성하려고 했습니다. 그 동안 엔터프라이즈의 모든 중간 계층 애플리케이션의 캐싱 요구 사항을 위해 메모리 내 OLTP를 사용하기 시작했습니다. 세부 정보: bwin이 SQL Server 2016(13.x) In-Memory OLTP를 사용하여 전례 없는 성능 및 규모를 달성하는 방법

구현 고려 사항

varbinary(max) 열에 BLOB을 저장하여 영구 메모리 최적화 테이블을 간단한 키-값 저장소로 사용할 수 있습니다. 또는 SQL Server 및 SQL Database JSON 지원을 사용하여 반구조화된 캐시를 구현할 수 있습니다. 마지막으로, 다양한 데이터 형식 및 제약 조건 등 완전한 관계형 스키마를 사용하여 비영구 테이블을 통해 완전한 관계형 캐시를 만들 수 있습니다.

GitHub에 게시된 스크립트를 사용하여 기본 제공 SQL Server 세션 상태 공급자인 aspnet-session-state에서 만든 개체를 대체하여 메모리 최적화 ASP.NET 세션 상태 시작

고객 사례 연구

tempdb 개체 대체

비지속성 테이블 및 메모리 최적화 테이블 형식을 사용하여 임시 테이블, 테이블 변수 및 TVP(테이블 반환 매개 변수)와 같은 기존 tempdb 기반 구조를 대체합니다.

메모리 액세스에 최적화된 테이블 변수 및 비영구 테이블은 일반적으로 기존 테이블 변수 및 #temp 테이블과 비교할 때 CPU를 줄이고 로그 IO를 완전히 제거합니다.

구현 고려 사항

시작하려면 메모리 최적화를 사용하여 임시 테이블 및 테이블 변수 성능 향상을 참조하세요.

고객 사례 연구

ETL(추출, 변환, 로드)

ETL 워크플로에는 종종 준비 테이블로 데이터 로드, 데이터 변환 및 최종 테이블에 로드가 포함됩니다.

데이터 준비에 비영구 메모리 최적화 테이블을 사용합니다. 모든 IO가 완전히 제거되고 데이터 액세스 효율성이 향상됩니다.

구현 고려 사항

워크플로의 일환으로 준비 테이블에서 변환을 수행하는 경우 고유하게 컴파일된 저장 프로시저를 사용하여 이러한 변환을 가속화할 수 있습니다. 이러한 변환을 동시에 수행할 수 있으면 메모리 최적화에서 추가적인 크기 조정 관련 이점을 얻을 수 있습니다.

샘플 스크립트

메모리 내 OLTP 사용을 시작하기 전에 먼저 MEMORY_OPTIMIZED_DATA 파일 그룹을 만들어야 합니다. 또한 데이터베이스 호환성 수준 130 이상을 사용하고 데이터베이스 옵션 MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT을 ON으로 설정하는 것이 좋습니다.

다음 위치에서 스크립트를 사용하여 기본 데이터 폴더에 파일 그룹을 만들고 권장 설정을 구성할 수 있습니다.

다음 샘플 스크립트는 데이터베이스에서 만들 수 있는 In-Memory OLTP 개체를 보여 줍니다.

먼저 In-Memory OLTP에 대한 데이터베이스를 구성합니다.

-- configure recommended DB option
ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON;
GO

내구성이 다른 테이블을 만들 수 있습니다.

-- memory-optimized table
CREATE TABLE dbo.table1
( c1 INT IDENTITY PRIMARY KEY NONCLUSTERED,
  c2 NVARCHAR(MAX))
WITH (MEMORY_OPTIMIZED=ON);
GO
-- non-durable table
CREATE TABLE dbo.temp_table1
( c1 INT IDENTITY PRIMARY KEY NONCLUSTERED,
  c2 NVARCHAR(MAX))
WITH (MEMORY_OPTIMIZED=ON,
      DURABILITY=SCHEMA_ONLY);
GO

메모리 내 테이블로 테이블 형식을 만들 수 있습니다.

-- memory-optimized table type
CREATE TYPE dbo.tt_table1 AS TABLE
( c1 INT IDENTITY,
  c2 NVARCHAR(MAX),
  is_transient BIT NOT NULL DEFAULT (0),
  INDEX ix_c1 HASH (c1) WITH (BUCKET_COUNT=1024))
WITH (MEMORY_OPTIMIZED=ON);
GO

고유하게 컴파일된 저장 프로시저를 만들 수 있습니다. 자세한 내용은 데이터 액세스 애플리케이션에서 고유하게 컴파일된 저장 프로시저 호출을 참조하세요.

-- natively compiled stored procedure
CREATE PROCEDURE dbo.usp_ingest_table1
  @table1 dbo.tt_table1 READONLY
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC
    WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT,
          LANGUAGE=N'us_english')

  DECLARE @i INT = 1

  WHILE @i > 0
  BEGIN
    INSERT dbo.table1
    SELECT c2
    FROM @table1
    WHERE c1 = @i AND is_transient=0

    IF @@ROWCOUNT > 0
      SET @i += 1
    ELSE
    BEGIN
      INSERT dbo.temp_table1
      SELECT c2
      FROM @table1
      WHERE c1 = @i AND is_transient=1

      IF @@ROWCOUNT > 0
        SET @i += 1
      ELSE
        SET @i = 0
    END
  END

END
GO
-- sample execution of the proc
DECLARE @table1 dbo.tt_table1;
INSERT @table1 (c2, is_transient) VALUES (N'sample durable', 0);
INSERT @table1 (c2, is_transient) VALUES (N'sample non-durable', 1);
EXECUTE dbo.usp_ingest_table1 @table1=@table1;
SELECT c1, c2 from dbo.table1;
SELECT c1, c2 from dbo.temp_table1;
GO

참조 리소스

추가 정보

다음 단계