메모리 최적화 테이블의 메모리 요구 사항 예측
적용 대상:
SQL ServerAzure SQL 데이터베이스Azure SQL Managed Instance
메모리 최적화 테이블에는 모든 행과 인덱스를 메모리에 유지하기에 충분한 메모리가 있어야 합니다. 메모리는 한정된 리소스이므로 시스템에서 메모리 사용량을 파악하고 관리해야 합니다. 이 섹션의 항목에서는 일반적인 메모리 사용 및 관리 시나리오에 대해 설명합니다.
새로운 메모리 최적화 테이블을 만들든, 아니면 기존 디스크 기반 테이블을 메모리 내 OLTP 메모리 최적화 테이블로 마이그레이션하든 간에 충분한 메모리로 서버를 프로비전할 수 있도록 각 테이블에 필요한 메모리를 적절히 예측하는 것이 중요합니다. 이 섹션에서는 메모리 최적화 테이블의 데이터를 저장하는 데 필요한 메모리 양을 예측하는 방법에 대해 설명합니다.
디스크 기반 테이블에서 메모리 최적화 테이블로 마이그레이션하려는 경우 이 항목을 진행하기 전에 마이그레이션에 가장 적합한 테이블에 대한 지침은 테이블 또는 저장 프로시저를 메모리 내 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% 이하).
메모리 요구 사항의 자세한 계산
메모리 최적화 테이블의 예
다음 메모리 최적화 테이블 스키마를 고려합니다.
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-트리로 구현됩니다. 리프 노드에는 인덱스 값과 메모리의 테이블 행에 대한 포인터가 포함됩니다.
해시 인덱스와 달리 비클러스터형 인덱스에는 고정된 버킷 크기가 없습니다. 인덱스는 데이터와 함께 동적으로 증가하고 축소됩니다.
비클러스터형 인덱스에 필요한 메모리는 다음과 같이 계산할 수 있습니다.
리프가 아닌 노드에 할당된 메모리
일반적인 구성의 경우 리프가 아닌 노드에 할당된 메모리는 인덱스가 사용하는 전체 메모리의 작은 비율입니다. 이 크기는 너무 작아서 무시해도 됩니다.리프 노드에 대한 메모리
리프 노드에는 테이블의 고유 키마다 행이 하나씩 있으며 이 행은 해당 고유 키가 있는 데이터 행을 가리킵니다. 키가 같은 행이 여러 개 있는 경우(즉, 고유하지 않은 비클러스터형 인덱스가 있는 경우) 인덱스 리프 노드에는 다른 행이 서로 연결된 행 중 하나를 가리키는 행이 하나만 있습니다. 따라서 필요한 총 메모리는 다음을 통해 근사값을 계산할 수 있습니다.- memoryForNonClusteredIndex = (pointerSize + sum(keyColumnDataTypeSizes)) * rowsWithUniqueKeys
비클러스터형 인덱스는 다음 쿼리에서 예로 들 수 있듯이 범위 조회에 사용할 때 가장 적합합니다.
SELECT * FROM t_hk
WHERE c2 > 5;
행 버전 관리에 대한 메모리
잠금을 방지하기 위해 메모리 내 OLTP는 행을 업데이트하거나 삭제할 때 낙관적 동시 실행을 사용합니다. 즉, 행이 업데이트될 때 행의 또 다른 버전이 만들어집니다. 또한 삭제는 논리적입니다. 기존 행은 삭제된 것으로 표시되지만 즉시 제거되지는 않습니다. 시스템은 버전을 사용할 수 있는 모든 트랜잭션이 실행을 완료할 때까지 이전 행 버전(삭제된 행 포함)을 사용할 수 있도록 유지합니다.
언제든지 가비지 수집 주기에 따라 메모리가 해제되기를 기다리는 많은 추가 행이 메모리에 있을 수 있기 때문에 이러한 추가 행을 수용하는 데 충분한 메모리가 있어야 합니다.
추가 행 수는 초당 최대 행 업데이트 및 삭제 수를 계산한 다음, 가장 긴 트랜잭션에 걸리는 시간(최소 1초)을 곱하여 예측할 수 있습니다.
그런 다음 해당 값에 행 크기를 곱하여 행 버전 관리에 필요한 바이트 수를 가져옵니다.
rowVersions = durationOfLongestTransactionInSeconds * peakNumberOfRowUpdatesOrDeletesPerSecond
부실 행에 필요한 메모리는 부실 행 수를 메모리 최적화 테이블 행의 크기로 곱하여 예측됩니다(위 표의 메모리 참조).
memoryForRowVersions = rowVersions * rowSize
테이블 변수에 대한 메모리
테이블 변수에 사용되는 메모리는 테이블 변수가 범위를 벗어나는 경우에만 해제됩니다. 테이블 변수에서 업데이트의 일부로 삭제된 행을 포함하여 삭제된 행에는 가비지 수집이 적용되지 않습니다. 테이블 변수가 범위를 종료할 때까지 메모리가 해제되지 않습니다.
많은 트랜잭션에서 사용되는 프로시저 범위와 달리 큰 SQL 일괄 처리에 정의된 테이블 변수는 많은 메모리를 사용할 수 있습니다. 테이블 변수는 가비지 수집되지 않기 때문에 테이블 변수의 삭제된 행은 다량의 메모리를 사용할 수 있으며 읽기 작업이 삭제된 행을 통과하여 검색해야 하므로 성능이 저하될 수 있습니다.
증가를 위한 메모리
위의 계산에서는 현재 상태의 테이블에 필요한 메모리를 예측합니다. 이 메모리 외에도 테이블의 증가를 예측하고 해당 증가를 수용할 수 있는 충분한 메모리를 제공해야 합니다. 예를 들어 10% 증가를 예측하는 경우 테이블에 필요한 총 메모리를 얻으려면 위의 결과에 1.1을 곱해야 합니다.