다음을 통해 공유


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

적용 대상: 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분 후에 완료됩니다. 다음 코드 블록의 단계는 다음과 같습니다.

  1. 몇 가지 해시 인덱스가 있는 메모리 최적화 테이블을 만듭니다.
  2. 수천 개의 행으로 테이블을 채웁니다.
    a. 모듈로 연산자는 StatusCode 열에서 중복 값의 비율을 구성하는 데 사용됩니다.
    b. 이 루프는 약 1분 안에 262,144개의 행을 삽입합니다.
  3. 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배 많이 지정하는 것이 좋습니다. 개수가 높을수록 다음을 의미합니다.
    • 특정 값을 검색할 때 검색 속도가 빨라집니다.
    • 메모리 사용률이 증가합니다.
    • 해시 인덱스의 전체 검색에 필요한 시간이 증가합니다.

추가 자료

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