다음을 통해 공유

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

  • 아티클

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

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

메모리 내 OLTP 개요

메모리 내 OLTP는 적절한 워크로드에서 상당한 성능 향상 효과를 제공할 수 있습니다. 어떤 경우에는 고객이 최대 30배의 성능 향상을 달성했지만 효과는 워크로드에 따라 달라집니다.

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

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

데이터베이스에서 메모리 내 OLTP를 활용하려면 다음과 같은 유형의 개체 중 하나 이상을 사용합니다.

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

메모리 내 OLTP는 SQL Server 및 SQL Database에 기본 제공되어 있습니다. 이러한 개체는 기존 개체와 유사하게 동작하기 때문에 데이터베이스 및 애플리케이션에 대한 최소한의 변경으로 성능 이점을 얻을 수 있습니다. 또한 메모리 최적화 테이블과 기존 디스크 기반 테이블을 모두 동일한 데이터베이스에 두고 두 데이터베이스에서 쿼리를 실행할 수 있습니다. 이 문서의 뒷부분에 있는 이러한 개체 유형 각각에 대한 샘플 Transact-SQL 스크립트를 참조하세요.

메모리 내 OLTP의 사용 시나리오

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

주의

알려진 문제: 메모리 최적화 테이블이 있는 데이터베이스의 경우 복구 없이 트랜잭션 로그 백업을 수행한 후 나중에 복구를 사용하여 트랜잭션 로그 복원을 실행하면 데이터베이스 복원 프로세스가 응답하지 않을 수 있습니다. 이 문제는 로그 전달 기능에도 영향을 줄 수 있습니다. 이 문제를 해결하려면 복원 프로세스를 시작하기 전에 SQL Server 인스턴스를 다시 시작할 수 있습니다.

다음은 메모리 내 OLTP를 성공적으로 사용한 고객의 시나리오 및 애플리케이션 패턴 목록입니다.

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

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

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

구현 고려 사항

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

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

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

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

메모리 내 OLTP는 다양한 원본에서 동시에 많은 양의 데이터를 수집하는 데 유용합니다. 또한 다른 대상보다 SQL Server 데이터베이스로 데이터를 수집하는 데 유용한 경우가 많습니다. SQL Server는 데이터에 대한 쿼리를 매우 빠르게 실행하고 실시간 인사이트를 제공하기 때문입니다.

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

  • 센서 판독값 및 이벤트를 수집하고 기록 분석뿐 아니라 알림도 허용합니다.
  • 동시 읽기 워크로드에 미치는 영향을 최소화하면서 여러 원본에서도 일괄 업데이트를 관리합니다.

구현 고려 사항

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

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

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

캐싱 및 세션 상태

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

ASP.NET 세션 상태는 매우 성공적인 메모리 내 OLTP 사용 사례입니다. SQL Server를 사용하면 한 고객이 초당 120만 개의 요청을 달성할 수 있습니다. 그 동안 엔터프라이즈의 모든 중간 계층 애플리케이션의 캐싱 요구 사항을 위해 메모리 내 OLTP를 사용하기 시작했습니다. 세부 정보: bwin에서 SQL Server 2016(13.x) 메모리 내 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으로 설정하는 것이 좋습니다.

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

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

먼저 메모리 내 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

관련 콘텐츠