메모리 최적화 표 : SQL Server 구현 전략

SQL Server DBA로서 15 년간의 여정에서, 나는 혁신적인 성능 향상을 유망한 수많은 기술을 만났지만 메모리 최적화 된 테이블만큼 설득력있게 전달 된 사람은 거의 없습니다.

Memory OLTP 기능의 일부로 SQL Server 2014에 처음 등장했습니다.헤카톤“),이 기술은 후속 릴리스에서 크게 성숙해졌습니다. 기본 개념은 우아하게 간단합니다. 기존 테이블은 디스크에 살고 메모리에 메모리에로드되어야합니다. 메모리-최적화 된 테이블은 지속 메커니즘으로 만 디스크 스토리지를 사용하여 메모리에 영구적으로 존재합니다.

이 아키텍처 변화는 버퍼 풀 관리, 잠금 메커니즘 및 래치 경합을 포함하여 디스크 기반 테이블과 관련된 여러 개의 오버 헤드 레이어를 제거합니다. 오늘,이 테이블이 최대 값을 제공 할 때, 왜 구현을 고려해야하는지, 생산 환경에서 성공적으로 배포하는 방법에 대한 실습 통찰력을 공유하겠습니다.

메모리 최적화 된 테이블을 사용하는 시점

기존 디스크 기반 테이블이 종종 병목 현상이되는 여러 특정 시나리오에서 메모리 최적화 된 테이블이 뛰어납니다. 하나의 주요 사용 사례는 초당 수천 개의 작업을 처리하는 대량 트랜잭션 시스템입니다. 나는 최근에 환자 등록 시스템이 전국에 동시에 클리닉이 문을 연 아침 시간 동안 상당한 둔화를 겪고있는 의료 서비스 제공 업체와 함께 일했습니다. 등록 테이블은 초당 약 3,000 개의 삽입/업데이트 작업을 처리하여 상당한 잠금 경합을 만듭니다.

메모리에서 최적화 된 테이블 설계로 마이그레이션 한 후 등록 시간은 초에서 밀리 초까지 감소했으며 시스템은 성능 저하없이 피크 하중을 쉽게 처리했습니다. 또 다른 우수한 애플리케이션은 매우 낮은 대기 시간이 필요한 시스템입니다. 통신 클라이언트는 서비스 수준 계약을 준수하기 위해 밀리 초가 중요한 통화 라우팅 시스템으로 어려움을 겪었습니다. 라우팅 테이블을 메모리 최적화 스토리지로 옮기고 기본적으로 컴파일 된 저장 프로 시저를 구현함으로써 라우팅 결정 시간을 12ms에서 0.8ms로 ​​줄였습니다.

메모리 최적화 된 테이블도 TempDB 경합 문제를 해결하는 데 빛납니다. 전자 상거래 플랫폼은 제품 추천 엔진이 수천 개의 임시 테이블을 동시에 만들었을 때 휴가 판매 중에 심각한 TempDB 병목 현상을 경험하고있었습니다. 이를 메모리 최적화 된 테이블 변수로 대체하면 경합이 완전히 제거되었으며 권장 생성 프로세스에 60% 성능 향상을 제공했습니다. 세션 상태 관리는 또 다른 완벽한 착용감을 나타냅니다. 동시 사용자가 50,000 명의 웹 애플리케이션은 전통적인 테이블에 세션 정보를 저장하여 피크 사용 중에 상당한 차단을 유발합니다. 메모리에서 최적화 된 테이블로 이동하면이 병목 현상이 완전히 제거되어 사용자로드에 관계없이 일관된 밀리 초의 응답 시간이 제공됩니다.

메모리 최적화 된 테이블을 구현하는 이유

메모리 최적화 된 테이블의 매력적인 성능 장점은 완전히 재 설계된 아키텍처에서 비롯됩니다. 기존 테이블은 행 버전 및 복잡한 잠금 장치를 사용하여 동시 액세스를 관리하여 무거운 하중에서 경합을 만듭니다. 대조적으로, 메모리-최적화 된 테이블은 잠금이없는 데이터 구조와 낙관적 인 멀티이션 동시성 제어 모델을 사용하여 여러 트랜잭션이 서로를 차단하지 않고 동시에 데이터에 액세스하고 수정할 수 있도록합니다. 이 근본적인 차이는 일반적으로 적합한 워크로드의 경우 5 배에서 40 배 범위의 성능 향상을 산출합니다.

메모리 최적화 된 테이블은 여러 성능 억제제를 동시에 제거합니다. 데이터는 페이징되지 않고 메모리에 영구적으로 상주하기 때문에 버퍼 풀 압력을 제거합니다. 그들은 페이지 및 범위 할당과 같은 내부 구조에서 래치 경합을 제거합니다. 가장 중요한 것은 전통적인 잠금 장치를 낙관적 동시성 제어로 대체하는데, 여기서 트랜잭션은 잠금 장치를 얻지 않고 자체 행 버전에서 작동합니다. 이 접근법은 전통적인 잠금이 병목 현상을 생성하는 높은 일환 시나리오에서 처리량을 크게 증가시킵니다.

기본적으로 컴파일 된 저장 절차를 사용하는 능력은이 기술을 채택해야 할 또 다른 강력한 이유를 나타냅니다. 메모리에서 최적화 된 테이블과 쌍을 이루면이 절차는 런타임에 해석되지 않고 기계 코드로 직접 컴파일합니다. 패키지 라우팅 알고리즘이 기존 해석 된 T-SQL을 사용하여 150ms로 실행 한 물류 회사와 협력했습니다. 기본적으로 컴파일 된 프로 시저로 변환 한 후 메모리에서 최적화 된 테이블에 액세스 한 후 실행 시간은 단지 4ms로 떨어 졌기 때문에 배치 대신 전체 차량의 라우팅 결정을 거의 실시간으로 처리 할 수 ​​있습니다.

메모리 최적화 된 테이블을 구현하는 방법

메모리에서 최적화 된 테이블 구현은 데이터베이스 수준에서 올바른 설정으로 시작됩니다. 메모리에서 최적화 된 데이터 지속성 파일을 저장하려면 먼저 전용 파일 그룹을 작성해야합니다. 다음은 이전의 구현 예입니다.

-- Add a Memory-Optimized filegroup
ALTER DATABASE InventoryManagement
ADD FILEGROUP InventoryManagement_MemoryOptimized CONTAINS MEMORY_OPTIMIZED_DATA;

-- Add a file to the new filegroup
ALTER DATABASE InventoryManagement
ADD FILE (NAME='InventoryMO_Data', FILENAME='D:\MSSQL\Data\InventoryMO_Data')
TO FILEGROUP InventoryManagement_MemoryOptimized;

인프라를 사용하면 메모리 최적화 된 테이블을 만들 수 있습니다. 구문에는 몇 가지 고유 한 요소가 포함되어 있습니다.

CREATE TABLE dbo.ProductInventory
(
    ProductID INT NOT NULL PRIMARY KEY NONCLUSTERED,
    WarehouseID INT NOT NULL,
    QuantityOnHand INT NOT NULL,
    LastUpdated DATETIME2 NOT NULL,
    UpdatedBy VARCHAR(50) NOT NULL,
    INDEX ix_Warehouse HASH (WarehouseID) WITH (BUCKET_COUNT = 64)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

전통적인 테이블 생성과 몇 가지 주요 차이점에 주목하십시오. 그만큼 PRIMARYKEY 명시 적으로 선언되어야합니다 NONCLUSTERED. 그만큼 HASH WarehouseId의 색인은 필요합니다 BUCKET_COUNT 사양은 충돌을 최소화하기 위해 열의 고유 값 수의 1-2 배 여야합니다.

그만큼 MEMORY_OPTIMIZED = ON 설정 설정은 메모리 내 기능을 활성화하는 반면 DURABILITY 설정은 지속성 동작을 결정합니다. SCHEMA_AND_DATA 구조와 컨텐츠가 서버 재시작을 살아 남기 위해서 SCHEMA_ONLY 지속될 필요가없는 임시 데이터에 적합합니다. 최적의 성능을 위해서는 메모리 최적화 된 테이블을 기본적으로 컴파일 된 저장 프로 시저와 페어링하십시오. 예는 다음과 같습니다.

CREATE PROCEDURE dbo.UpdateInventoryQuantity
    @ProductID INT,
    @WarehouseID INT,
    @QuantityChange INT,
    @UpdatedBy VARCHAR(50)
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC WITH
    (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'English')
    
    UPDATE dbo.ProductInventory
    SET QuantityOnHand = QuantityOnHand + @QuantityChange,
        LastUpdated = SYSUTCDATETIME(),
        UpdatedBy = @UpdatedBy
    WHERE ProductID = @ProductID AND WarehouseID = @WarehouseID;
    
    IF @@ROWCOUNT = 0
    BEGIN
        INSERT INTO dbo.ProductInventory 
            (ProductID, WarehouseID, QuantityOnHand, LastUpdated, UpdatedBy)
        VALUES 
            (@ProductID, @WarehouseID, @QuantityChange, SYSUTCDATETIME(), @UpdatedBy);
    END
END;

이 절차는 기본적으로 컴파일 된 모듈과 관련된 몇 가지 요구 사항을 보여줍니다. NATIVE_COMPILATION 그리고 SCHEMABINDING 옵션, ATOMIC 차단 및 명시 적 트랜잭션 격리 수준. 이러한 요소를 사용하면 SQL Server가 해석의 오버 헤드없이 메모리에서 최적화 된 테이블 구조에 직접 액세스하는 효율적인 기계 코드로 절차를 컴파일 할 수 있습니다.

마이그레이션 및 고려 사항

메모리에서 최적화 된 테이블로 마이그레이션 할 때 체계적인 접근 방식은 최상의 결과를 얻습니다. 쿼리 스토어, 확장 이벤트 또는 타사 모니터링 솔루션과 같은 도구를 사용하여 철저한 워크로드 분석으로 시작하여 최고 수준의 테이블을 식별하는 것이 좋습니다. 내가 함께 일한 교육 소프트웨어 회사는 학생 로그인 세션 및 과제 진행 추적 (학생 로그인 세션 및 과제 진행 추적)이 데이터베이스 경합의 70%를 차지한다는 것을 발견했습니다. 그들은이 테이블에 초기 마이그레이션 노력을 집중시켜 최소한의 위험으로 상당한 성능 향상을 달성했습니다.

메모리 요구 사항에는 신중한 계획이 필요합니다. 메모리 최적화 테이블은 일반적으로 버전 작성 인프라 및 인덱스 구조로 인해 데이터 크기보다 2-3 배 더 많은 메모리가 필요합니다. 8GB의 실제 데이터가 장착 된 소매 재고 시스템은 효율적으로 작동하기 위해 약 22GB의 메모리가 필요합니다. 서버에 충분한 자원이 있는지 확인하십시오. 특히 기존 테이블 용 ColumnStore 인덱스 또는 대형 버퍼 풀과 같은 추가 메모리 집약적 기능을 실행하는 경우.

모든 기능이 메모리 최적화 된 테이블과 호환되는 것은 아니므로 일부 설계 적응이 필요합니다. 제조 회사의 품질 관리 시스템은 여러 테이블을 참조하는 트리거 및 외국 주요 제약 조건에 크게 의존했습니다. 이전에 선언적 제약으로 처리 된 것과 동일한 비즈니스 규칙을 시행하기 위해 기본적으로 컴파일 된 저장 절차를 사용하여 여러 구성 요소를 재 설계해야했습니다. 이것은 추가 개발 노력이 필요했지만 궁극적으로 원래 디자인보다 더 나은 성능을 제공했습니다.

메모리 최적화 테이블의 장점은 상당합니다. OLTP 워크로드의 극적인 성능 개선, 차단 및 잠금 문제 제거, 동일한 워크로드의 CPU 사용량 감소 및 하드웨어 업그레이드없이 상당히 높은 트랜잭션 볼륨을 처리하는 기능. 금융 서비스 고객은 마이그레이션 후 기존 하드웨어에서 8 배 높은 처리량을 달성하여 비용이 많이 드는 인프라 확장을 피했습니다. 단점도 고려해야합니다. 하드웨어 업그레이드가 필요할 수있는 메모리 요구 사항 증가, 기능 제한 내에서 작동에 대한 잠재적 코드 변경, 낙관적 동시성 개념에 익숙하지 않은 개발 팀을위한 가파른 학습 곡선, 메모리가 부족한 이후보다 신중한 용량 계획에 대한 필요성이 심각한 결과를 초래할 수 있습니다. 또한 복잡한 분석 쿼리와 같은 특정 워크로드 유형은이 기술을 통해 이점이 없으며 잠재적으로 더 악화 될 수 있습니다.

모니터링은 메모리에서 최적화 된 테이블을 사용하여 중요합니다. 동적 관리 뷰를 사용하십시오 sys.dm_db_xtp_memory_consumers 메모리 사용량을 추적합니다 sys.dm_db_xtp_hash_index_stats 잠재적 해시 충돌 문제를 식별합니다. 소매 회사는 메모리 활용이 80%를 초과했을 때 또는 해시 인덱스 체인 길이 평균이 사전 결정된 임계 값을 교차했을 때 자동화 된 경고를 조언하여 성능에 적극적으로 영향을 미치기 전에 잠재적 인 문제를 해결할 수있었습니다.

결론

메모리 최적화 된 테이블은 처리량이 많은 트랜잭션 워크로드에 대한 SQL Server의 가장 혁신적인 기능 중 하나를 나타냅니다. 적절한 시나리오를 위해 신중하게 구현되면 겉보기에 다루기 어려운 성능 문제를 해결하고 응용 프로그램 대응 성을 극적으로 향상시킬 수 있습니다. 이 기술은 소개 이후 크게 성숙해졌으며 각 SQL 서버 버전은 정제를 추가하고 제한을 제거합니다.

데이터베이스 관리에서 15 년이 지난 후, 나는 가장 성공적인 기술 구현이 혁신과 실용주의를 결합한다는 것을 알게되었습니다. 메모리에서 최적화 된 테이블은이 균형을 표현하여 친숙한 SQL 서버 개념 및 도구와 통합하면서 혁신적인 성능 기능을 제공합니다. 워크로드 특성에 대한 명확한 평가로 시작하고, 측정 된 단계로 구현하고, 부지런히 모니터링하면,이 기술이 상당한 약속을 지니고 있음을 알게 될 것입니다. 메모리에서 최적화 된 테이블을 올바르게 구현하는 데 필요한 노력은 상당하지만 올바른 시나리오의 경우 이러한 최적화 기술은 이러한 극적이고 즉각적인 결과를 제공 할 수 거의 없습니다.