메모리 최적화 테이블의 해시 인덱스 문제 해결Troubleshooting Hash Indexes for Memory-Optimized Tables

이 항목은 다음에 적용됩니다. 예SQL Server 예Azure SQL Database아니요Azure SQL Data Warehouse 아니요병렬 데이터 웨어하우스THIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

사전 요구 사항Prerequisite

이 문서를 이해하는 데 중요한 컨텍스트 정보는 다음 항목에서 확인할 수 있습니다.Important context information for understanding this article is available at:

실제 수Practical numbers

메모리 최적화 테이블의 해시 인덱스를 만들 때 생성 시 버킷 수를 지정해야 합니다.When creating a hash index for a memory-optimized table, the number of buckets needs to be specified at create time. 대부분의 경우 버킷 수는 인덱스 키에 있는 고유한 값 수의 1~2배 사이여야 합니다.In most cases the bucket count would ideally be between 1 and 2 times the number of distinct values in the index key.

그러나 BUCKET_COUNT가 기본 범위에서 약간 위에 있거나 약간 아래에 있더라도 해시 인덱스의 성능은 지속할 수 있거나 허용 가능합니다.However, even if the BUCKET_COUNT is moderately below or above the preferred range, the performance of your hash index is likely to be tolerable or acceptable. 최소한 메모리 최적화 테이블에서 증가할 것으로 예측되는 행 수와 비교적 동일한 BUCKET_COUNT를 해시 인덱스에 지정하는 것이 좋습니다.At minimum, consider giving your hash index a BUCKET_COUNT roughly equal to the number of rows you predict your memory-optimized table will grow to have.
테이블에서 2백만 개의 행이 있고 계속 증가하고 있다고 가정하지만 테이블이 10배인 2천만 개로 증가할 것으로 예측합니다.Suppose your growing table has 2,000,000 rows, but the prediction is it will grow 10 times to 20,000,000 rows. 이 경우 테이블의 행 수보다 10배 많은 버킷 수로 시작됩니다.Start with a bucket count that is 10 times the number of rows in the table. 이렇게 하면 증가하는 행 수를 위한 공간이 확보됩니다.This gives you room for an increased quantity of rows.

  • 이상적으로는 행 수가 초기 버킷 수에 도달할 때 버킷 수를 늘리는 것이 좋습니다.Ideally you would increase the bucket count when the quantity of rows reaches the initial bucket count.
  • 행 수가 버킷 수 보다 5배로 더 많아지더라도 대부분의 경우 성능은 여전히 적절히 유지됩니다.Even if the quantity of rows grows to 5 times larger than the bucket count, the performance is still good in most situations.

해시 인덱스에 고유 키 값이 천만 개 있다고 가정하면Suppose a hash index has 10,000,000 distinct key values.

  • 2백만 개의 버킷 수가 허용할 수 있는 최저 수치입니다.A bucket count of 2,000,000 would be about as low as you could accept. 성능 저하도 견딜 수 있는 정도입니다.The degree of performance degradation could be tolerable.

인덱스에 중복 값이 너무 많은 경우Too many duplicate values in the index?

해시 인덱스 값에 중복 비율이 높은 경우에는 해시 버킷은 늘어난 체인을 경험합니다.If the hash indexed values have a high rate of duplicates, the hash buckets suffer longer chains.

이전 T-SQL 구문 코드 블록에서 동일한 SupportEvent 테이블이 있다고 가정합니다.Assume you have the same SupportEvent table from the earlier T-SQL syntax code block. 다음 T-SQL 코드에서는 모든 값 대 고유 값의 비율을 찾아 표시하는 방법을 보여 줍니다.The following T-SQL code demonstrates how you can find and display the ratio of all values to unique values:

-- 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 이상이면 해시의 인덱스 유형이 좋지 않다는 것을 의미합니다.A ratio of 10.0 or higher means a hash would be a poor type of index. 대신 비클러스터형 인덱스를 사용하는 것이 좋습니다.Consider using a nonclustered index instead,

해시 인덱스 버킷 수 문제 해결Troubleshooting hash index bucket count

이 섹션에서는 해시 인덱스 버킷 수에 대한 문제를 해결하는 방법을 설명합니다.This section discusses how to troubleshoot the bucket count for your hash index.

체인 및 빈 버킷 통계 모니터링Monitor statistics for chains and empty buckets

다음 T-SQL SELECT를 실행하여 해시 인덱스의 통계 상태를 모니터링할 수 있습니다.You can monitor the statistical health of your hash indexes by running the following T-SQL SELECT. SELECT는 sys.dm_db_xtp_hash_index_stats라는 DMV(Data Management View)를 사용합니다.The SELECT uses the data management view (DMV) named sys.dm_db_xtp_hash_index_stats.

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 결과를 비교합니다.Compare the SELECT results to the following statistical guidelines:

  • 빈 버킷:Empty buckets:
    • 33%가 정상 목표 값이지만 좀 더 높아도 괜찮습니다(90%).33% is a good target value, but a larger percentage (even 90%) is usually fine.
    • 버킷 수가 고유 키 값 수와 일치하면 대략 버킷의 33% 정도 비어 있는 것입니다.When the bucket count equals the number of distinct key values, approximately 33% of the buckets are empty.
    • 값이 10% 이하면 너무 낮습니다.A value below 10% is too low.
  • 버킷 내 체인:Chains within buckets:
    • 중복 인덱스 키 값이 없는 경우 평균 체인 길이 1이 이상적입니다.An average chain length of 1 is ideal in case there are no duplicate index key values. 체인 길이는 주로 최대 10까지 허용됩니다.Chain lengths up to 10 are usually acceptable.
    • 평균 체인 길이가 10보다 크고 빈 버킷 백분율이 10%보다 크면 데이터에 너무 많은 중복이 있어 해시 인덱스가 가장 적합한 형식이 아닐 수도 있습니다.If the average chain length is greater than 10, and the empty bucket percent is greater than 10%, the data has so many duplicates that a hash index might not be the most appropriate type.

체인 및 빈 버킷 데모Demonstration of chains and empty buckets

다음 T-SQL 코드 블록을 사용하여 SELECT * FROM sys.dm_db_xtp_hash_index_stats;을 간편하게 테스트할 수 있습니다.The following T-SQL code block gives you an easy way to test a SELECT * FROM sys.dm_db_xtp_hash_index_stats;. 코드 블록은 1분 내에 완료됩니다.The code block completes in 1 minute. 다음은 아래 코드 블록의 절입니다.Here are the phases of the following code block:

  1. 몇 개의 해시 인덱스가 있는 메모리 최적화 테이블을 만듭니다.Creates a memory-optimized table that has a few hash indexes.
  2. 수천 개의 행으로 테이블을 채웁니다.Populates the table with thousands of rows.
    1.a. 모듈로 연산자는 StatusCode 열에서 중복 값의 비율을 구성하는 데 사용됩니다.A modulo operator is used to configure the rate of duplicate values in the StatusCode column.
    2.b. 약 1분 이내에 루프에서 26만 2,144개의 행을 삽입합니다.The loop inserts 262,144 rows in approximately 1 minute.
  3. sys.dm_db_xtp_hash_index_stats에서 이전 SELECT를 실행하라는 메시지가 인쇄됩니다.PRINTs a message asking you to run the earlier SELECT from sys.dm_db_xtp_hash_index_stats.
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 루프는 다음을 수행합니다.The preceding INSERT loop does the following:

  • 기본 키 인덱스 및 ix_OrderSequence에 고유한 값을 삽입합니다.Inserts unique values for the primary key index, and for ix_OrderSequence.
  • StatusCode에 대해 8개의 고유 값만 표시하는 수백만 개의 행을 삽입합니다.Inserts a couple hundred thousands rows which represent only 8 distinct values for StatusCode. 따라서 인덱스 ix_StatusCode에는 값 중복 비율이 높습니다.Therefore there is a high rate of value duplication in index ix_StatusCode.

버킷 수가 최적이 아닌 경우 문제 해결을 위해 sys.dm_db_xtp_hash_index_stats에서 다음 SELECT 출력을 확인합니다.For troubleshooting when the bucket count is not optimal, examine the following output of the SELECT from sys.dm_db_xtp_hash_index_stats. 이러한 결과에 대해 섹션 D.1에서 복사한 SELECT에 WHERE Object_Name(h.object_id) = 'SalesOrder_Mem' 를 추가했습니다.For these results we added WHERE Object_Name(h.object_id) = 'SalesOrder_Mem' to the SELECT copied from section D.1.

SELECT 결과는 향상된 표시를 위해 좀 더 좁은 두 개의 결과 테이블로 분할되어 코드 뒤에 표시됩니다.Our SELECT results are displayed after the code, artificially split into two narrower results tables for better display.

  • 버킷 수에 대한 결과는 다음과 같습니다.Here are the results for bucket count.
IndexNameIndexName total_bucket_counttotal_bucket_count empty_bucket_countempty_bucket_count EmptyBucketPercentEmptyBucketPercent
IX_OrderSequenceix_OrderSequence 3276832768 1313 00
ix_StatusCodeix_StatusCode 88 44 5050
PK_SalesOrd_B14003...PK_SalesOrd_B14003... 262144262144 9652596525 3636
  • 다음은 체인 길이에 대한 결과입니다.Next are the results for chain length.
IndexNameIndexName avg_chain_lengthavg_chain_length max_chain_lengthmax_chain_length
IX_OrderSequenceix_OrderSequence 88 2626
ix_StatusCodeix_StatusCode 6553665536 6553665536
PK_SalesOrd_B14003...PK_SalesOrd_B14003... 11 88

3개의 해시 인덱스에 대한 위의 결과 테이블을 해석해 보겠습니다.Let us interpret the preceding results tables for the three hash indexes:

ix_StatusCodeix_StatusCode:

  • 버킷의 50%가 비어 있으며 상태가 정상입니다.50% of the buckets are empty, which is good.
  • 하지만 평균 체인 길이가 65536으로 매우 깁니다.However, the average chain length is very high at 65536.
    • 이것은 중복 값 비율이 높다는 것을 나타냅니다.This indicates a high rate of duplicate values.
    • 따라서 이 경우 해시 인덱스를 사용하는 것은 적합하지 않습니다.Therefore, using a hash index is not appropriate in this case. 대신 비클러스터형 인덱스를 사용해야 합니다.A nonclustered index should be used instead.

IX_OrderSequenceix_OrderSequence:

  • 버킷의 0%가 비어 있으며 너무 낮습니다.0% of the buckets are empty, which is too low.
  • 이 인덱스에 있는 모든 값은 고유하더라도 평균 체인 길이가 8입니다.The average chain length is 8, even though all values in this index are unique.
    • 따라서 평균 체인 길이가 2 또는 3으로 줄어들 수 있도록 버킷 수를 늘려야 합니다.Therefore the bucket count should be increased, to reduce the average chain length closer to 2 or 3.
  • 인덱스 키에 262,144개의 고유한 값이 있으므로 버킷 수가 적어도 262,144개가 되어야 합니다.Because the index key has 262144 unique values, the bucket count should be at least 262144.
    • 향후에 더 성장할 것으로 예상된다면 버킷 수를 더 높여야 합니다.If future growth is expected, the bucket count should be higher.

기본 키 인덱스(PK__SalesOrd_…)Primary key index (PK_SalesOrd_...):

  • 버킷의 36%가 비어 있으며 상태가 정상입니다.36% of the buckets are empty, which is good.
  • 평균 체인 길이가 1이면 좋습니다.The average chain length is 1, which is also good. 변경할 필요가 없습니다.No change is needed.

상충 관계 균형 조정Balancing the trade-off

OLTP는 작업 시 개별 행에 중점을 둡니다.OLTP workloads focus on individual rows. 일반적으로 전체 테이블 검색은 OLTP 작업에서 성능이 중요한 경로에 두지 않습니다.Full table scans are not usually in the performance critical path for OLTP workloads. 따라서 메모리 사용 수량같음 테스트 및 삽입 작업의 성능 간에 균형을 맞추어야 합니다.Therefore, the trade-off you must balance is between quantity of memory utilization versus performance of equality tests and insert operations.

메모리 사용률이 더 큰 문제일 경우If memory utilization is the bigger concern:

  • 인덱스 키 레코드의 수에 가까운 버킷 수를 선택합니다.Choose a bucket count close to the number of index key records.
  • 버킷 수가 인덱스 키 값 수보다 너무 적으면 안 됩니다. 이 경우 대부분의 DML 작업과 서버 재시작 후 데이터베이스를 복구하는 시간에 영향이 있습니다.The bucket count should not be significantly lower than the number of index key values, as this impacts most DML operations as well the time it takes to recover the database after server restart.

같음 테스트의 성능이 더 큰 문제일 경우If performance of equality tests is the bigger concern:

  • 버킷 수를 고유한 인덱스 값 수보다 2-3배 많이 지정하는 것이 좋습니다.A higher bucket count, of two or three times the number of unique index values, is appropriate. 더 높은 수는 다음을 의미합니다.A higher count means:
    • 하나의 특정 값을 찾을 때 더 빠르게 검색됩니다.Faster retrievals when looking for one specific value.
    • 메모리 사용률이 증가합니다.An increased memory utilization.
    • 해시 인덱스의 전체 검색에 필요한 시간이 늘어납니다.An increase in the time required for a full scan of the hash index.

더 보기Additional reading

메모리 최적화 테이블의 해시 인덱스 Hash Indexes for Memory-Optimized Tables
메모리 최적화 테이블의 비클러스터형 인덱스Nonclustered Indexes for Memory-Optimized Tables