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

 

·         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 [IT재직자] 개강임박!! 올해 마지막 무료 및 국비지원 교육 (JAVA, 리눅스 기초/고급 , CCNA, CCIE 등) 코난(김대우) 2022.12.02 82
Notice 2022년 11월 SQLER의 강좌 업데이트 리스트 코난(김대우) 2022.12.01 23
Notice 2022 공개SW 페스티벌 - Open Up 코난(김대우) 2022.12.01 15
» 메모리 최적화 테이블에서 해시 인덱스 사용시 버킷 카운트의 중요성 jevida(강성욱) 2017.05.31 3021
2082 인덱스 리빌드시 병렬로 실행계획이 생성되지 않는 이유 jevida(강성욱) 2017.03.13 4990
2081 SQL Server 833오류 (15 Sec Slow IO Detected) jevida(강성욱) 2017.03.13 4427
2080 SQL Server nonpreemptive 모드에서Long Sync IO 오류 jevida(강성욱) 2017.03.13 3146
2079 SQL Server DTC Transaction의 SPID = -2 반환 jevida(강성욱) 2017.03.13 2417
2078 SQL Server Read/Writer 동기화 작동 원리 jevida(강성욱) 2017.03.13 2775
2077 SQL Server가 논리 및 물리 프로세서를 결정하는 방법 jevida(강성욱) 2017.03.13 2911
2076 데이터베이스에서 사용자 삭제 오류 jevida(강성욱) 2017.03.13 2817
2075 SQL Server ODBC Driver 및 Command package Tool 설치 jevida(강성욱) 2017.01.11 4780
2074 SQL Server Install on Linux (Ubuntu – 16.0.4) jevida(강성욱) 2017.01.11 5032
2073 SQL Azure blob storage 백업을 위한 프록시 세팅 jevida(강성욱) 2017.01.11 4237
2072 SQL on Azure 환경에서 SQL 서버 연결 불능 문제(VNET 설정 문제) jevida(강성욱) 2017.01.11 3317
2071 SQL Server 2016 자동 통계 업데이트 임계값 변경 jevida(강성욱) 2017.01.11 4684
2070 SQL Server 2016 Multiple Log Writer Workers jevida(강성욱) 2017.01.11 3843
2069 SQL Server 2016 Larger Data File Writes jevida(강성욱) 2017.01.11 3214
2068 SQL Server 2016 향상된 업데이트 스케줄링 알고리즘 jevida(강성욱) 2017.01.11 3677
2067 SQL Server 2016 Automatic Soft NUMA jevida(강성욱) 2017.01.11 3136
2066 SQL Server 2016 Tempdb 환경 설정 jevida(강성욱) 2017.01.11 4432
2065 SQL Server 2016 향상된 즉시 파일 초기화 jevida(강성욱) 2017.01.11 2788
2064 SQL Server 2016 LDF 생성 또는 증가 시 변경된 스탬프 패턴 jevida(강성욱) 2017.01.11 2659





XE Login

테스트 팝업
Close