메모리 최적화 테이블의 해시 인덱스 문제 해결
적용 대상: 
SQL Server Azure SQL 데이터베이스 Azure SQL Managed Instance
전제 조건
이 문서를 이해하는 데 중요한 컨텍스트 정보는 다음 항목에서 확인할 수 있습니다.
실제 숫자
메모리 최적화 테이블에 대한 해시 인덱스를 생성할 때 버킷 수는 생성 시에 지정해야 합니다. 대부분의 경우 버킷 수는 인덱스 키에 있는 고유한 값 수의 1~2배 사이여야 합니다.
그러나 BUCKET_COUNT가 기본 범위에서 약간 위에 있거나 약간 아래에 있더라도 해시 인덱스의 성능은 지속할 수 있거나 허용 가능합니다.
최소한 해시 인덱스에 메모리 최적화 테이블이 증가할 것으로 예상하는 행 수와 거의 동일한 BUCKET_COUNT를 제공하는 것이 좋습니다.
증가하는 테이블에 2,000,000개의 행이 있지만 예측은 10배인 20,000,000개 행으로 증가합니다. 테이블의 행 수의 10배인 버킷 수로 시작합니다. 이렇게 하면 증가하는 행 수를 위한 공간이 확보됩니다.
- 이상적으로는 행 수가 초기 버킷 수에 도달할 때 버킷 수를 늘리는 것이 좋습니다.
- 행 수가 버킷 수보다 5배 더 커지더라도 대부분의 상황에서는 여전히 성능이 양호합니다.
해시 인덱스에 10,000,000개의 고유 키 값을 가지고 있다고 가정합니다.
- 버킷 수 2,000,000개는 받아들일 수 있는 최소한의 수준입니다. 이 경우에 성능 저하 정도는 허용할 수 있습니다.
인덱스에 중복된 값이 너무 많나요?
해시 인덱싱된 값의 중복 비율이 높은 경우 해시 버킷은 더 긴 체인을 겪습니다.
이전 T-SQL 구문 코드 블록과 동일한 SupportEvent 테이블이 있다고 가정합니다. 다음 T-SQL 코드는 고유한 값에 대한 모든 값의 비율을 찾아서 표시하는 방법을 보여줍니다.
-- Calculate ratio of: Rows / Unique_Values.
DECLARE @allValues float(8) = 0.0, @uniqueVals float(8) = 0.0;
SELECT @allValues = Count(*) FROM SupportEvent;
SELECT @uniqueVals = Count(*) FROM
(SELECT DISTINCT SupportEngineerName
FROM SupportEvent) as d;
-- If (All / Unique) >= 10.0, use a nonclustered index, not a hash.
SELECT Cast((@allValues / @uniqueVals) as float) as [All_divby_Unique];
go
- 비율이 10.0 이상인 경우 해시가 잘못된 유형의 인덱스일 수 있습니다. 대신 비클러스터형 인덱스를 사용하는 것이 좋습니다.
해시 인덱스 버킷 수 문제 해결
이 섹션에서는 해시 인덱스의 버킷 수 문제를 해결하는 방법을 설명합니다.
체인 및 빈 버킷 통계 모니터링
다음 T-SQL SELECT를 실행하여 해시 인덱스의 통계 상태를 모니터링할 수 있습니다. SELECT는 sys.dm_db_xtp_hash_index_stats로 명명된 데이터 관리 뷰(DMV)를 사용합니다.
SELECT
QUOTENAME(SCHEMA_NAME(t.schema_id)) + N'.' + QUOTENAME(OBJECT_NAME(h.object_id)) as [table],
i.name as [index],
h.total_bucket_count,
h.empty_bucket_count,
FLOOR((
CAST(h.empty_bucket_count as float) /
h.total_bucket_count) * 100)
as [empty_bucket_percent],
h.avg_chain_length,
h.max_chain_length
FROM
sys.dm_db_xtp_hash_index_stats as h
JOIN sys.indexes as i
ON h.object_id = i.object_id
AND h.index_id = i.index_id
JOIN sys.memory_optimized_tables_internal_attributes ia ON h.xtp_object_id=ia.xtp_object_id
JOIN sys.tables t on h.object_id=t.object_id
WHERE ia.type=1
ORDER BY [table], [index];
SELECT 결과를 다음 통계 지침과 비교합니다.
- 빈 버킷:
- 33%가 정상 목표 값이지만 좀 더 높아도 괜찮습니다(90%).
- 버킷 수가 고유 키 값의 수와 같으면 버킷의 약 33%가 비어 있습니다.
- 값이 10% 이하면 너무 낮습니다.
- 버킷 내 체인:
- 중복 인덱스 키 값이 없는 경우 평균 체인 길이는 1인 것이 좋습니다. 최대 10의 체인 길이는 일반적으로 허용됩니다.
- 평균 체인 길이가 10보다 크고 빈 버킷 비율이 10%보다 큰 경우 데이터에 너무 많은 중복 항목이 있으므로 해시 인덱스는 가장 적합한 형식이 아닐 수 있습니다.
체인 및 빈 버킷 데모
다음 T-SQL 코드 블록을 사용하여 SELECT * FROM sys.dm_db_xtp_hash_index_stats;을 간편하게 테스트할 수 있습니다. 코드 블록은 1분 후에 완료됩니다. 다음 코드 블록의 단계는 다음과 같습니다.
- 몇 가지 해시 인덱스가 있는 메모리 최적화 테이블을 만듭니다.
- 수천 개의 행으로 테이블을 채웁니다.
a. 모듈로 연산자는 StatusCode 열에서 중복 값의 비율을 구성하는 데 사용됩니다.
b. 이 루프는 약 1분 안에 262,144개의 행을 삽입합니다. - sys.dm_db_xtp_hash_index_stats에서 이전 SELECT를 실행하도록 요청하는 메시지를 출력합니다.
DROP TABLE IF EXISTS SalesOrder_Mem;
go
CREATE TABLE SalesOrder_Mem
(
SalesOrderId uniqueidentifier NOT NULL DEFAULT newid(),
OrderSequence int NOT NULL,
OrderDate datetime2(3) NOT NULL,
StatusCode tinyint NOT NULL,
PRIMARY KEY NONCLUSTERED
HASH (SalesOrderId) WITH (BUCKET_COUNT = 262144),
INDEX ix_OrderSequence
HASH (OrderSequence) WITH (BUCKET_COUNT = 20000),
INDEX ix_StatusCode
HASH (StatusCode) WITH (BUCKET_COUNT = 8),
INDEX ix_OrderDate NONCLUSTERED (OrderDate DESC)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
go
--------------------
SET NOCOUNT ON;
-- Same as PK bucket_count. 68 seconds to complete.
DECLARE @i int = 262144;
BEGIN TRANSACTION;
WHILE @i > 0
BEGIN
INSERT SalesOrder_Mem
(OrderSequence, OrderDate, StatusCode)
Values
(@i, GetUtcDate(), @i % 8); -- Modulo technique.
SET @i -= 1;
END
COMMIT TRANSACTION;
PRINT 'Next, you should query: sys.dm_db_xtp_hash_index_stats .';
go
앞의 INSERT 루프는 다음을 수행합니다.
- 기본 키 인덱스 및 ix_OrderSequence의 고유 값을 삽입합니다.
StatusCode에 대해 8개의 고유 값만 표시하는 수십만 개의 행을 삽입합니다. 따라서 인덱스 ix_StatusCode에는 값 중복 비율이 높습니다.
버킷 수가 최적이 아닌 경우 문제를 해결하려면 sys.dm_db_xtp_hash_index_stats에서 SELECT의 다음 출력을 검토합니다. 이러한 결과를 위해 섹션 D.1에서 복사한 SELECT에 WHERE Object_Name(h.object_id) = 'SalesOrder_Mem'을 추가했습니다.
SELECT 결과는 향상된 표시를 위해 보다 좁은 두 개의 결과 테이블로 분할되어 코드 이후에 표시됩니다.
- 버킷 수에 대한 결과는 다음과 같습니다.
| IndexName | total_bucket_count | empty_bucket_count | EmptyBucketPercent |
|---|---|---|---|
| IX_OrderSequence | 32768 | 13 | 0 |
| ix_StatusCode | 8 | 4 | 50 |
| PK_SalesOrd_B14003... | 262144 | 96525 | 36 |
- 다음은 체인 길이에 대한 결과입니다.
| IndexName | avg_chain_length | max_chain_length |
|---|---|---|
| IX_OrderSequence | 8 | 26 |
| ix_StatusCode | 65536 | 65536 |
| PK_SalesOrd_B14003... | 1 | 8 |
세 개의 해시 인덱스에 대한 이전 결과 테이블을 해석해 보겠습니다.
ix_StatusCode:
- 버킷의 50%가 비어 있으며 양호합니다.
- 그러나 평균 체인 길이는 65536으로 매우 높습니다.
- 이는 중복 값의 높은 비율을 나타냅니다.
- 따라서 이 경우 해시 인덱스를 사용하는 것은 적합하지 않습니다. 대신 비클러스터형 인덱스를 사용해야 합니다.
ix_OrderSequence:
- 버킷의 0%가 비어 있으며 너무 낮습니다.
- 이 인덱스 내의 모든 값이 고유하더라도 평균 체인 길이는 8입니다.
- 따라서 평균 체인 길이를 2 또는 3에 가깝게 줄이려면 버킷 수를 늘려야 합니다.
- 인덱스 키에는 262144개의 고유 값이 있으므로 버킷 수는 262144 이상이어야 합니다.
- 향후에 더 성장할 것으로 예상된다면 버킷 수를 더 높여야 합니다.
기본 키 인덱스(PK_SalesOrd_...):
- 버킷의 36%가 비어 있으며 양호합니다.
- 평균 체인 길이는 1이며 이 또한 양호합니다. 변경이 필요하지 않습니다.
절충의 균형 유지
OLTP는 작업 시 개별 행에 중점을 둡니다. 일반적으로 전체 테이블 검색은 OLTP 작업에서 성능이 중요한 경로에 두지 않습니다. 따라서 균형을 유지해야 하는 절충은 메모리 사용량과 같음 테스트 및 삽입 작업의 성능 간에 이루어집니다.
메모리 사용률이 더 중요한 경우:
- 고유 인덱스 키 값 수에 가까운 버킷 수를 선택합니다.
- 버킷 수는 서버가 다시 시작된 후 데이터베이스를 복구하는 데 걸리는 시간뿐만 아니라 대부분의 DML 작업에 영향을 주기 때문에 고유 인덱스 키 값의 수보다 크게 낮아서는 안 됩니다.
같음 테스트의 성능이 더 중요한 경우:
- 버킷 수를 고유한 인덱스 값 수보다 2-3배 많이 지정하는 것이 좋습니다. 개수가 높을수록 다음을 의미합니다.
- 특정 값을 검색할 때 검색 속도가 빨라집니다.
- 메모리 사용률이 증가합니다.
- 해시 인덱스의 전체 검색에 필요한 시간이 증가합니다.