메모리 최적화 테이블에서 해시 인덱스 사용시 버킷 카운트의 중요성

메모리 최적화 테이블에서 해시 인덱스 사용시 버킷 카운트의 중요성

 

·         Version : SQL Server 2014, 2016

 

SQL Server Memory optimized table(이하 메모리 최적화 테이블)은 기본적으로 메인 메모리에 상주한다. 메모리 최적화 테이블의 데이터는은 메모리에 상주하며 읽고 쓴다. 내구성의 목적으로  보조 복사본이 디스크에서 유지 관리 된다. 메모리 액세스에 최적화된 테이블의 데이터는 데이터베이스 복구중(또는 서버 재시작)에만 디스크에서 읽는다.

 

아래 스크립트는 메모리 최적화 테이블을 생성한다.

CREATE TYPE [Sales].[SalesOrderDetailType_inmem] AS TABLE(   [OrderQty] [smallint] NOT NULL,   [ProductID] [int] NOT NULL,   [SpecialOfferID] [int] NOT NULL,   [LocalID] [int] NOT NULL,     INDEX [IX_ProductID] HASH ([ProductID]) WITH ( BUCKET_COUNT = 8),   INDEX [IX_SpecialOfferID] NONCLUSTERED (LocalID) ) WITH ( MEMORY_OPTIMIZED = ON )

 

스크립트를 보면 BUCKET_COUNT 매개 변수 값을 지정해야하는데 잘못된 BUCKET_COUNT 매개 변수 값(특히 너무 낮은 값)을 사용하면 데이터베이스 복구 시간과 작업 성능에 큰 영향을 줄 수 있다.  특히 중복 인덱스 키는 해시 인덱스를 사용하는 경우 동일한 버킷에 해시되어 해당 버킷의 체인이 증가하기 때문에 성능을 저하시킬 수 있다. 인덱스에 할당된 해시 테이블의 크기는 BUCKET_COUNT에 의해서 할당되며 버킷 수는 내부적으로 2의 제곱수로 반올림된다. 예를들면 버킷수를 300,000개로 지정하면 실제 버킷수는 524,288개가 된다.

 

일반적인 권장 버킷 수는 인덱스 키에 있는 고유한 값의 1~2배 사이어야 한다. 인덱스 키에 평균 10행 이상의 중복 값이 많이 포함된 경우 비클러스터형 인덱스를 사용한다.  특정 인덱스 키에 대해 얼마나 많은 값이 지정될지 항상 예측 할 수 있는것은 아니기 때문에  BUCKET_COUNT 값이 실제 키 값 수의 5배 이내에 해당하는 경우 성능에 문제 없이 사용할 수 있다.

 

메모리 최적화 테이블의 버킷 수 문제를 해결하려면 sys.dm_db_xtp_hash_index_stats를 사용해서 빈 버킷 수 통계와 행 체인 길이를 확인할 수 있다. 아래 스크립트 현재 데이터베이스의 모든 해시 인덱스에 대한 통계를 확인할 수 있으며 큰 테이블이 있는 경우 쿼리 실행이 오래 걸릴 수 있다.

SELECT object_name(hs.object_id) AS 'object name', i.name as 'index name', hs.total_bucket_count, hs.empty_bucket_count, floor((cast(empty_bucket_count as float)/total_bucket_count) * 100) AS 'empty_bucket_percent', hs.avg_chain_length, hs.max_chain_length FROM sys.dm_db_xtp_hash_index_stats AS hs JOIN sys.indexes AS i ON hs.object_id=i.object_id AND hs.index_id=i.index_id

 

실행 결과에서 empty_bucket_percent 는 해시 인덱스에 있는 빈 버킷 수를 나타내며, 10% 미만일 경우 버킷 수도 적을 가능성이 크다. 이상적인 값은 33% 이상이다. avg_chain_length는 해시 커빗의 평균 행 체인 길이를 나타낸다. 10% 이상이면 중복 인덱스 키 값이 많을 수 있으므로 비클러스터형 인덱스가 더 적합하다.

 

실제 작은 테이블의 경우 인덱스 크기가 작므으로 메모리 사용률은 문제가 되지 않으나 큰 테이블의 경우 메모리 사용이 많으므로 주의가 필요하다. 예를 들어 4개의 해시 인덱스가 있는 2억5천만개의 행 테이블이 있고 각각 10억 개의 버킷 수를 가진 경우 해시 테이블의 오버헤드는 4개의 인덱스X 10억 개의 버킷수 X 8byte = 32GB 메모리를 사용한다. 각 인덱스에 대해 2억 5천만개의 버킷수를 선택할 때 해시 테입르의 총 오버헤드는 8GB가 된다.

 

 

[참고자료]

·         Introduction to Memory-Optimized Tables : https://msdn.microsoft.com/en-us/library/dn511014.aspx

·         etermining the Correct Bucket Count for Hash Indexes : https://msdn.microsoft.com/en-us/library/dn494956(v=sql.120).aspx

·         sys.dm_db_xtp_hash_index_stats : https://msdn.microsoft.com/library/dn296679(SQL.130).aspx

·         Altering Memory-Optimized Tables : https://msdn.microsoft.com/en-us/library/dn269114.aspx

 

 

 

2017-03-21 / 강성욱 / http://sqlmvp.kr

 

SQL Server 2016, MS SQL, Memory Optimized table, 메모리 최적화 테이블, Memory consumption, hash index, bucket count, memory table, SQL 2014