적용 대상:
SQL ServerAzure SQL 데이터베이스Azure SQL Managed Instance
메모리 최적화 테이블에는 모든 행과 인덱스를 메모리에 유지하기에 충분한 메모리가 있어야 합니다. 메모리는 한정된 리소스이므로 시스템에서 메모리 사용량을 파악하고 관리해야 합니다. 이 섹션의 항목에서는 일반적인 메모리 사용 및 관리 시나리오에 대해 설명합니다.
충분한 메모리로 서버를 프로비전할 수 있도록 각 메모리 최적화 테이블의 메모리 요구 사항을 합리적으로 예측하는 것이 중요합니다. 새 테이블과 디스크 기반 테이블에서 마이그레이션된 테이블에 모두 적용됩니다. 이 섹션에서는 메모리 최적화 테이블의 데이터를 저장하는 데 필요한 메모리 양을 예측하는 방법에 대해 설명합니다.
디스크 기반 테이블에서 메모리 최적화 테이블로의 마이그레이션을 고려하는 경우 마이그레이션에 가장 적합한 테이블에 대한 지침은 테이블 또는 저장 프로시저를 In-Memory OLTP로 이식해야 하는지 여부를 확인 하세요. 메모리 내 OLTP로 마이그레이션의 모든 항목은 디스크 기반에서 메모리 최적화 테이블로 마이그레이션하는 방법에 대한 지침을 제공합니다.
메모리 요구 사항을 예측하기 위한 기본 지침
SQL Server 2016(13.x) 이상 버전에서는 메모리 최적화 테이블의 크기에 제한이 없지만 테이블은 메모리에 맞아야 합니다. SQL Server 2014(12.x)에서 지원되는 데이터 크기는 SCHEMA_AND_DATA 테이블에 대해 256GB입니다.
메모리 최적화 테이블의 크기는 데이터 크기와 행 헤더의 일부 오버헤드에 해당합니다. 메모리 최적화 테이블의 크기는 대략 원래 디스크 기반 테이블의 클러스터형 인덱스 또는 힙 크기에 해당합니다.
메모리 최적화 테이블의 인덱스는 디스크 기반 테이블의 비클러스터형 인덱스보다 작은 경향이 있습니다. 비클러스터형 인덱스의 크기는 [primary key size] * [row count] 순입니다. 해시 인덱스의 크기는 [bucket count] * 8 bytes입니다.
활성 워크로드가 있는 경우 행 버전 관리 및 다양한 작업을 고려하려면 추가 메모리가 필요합니다. 필요한 메모리 양은 워크로드에 따라 달라지지만 안전하려면 메모리 최적화 테이블 및 인덱스의 예상 크기의 2배로 시작하고 실제 메모리 사용량을 관찰하는 것이 좋습니다. 행 버전 관리 오버헤드는 항상 워크로드의 특성에 따라 달라집니다. 특히 장기 실행 트랜잭션은 오버헤드를 증가시킵니다. 더 큰 데이터베이스를 사용하는 대부분의 워크로드(예: 100GB 초과)의 경우 오버헤드가 제한되는 경향이 있습니다(25% 이하).
In-Memory OLTP 엔진의 잠재적인 메모리 오버헤드에 대한 자세한 내용은 메모리 조각화를 참조하세요.
메모리 요구 사항의 자세한 계산
메모리 최적화 테이블의 예
다음 메모리 최적화 테이블 스키마를 고려합니다.
CREATE TABLE t_hk
(
col1 int NOT NULL PRIMARY KEY NONCLUSTERED,
col2 int NOT NULL INDEX t1c2_index
HASH WITH (bucket_count = 5000000),
col3 int NOT NULL INDEX t1c3_index
HASH WITH (bucket_count = 5000000),
col4 int NOT NULL INDEX t1c4_index
HASH WITH (bucket_count = 5000000),
col5 int NOT NULL INDEX t1c5_index NONCLUSTERED,
col6 char (50) NOT NULL,
col7 char (50) NOT NULL,
col8 char (30) NOT NULL,
col9 char (50) NOT NULL
) WITH (memory_optimized = on) ;
GO
이 스키마를 사용하여 이 메모리 최적화 테이블에 필요한 최소 메모리를 결정해 보겠습니다.
테이블용 메모리
메모리 최적화 테이블 행에는 다음 세 부분으로 구성됩니다.
Timestamps
행 머리글/타임스탬프 = 24바이트인덱스 포인터
테이블의 각 해시 인덱스마다 각 행에는 인덱스 내 다음 행에 대한 8바이트 주소 포인터가 있습니다. 4개의 인덱스가 있으므로 각 행은 인덱스 포인터에 32바이트(각 인덱스에 대한 8바이트 포인터)를 할당합니다.Data
행의 데이터 부분 크기는 각 데이터 열의 형식 크기를 합하여 결정됩니다. 표에는 5개의 4바이트 정수, 3개의 50바이트 문자 열 및 1개의 30바이트 문자 열이 있습니다. 따라서 각 행의 데이터 부분은 4 + 4 + 4 + 4 + 4 + 50 + 50 + 30 + 50 또는 200바이트입니다.
다음은 메모리 최적화 테이블에 있는 5,000,000(5백만)개 행에 대한 크기 계산입니다. 데이터 행에서 사용하는 총 메모리는 다음과 같이 예측됩니다.
테이블 행의 메모리
위의 계산에서 메모리 최적화 테이블의 각 행 크기는 24 + 32 + 200 또는 256바이트입니다. 행이 500만 개이므로 테이블은 5,000,000 * 256바이트 또는 1,280,000,000바이트(약 1.28GB)를 사용합니다.
인덱스에 대한 메모리
각 해시 인덱스 메모리
각 해시 인덱스는 8바이트 주소 포인터의 해시 배열입니다. 배열의 크기는 해당 인덱스의 고유 인덱스 값 수에 따라 가장 잘 결정됩니다. 현재 예제에서 고유한 Col2 값의 수는 t1c2_index 배열 크기에 적합한 시작점입니다. 해시 배열이 너무 크면 메모리가 낭비되고, 너무 작은 해시 배열은 동일한 인덱스 항목에 해시하는 인덱스 값에 의한 충돌이 너무 많기 때문에 성능이 저하됩니다.
해시 인덱스는 다음과 같이 매우 빠른 같음 조회를 달성합니다.
SELECT * FROM t_hk
WHERE Col2 = 3;
비클러스터형 인덱스는 다음과 같은 범위 조회에 더 빠릅니다.
SELECT * FROM t_hk
WHERE Col2 >= 3;
디스크 기반 테이블을 마이그레이션하는 경우 다음을 사용하여 인덱스 t1c2_index 고유 값의 수를 확인할 수 있습니다.
SELECT COUNT(DISTINCT [Col2])
FROM t_hk;
새 테이블을 만드는 경우 배포 전에 배열 크기를 예측하거나 테스트에서 데이터를 수집해야 합니다.
메모리 내 OLTP 메모리 최적화 테이블에서 해시 인덱스가 작동하는 방법에 대한 자세한 내용은 해시 인덱스를 참조하세요.
해시 인덱스 배열 크기 설정
해시 배열 크기는 (bucket_count= value)가 0보다 큰 정수 값인 value에 의해 설정됩니다.
value가 2의 거듭제곱이 아닌 경우 실제 bucket_count는 다음으로 가장 가까운 2의 거듭제곱으로 반올림됩니다. 예제 테이블(bucket_count = 5000000)에서 5,000,000은 2의 거듭제곱이 아니므로 실제 버킷 수는 8,388,608(2^23)까지 반올림됩니다. 해시 배열에 필요한 메모리를 계산할 때 5,000,000이 아닌, 이 숫자를 사용해야 합니다.
따라서 이 예제에서 각 해시 배열에 필요한 메모리는 다음과 같습니다.
8,388,608 * 8 = 2^23 * 8 = 2^23 * 2^3 = 2^26 = 67,108,864 또는 약 64MB.
해시 인덱스가 세 개 있으므로 해시 인덱스에 필요한 메모리는 3 * 64MB = 192MB입니다.
비클러스터형 인덱스에 대한 메모리
비클러스터형 인덱스는 인덱스 값과 이후 노드에 대한 포인터가 포함된 내부 노드가 있는 Bw-트리로 구현됩니다. 리프 노드에는 인덱스 값과 메모리의 테이블 행에 대한 포인터가 포함됩니다.
해시 인덱스와 달리 비클러스터형 인덱스에는 고정된 버킷 크기가 없습니다. 인덱스는 데이터와 함께 동적으로 증가하고 축소됩니다.
비클러스터형 인덱스에 필요한 메모리는 다음과 같이 계산할 수 있습니다.
비리프 노드에 할당된 메모리
일반적인 구성의 경우 비리프 노드에 할당된 메모리는 인덱스가 사용하는 전체 메모리의 작은 비율입니다. 이 크기는 너무 작아서 무시해도 됩니다.리프 노드에 대한 메모리
리프 노드에는 테이블의 고유 키마다 행이 하나씩 있으며 이 행은 해당 고유 키가 있는 데이터 행을 가리킵니다. 키가 같은 행이 여러 개 있는 경우(즉, 비클러스터형 비고유 인덱스가 있는 경우) 인덱스 리프 노드에는 다른 행과 연결된 행 중 하나를 가리키는 하나의 행만 포함됩니다. 따라서 필요한 총 메모리는 다음을 통해 근사값을 계산할 수 있습니다.- 비클러스터형 인덱스에 대한 메모리 = (포인터 크기 + 키 열 데이터 유형 크기의 합계) * 고유 키가 있는 행 수
비클러스터형 인덱스는 다음 쿼리에서 예로 들 수 있듯이 범위 조회에 사용할 때 가장 적합합니다.
SELECT * FROM t_hk
WHERE c2 > 5;
행 버전 관리에 대한 메모리
잠금을 방지하기 위해 메모리 내 OLTP는 행을 업데이트하거나 삭제할 때 낙관적 동시 실행을 사용합니다. 즉, 행이 업데이트될 때 행의 또 다른 버전이 만들어집니다. 또한 삭제는 논리적입니다. 기존 행은 삭제된 것으로 표시되지만 즉시 제거되지는 않습니다. 시스템은 버전 실행을 완료할 수 있는 모든 트랜잭션이 완료될 때까지 이전 행 버전(삭제된 행 포함)을 사용할 수 있도록 유지합니다.
언제든지 가비지 수집 주기에 따라 메모리가 해제되기를 기다리는 많은 추가 행이 메모리에 있을 수 있기 때문에 이러한 추가 행을 수용하는 데 충분한 메모리가 있어야 합니다.
추가 행 수는 초당 최대 행 업데이트 및 삭제 수를 계산한 다음, 가장 긴 트랜잭션에 걸리는 시간(최소 1초)을 곱하여 예측할 수 있습니다.
그런 다음 해당 값에 행 크기를 곱하여 행 버전 관리에 필요한 바이트 수를 가져옵니다.
rowVersions = durationOfLongestTransactionInSeconds * peakNumberOfRowUpdatesOrDeletesPerSecond
부실 행에 대한 메모리 요구는 부실 행 수와 메모리 최적화 테이블 행의 크기를 곱하여 예측됩니다. 자세한 내용은 테이블의 메모리를 참조하세요.
memoryForRowVersions = rowVersions * rowSize
테이블 변수에 대한 메모리
테이블 변수에 사용되는 메모리는 테이블 변수가 범위를 벗어나는 경우에만 해제됩니다. 테이블 변수에서 업데이트의 일부로 삭제된 행을 포함하여 삭제된 행에는 가비지 수집이 적용되지 않습니다. 테이블 변수가 범위를 종료할 때까지 메모리가 해제되지 않습니다.
저장 프로시저 대신 큰 SQL 일괄 처리에 정의되고 많은 트랜잭션에서 사용되는 테이블 변수는 많은 양의 메모리를 사용할 수 있습니다. 테이블 변수는 가비지 수집되지 않기 때문에 테이블 변수의 삭제된 행은 다량의 메모리를 사용할 수 있으며 읽기 작업이 삭제된 행을 통과하여 검색해야 하므로 성능이 저하될 수 있습니다.
증가를 위한 메모리
이전 계산에서는 현재 존재하는 테이블에 대한 메모리 요구 사항을 추정합니다. 이 메모리 외에도 테이블의 증가를 예측하고 해당 증가를 수용할 수 있는 충분한 메모리를 제공해야 합니다. 예를 들어 10% 증가를 예상하는 경우 테이블에 필요한 총 메모리를 얻으려면 기존 결과에 1.1을 곱해야 합니다.
메모리 조각화
메모리 할당 호출의 오버헤드를 방지하고 성능을 향상시키기 위해 In-Memory OLTP 엔진은 항상 슈퍼락이라고 하는 64KB 블록을 사용하여 SQLOS(SQL Server 운영 체제)에서 메모리를 요청합니다.
각 슈퍼블록에는 sizeclass라고 하는 특정 크기 범위 내에서만 메모리 할당이 포함됩니다. 예를 들어, superblock A 는 1-16바이트 sizeclass에 메모리 할당이 있을 수 있고, 슈퍼록 B 는 17-32바이트 sizeclass 등에 메모리 할당이 있을 수 있습니다.
기본적으로 슈퍼 블록은 논리 CPU에 의해 분할됩니다. 즉, 각 논리 CPU에 대해 별도의 슈퍼블록 집합이 있으며 sizeclass별로 더 세분화됩니다. 이렇게 하면 여러 CPU에서 실행되는 요청 간의 메모리 할당 경합이 줄어듭니다.
In-Memory OLTP 엔진이 새 메모리 할당을 만들 때 먼저 요청된 sizeclass 및 요청을 처리하는 CPU에 대한 기존 슈퍼블록에서 사용 가능한 메모리를 찾으려고 시도합니다. 이 시도가 성공하면 특정 메모리 소비자에 used_bytes 대한 sys.dm_xtp_system_memory_consumers 열의 값이 요청된 메모리 크기로 증가하지만 열의 allocated_bytes 값은 동일하게 유지됩니다.
기존 슈퍼블록에 사용 가능한 메모리가 없는 경우 새 슈퍼블록이 할당되고 요청된 메모리 크기에 따라 값 used_bytes 이 증가하는 반면 열의 값 allocated_bytes 은 64KB 증가합니다.
시간이 지남에 따라 슈퍼 블록의 메모리가 할당되고 할당 취소되므로 In-Memory OLTP 엔진에서 사용하는 총 메모리 양이 사용된 메모리 양보다 훨씬 커질 수 있습니다. 즉, 메모리가 조각화될 수 있습니다.
가비지 수집 은 사용된 메모리를 줄일 수 있지만 하나 이상의 슈퍼 블록이 비어 할당 취소된 경우에만 할당된 메모리를 줄입니다. 이는 sys.sp_xtp_force_gc 시스템 저장 프로시저를 사용하는 자동 및 강제 가비지 수집 모두에 적용됩니다.
In-Memory OLTP 엔진 메모리 조각화 및 할당된 메모리 사용량이 예상보다 높아지면 추적 플래그 9898을 사용하도록 설정할 수 있습니다. 이렇게 하면 슈퍼 잠금 분할 체계가 CPU당에서 NUMA 노드당으로 변경되어 총 슈퍼록 수와 높은 메모리 조각화 가능성이 줄어듭니다.
이 최적화는 논리 CPU가 많은 대형 머신에 더 적합합니다. 이 최적화의 장단분은 메모리 할당 경합의 잠재적 증가로 인해 수퍼락 수가 줄어들어 전체 워크로드 처리량이 감소할 수 있습니다. 워크로드 패턴에 따라 NUMA당 메모리 분할 사용으로 인한 처리량 감소가 눈에 띄거나 눈에 띄지 않을 수 있습니다.