메모리 최적화 테이블에 대한 해시 인덱스 문제 해결

아티클
08/21/2023

적용 대상:SQL Server Azure SQL DatabaseAzure 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 를 실행하도록 요청하는 메시지를 PRINT합니다.

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 고유 값을 삽입합니다.
에 대한 8개의 고유 값만 나타내는 수십만 개의 행을 StatusCode삽입합니다. 따라서 인덱스 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배 많이 지정하는 것이 좋습니다. 개수가 높을수록 다음을 의미합니다.
- 특정 값을 검색할 때 검색 속도가 빨라집니다.
- 메모리 사용률이 증가합니다.
- 해시 인덱스의 전체 검색에 필요한 시간이 증가합니다.

추가 자료

메모리 최적화 테이블에 대한 해시 인덱스
메모리 최적화 테이블의 비클러스터형 인덱스