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

 

·         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


No. Subject Author Date Views
Notice SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 34935
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 17245
2094 SQL Linux에서 기본 백업 디렉토리 변경 jevida(강성욱) 2017.09.13 3937
2093 Linux에서 DISK I/O 사용량 확인 jevida(강성욱) 2017.09.13 3918
2092 Linux에서 CPU 사용량 확인 jevida(강성욱) 2017.09.13 2305
2091 SQL Linux에서 SQL Server 시작, 중지, 활성, 비활성 jevida(강성욱) 2017.09.13 1537
2090 SQL Server DBA 체크리스트 jevida(강성욱) 2017.05.31 8271
2089 SQL Server 데이터베이스 백업이 성공적인지 확인하는 방법 jevida(강성욱) 2017.05.31 3808
2088 트리거가 적용된 메모리 최적화 테이블에서 alter table 실패 jevida(강성욱) 2017.05.31 3387
2087 메모리 최적화 테이블 변수 및 예상 행수 jevida(강성욱) 2017.05.31 3030
2086 백업 파일 복원과 3241 오류 (Microsoft® SQL Server® Backup to Microsoft Azure®Tool 사용) jevida(강성욱) 2017.05.31 3261
2085 In-Memory OLTP를 사용할 때 체크포인트 파일이 너무 많아 지는 이유 jevida(강성욱) 2017.05.31 2738
2084 메모리 최적화 테이블변수와 701 오류 (loop 사용으로 인한 메모리 부족 오류) jevida(강성욱) 2017.05.31 3453
» 메모리 최적화 테이블에서 해시 인덱스 사용시 버킷 카운트의 중요성 jevida(강성욱) 2017.05.31 3103
2082 인덱스 리빌드시 병렬로 실행계획이 생성되지 않는 이유 jevida(강성욱) 2017.03.13 5067
2081 SQL Server 833오류 (15 Sec Slow IO Detected) jevida(강성욱) 2017.03.13 4517
2080 SQL Server nonpreemptive 모드에서Long Sync IO 오류 jevida(강성욱) 2017.03.13 3300
2079 SQL Server DTC Transaction의 SPID = -2 반환 jevida(강성욱) 2017.03.13 2460
2078 SQL Server Read/Writer 동기화 작동 원리 jevida(강성욱) 2017.03.13 2819
2077 SQL Server가 논리 및 물리 프로세서를 결정하는 방법 jevida(강성욱) 2017.03.13 2955
2076 데이터베이스에서 사용자 삭제 오류 jevida(강성욱) 2017.03.13 2928
2075 SQL Server ODBC Driver 및 Command package Tool 설치 jevida(강성욱) 2017.01.11 4857





XE Login