해시 인덱스에 대한 올바른 버킷 수 확인

메모리 최적화 테이블을 만들 때 매개 변수 값을 지정 BUCKET_COUNT 해야 합니다. 이 항목에서는 매개 변수에 대한 적절한 값을 결정하기 위한 BUCKET_COUNT 권장 사항을 제공합니다. 올바른 버킷 수를 확인할 수 없는 경우 비클러스터형 인덱스 대신 사용합니다. 잘못된 BUCKET_COUNT 값, 특히 너무 낮은 값은 데이터베이스의 복구 시간뿐만 아니라 워크로드 성능에 큰 영향을 미칠 수 있습니다. 버킷 수를 더 높게 평가하는 것이 낫습니다.

키가 동일한 버킷에 해시되어 그 버킷의 체인이 증가하므로 중복 인덱스 키는 해시 인덱스의 성능을 저하시킬 수 있습니다.

비클러스터형 해시 인덱스에 대한 자세한 내용은 Memory-Optimized 테이블에서 인덱스를 사용하기 위한 해시 인덱스 및 지침을 참조하세요.

메모리 최적화 테이블의 각 해시 인덱스마다 해시 테이블 하나가 할당됩니다. 인덱스용으로 할당된 해시 테이블의 크기는 CREATE TABLE(Transact-SQL) 또는 CREATE TYPE(Transact-SQL)의 매개 변수에 의해 BUCKET_COUNT 지정됩니다. 버킷 수는 내부적으로 2의 제곱수로 반올림됩니다. 예를 들어 버킷 수를 300,000으로 지정하면 실제 버킷 수는 524,288개입니다.

버킷 수에 대한 문서 및 비디오에 대한 링크는 해시 인덱스(In-Memory OLTP)에 적합한 버킷 수를 확인하는 방법을 참조하세요.

권장 사항

대부분의 경우 버킷 수는 인덱스 키의 고유 값 수의 1~2배 사이여야 합니다. 인덱스 키에 중복 값이 많은 경우 평균적으로 각 인덱스 키 값에 대해 10개 이상의 행이 있는 경우 비클러스터형 인덱스를 대신 사용합니다.

특정 인덱스 키가 가질 수 있거나 가질 값 수를 항상 예측할 수 있는 것은 아닙니다. 값이 실제 키 값 수의 5배 이내인 경우 BUCKET_COUNT 성능이 허용되어야 합니다.

기존 데이터의 고유 인덱스 키 수를 확인하려면 다음 예제와 유사한 쿼리를 사용합니다.

기본 키 및 고유 인덱스

기본 키 인덱스는 고유하므로 키의 고유 값 수는 테이블의 행 수에 해당합니다. AdventureWorks 데이터베이스의 Sales.SalesOrderDetail 테이블의 기본 키(SalesOrderID, SalesOrderDetailID)에 대한 예제 기본 키의 경우 다음 쿼리를 실행하여 테이블의 행 수에 해당하는 고유 기본 키 값의 수를 계산합니다.

SELECT COUNT(*) AS [row count]   
FROM Sales.SalesOrderDetail

이 쿼리는 121,317개의 행 수를 보여줍니다. 행 수가 크게 변경되지 않는 경우 버킷 수를 240,000으로 사용합니다. 테이블 내 판매 주문 수가 4배로 증가할 것으로 예상되면 버킷 수를 480,000개로 설정합니다.

고유하지 않은 인덱스

다른 인덱스(예: SpecialOfferID, ProductID)에 대한 다중 열 인덱스의 경우 다음 쿼리를 실행하여 고유 인덱스 키 값의 수를 확인합니다.

SELECT COUNT(*) AS [SpecialOfferID_ProductID index key count]  
FROM   
   (SELECT DISTINCT SpecialOfferID, ProductID   
    FROM Sales.SalesOrderDetail) t

이 쿼리는 비클러스터형 해시 인덱스 대신 비클러스터형 인덱스가 사용되어야 함을 나타내는 484의 인덱스 키 개수(SpecialOfferID, ProductID)를 반환합니다.

중복 횟수 확인

인덱스 키 값의 평균 중복 값 수를 확인하려면 총 행 수를 고유 인덱스 키 수로 나눕니다.

인덱스 예제(SpecialOfferID, ProductID)의 경우 121317/484 = 251로 이어집니다. 즉, 인덱스 키 값의 평균은 251이며, 따라서 비클러스터형 인덱스여야 합니다.

버킷 수 문제 해결

메모리 최적화 테이블의 버킷 수 문제를 해결하려면 sys.dm_db_xtp_hash_index_stats(Transact-SQL) 를 사용하여 빈 버킷 및 행 체인의 길이에 대한 통계를 가져옵니다. 다음 쿼리를 사용하여 현재 데이터베이스의 모든 해시 인덱스에 대한 통계를 가져올 수 있습니다. 데이터베이스에 큰 테이블이 있는 경우 쿼리를 실행하는 데 몇 분 정도 걸릴 수 있습니다.

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 해시 인덱스의 빈 버킷 수를 나타냅니다.

empty_bucket_percent 10% 미만이면 버킷 수가 너무 낮을 수 있습니다. 이상적으로 empty_bucket_percent는 33% 이상이어야 합니다. 버킷 수가 인덱스 키 값의 수와 일치하는 경우 해시 분포로 인해 버킷의 약 1/3이 비어 있습니다.

평균_체인_길이
avg_chain_length 해시 버킷에 있는 행 체인의 평균 길이를 나타냅니다.

avg_chain_length 10보다 크고 empty_bucket_percent 10%보다 큰 경우 중복 인덱스 키 값이 많을 수 있으며 비클러스터형 인덱스가 더 적합할 수 있습니다. 평균 체인 길이 1이 이상적입니다.

체인 길이에 영향을 주는 두 가지 요소가 있습니다.

중복; 모든 중복 행은 해시 인덱스의 동일한 체인에 속합니다.
여러 키 값이 동일한 버킷에 매핑됩니다. 버킷 수가 낮을수록 여러 값이 매핑되는 버킷이 늘어나게 됩니다.

예를 들어 다음 테이블 및 스크립트를 사용하여 표에 샘플 행을 삽입합니다.

CREATE TABLE [Sales].[SalesOrderHeader_test]  
(  
   [SalesOrderID] [uniqueidentifier] NOT NULL DEFAULT (newid()),  
   [OrderSequence] int NOT NULL,  
   [OrderDate] [datetime2](7) NOT NULL,  
   [Status] [tinyint] NOT NULL,  
  
PRIMARY KEY NONCLUSTERED HASH ([SalesOrderID]) WITH ( BUCKET_COUNT = 262144 ),  
INDEX IX_OrderSequence HASH (OrderSequence) WITH ( BUCKET_COUNT = 20000),  
INDEX IX_Status HASH ([Status]) WITH ( BUCKET_COUNT = 8),  
INDEX IX_OrderDate NONCLUSTERED ([OrderDate] ASC),  
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )  
GO  
  
DECLARE @i int = 0  
BEGIN TRAN  
WHILE @i < 262144  
BEGIN  
   INSERT Sales.SalesOrderHeader_test (OrderSequence, OrderDate, [Status]) VALUES (@i, sysdatetime(), @i % 8)  
   SET @i += 1  
END  
COMMIT  
GO

이 스크립트는 테이블에 262,144개의 행을 삽입합니다. 기본 키 인덱스와 IX_OrderSequence에 고유 값을 삽입합니다. 인덱스 IX_Status 많은 중복 값을 삽입합니다. 스크립트는 8개의 고유 값만 생성합니다.

BUCKET_COUNT 문제 해결 쿼리의 출력은 다음과 같습니다.

인덱스 이름	전체 버킷 개수	빈 버킷 수	빈_버킷_퍼센트	평균_체인_길이	최대_체인_길이
IX_Status	8 (여덟)	4	50	65536	65536
IX_OrderSequence	32768	13	0	8 (여덟)	26
PK_SalesOrd_B14003C3F8FB3364	262144	96319	36	1	8 (여덟)

이 테이블의 세 가지 해시 인덱스를 고려합니다.

IX_Status: 버킷의 50%가 비어 있어 좋습니다. 그러나 평균 체인 길이는 매우 높습니다(65,536). 이는 많은 수의 중복 값을 나타냅니다. 따라서 비클러스터형 해시 인덱스 사용은 이 경우에 적절하지 않습니다. 대신 비클러스터형 인덱스를 사용해야 합니다.
IX_OrderSequence: 버킷의 0%가 비어 있는데, 이는 비어 있는 비율로는 너무 낮습니다. 또한 평균 체인 길이는 8입니다. 이 인덱스 값이 고유하므로 평균 8개의 값이 각 버킷에 매핑됩니다. 버킷 수를 늘려야 합니다. 인덱스 키에는 262,144개의 고유 값이 있으므로 버킷 수는 262,144개 이상이어야 합니다. 향후 성장이 예상되는 경우 그 수는 더 높아야 합니다.
기본 키 인덱스(PK__SalesOrder...): 버킷의 36%가 비어 있어 양호합니다. 또한 평균 체인 길이는 1이며, 이는 또한 좋다. 변경이 필요하지 않습니다.

메모리 최적화 해시 인덱스의 문제 해결에 대한 자세한 내용은 Memory-Optimized 해시 인덱스의 일반적인 성능 문제 해결을 참조하세요.

추가 최적화에 대한 자세한 고려 사항

이 섹션에서는 버킷 수를 최적화하기 위한 추가 고려 사항을 간략하게 설명합니다.

해시 인덱스에 대한 최상의 성능을 얻으려면 해시 테이블에 할당된 메모리 양과 인덱스 키의 고유 값 수의 균형을 조정합니다. 또한 지점 조회의 성능과 테이블 검색 간의 균형이 있습니다.

버킷 수 값이 높을수록 인덱스에 더 많은 빈 버킷이 있습니다. 이는 각 버킷이 테이블 검색의 일부로 검사되므로 메모리 사용량(버킷당 8바이트)과 테이블 검색 성능에 영향을 줍니다.
버킷 수가 낮을수록 단일 버킷에 더 많은 값이 할당됩니다. 이렇게 하면 SQL Server가 검색 조건자에서 지정한 값을 찾기 위해 단일 버킷의 여러 값을 트래버스해야 할 수 있으므로 지점 조회 및 삽입의 성능이 저하됩니다.

버킷 수가 고유 인덱스 키 수보다 훨씬 낮은 경우 많은 값이 각 버킷에 매핑됩니다. 이렇게 하면 대부분의 DML 작업, 특히 지점 조회(개별 인덱스 키 조회) 및 삽입 작업의 성능이 저하됩니다. 예를 들어 SELECT 쿼리의 성능이 저하되고 WHERE 절의 인덱스 키 열과 일치하는 같음 조건자가 있는 UPDATE 및 DELETE 작업이 발생할 수 있습니다. 낮은 버킷 수는 데이터베이스 시작 시 인덱스가 다시 생성되므로 데이터베이스의 복구 시간에도 영향을 줍니다.

중복 인덱스 키 값

중복 값은 해시 충돌의 성능 영향을 증가시킬 수 있습니다. 각 인덱스 키의 중복 수가 적은 경우에는 일반적으로 문제가 되지 않습니다. 그러나 고유 인덱스 키 수와 테이블의 행 수 간의 불일치가 매우 커지면 문제가 될 수 있습니다.

인덱스 키가 동일한 모든 행은 동일한 중복 체인으로 이동합니다. 해시 충돌로 인해 여러 인덱스 키가 동일한 버킷에 있는 경우 인덱스 스캐너는 항상 첫 번째 값에 대한 전체 중복 체인을 검색해야 두 번째 값에 해당하는 첫 번째 행을 찾을 수 있습니다. 키가 중복되면 가비지 수집기가 행을 찾기가 더 어려워집니다. 예를 들어 키에 대한 중복 항목이 1,000개이고 행 중 하나가 삭제된 경우 가비지 수집기는 1,000개의 중복 체인을 검색하여 인덱스에서 행의 연결을 해제해야 합니다. 가비지 수집기가 모든 인덱스에서 연결을 해제해야 하므로 삭제를 발견한 쿼리에서 행을 찾는 데 더 효율적인 인덱스(기본 키 인덱스)를 사용한 경우에도 마찬가지입니다.

해시 인덱스의 경우 중복 인덱스 키 값으로 인한 작업을 줄이는 두 가지 방법이 있습니다.

대신 비클러스터형 인덱스 사용 애플리케이션을 변경하지 않고도 인덱스 키에 열을 추가하여 중복을 줄일 수 있습니다.
인덱스 버킷 수를 매우 높게 지정합니다. 예를 들어 고유 인덱스 키 수의 20~100배입니다. 이렇게 하면 해시 충돌이 줄어듭니다.

작은 테이블

더 작은 테이블의 경우 인덱스의 크기가 데이터베이스의 전체 크기에 비해 작기 때문에 일반적으로 메모리 사용률은 문제가 되지 않습니다.

이제 원하는 성능 종류에 따라 선택해야 합니다.

인덱스의 성능에 중요한 작업이 주로 지점 조회 및/또는 삽입 작업인 경우 버킷 수가 높을수록 해시 충돌 가능성을 줄이는 것이 적절합니다. 행 수의 3배 이상이 가장 좋은 옵션입니다.
전체 인덱스 검색이 성능에 중요한 주요 작업인 경우 실제 인덱스 키 값 수에 가까운 버킷 수를 사용합니다.

큰 테이블

큰 테이블의 경우 메모리 사용률이 문제가 될 수 있습니다. 예를 들어 각각 버킷 수가 10억 개인 해시 인덱스가 4개인 2억 5천만 개의 행 테이블이 있는 경우 해시 테이블의 오버헤드는 4개의 인덱스 * 10억 버킷 * 8바이트 = 32GB의 메모리 사용률입니다. 각 인덱스에 대해 2억 5천만 개의 버킷 수를 선택할 때 해시 테이블의 총 오버헤드는 8기가바이트입니다. 이는 각 인덱스가 각 개별 행에 추가하는 8바이트 메모리 사용량(이 시나리오에서는 8GB(인덱스 4개 * 8바이트 * 2억 5천만 행)에 추가됩니다.

전체 테이블 검사는 일반적으로 OLTP 워크로드에 대한 성능에 중요한 경로에 있지 않습니다. 따라서 메모리 사용률과 지점 조회 및 삽입 작업의 성능 중에서 선택할 수 있습니다.

메모리 사용률이 중요한 경우 인덱스 키 값 수에 가까운 버킷 수를 선택합니다. 버킷 수는 인덱스 키 값 수보다 크게 낮아서는 안 됩니다. 이는 대부분의 DML 작업과 서버를 다시 시작한 후 데이터베이스를 복구하는 데 걸리는 시간에 영향을 줍니다.
포인트 조회에 대한 성능을 최적화할 때 고유 인덱스 값의 2배 또는 3배의 버킷 수가 더 높아질 수 있습니다. 버킷 수가 많을수록 메모리 사용률이 증가하고 전체 인덱스 검사에 필요한 시간이 증가합니다.

또한 참조하십시오

Memory-Optimized 테이블의 인덱스

Last updated on 2017-06-13