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

 

·         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 asfloat)/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개의 해시 인덱스가 있는 25천만개의  테이블이 있고 각각 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


강성욱 / jevida@naver.com

Microsoft SQL Server MVP

Blog : http://sqlmvp.kr

Facebook : http://facebook.com/sqlmvp






profile

강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

Kakao Talk : SQLMVP

Line : jevida


현재 LA에 거주하고 있으며 SQL에 관심있는 분이면 언제든 친추 환영합니다.