메모리 액세스에 최적화된 테이블의 해시 인덱스Hash Indexes for Memory-Optimized Tables

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

이 문서에서는 메모리 액세스에 최적화된 테이블에서 사용할 수 있는 해시 형식의 인덱스를 설명합니다.This article describes the hash type of index that is available for a memory-optimized table. 이 문서에서는 다음 내용을 다룹니다.The article:

  • TRANSACT-SQL 구문을 보여 주는 짧은 코드 예제를 제공합니다.Provides short code examples to demonstrate the Transact-SQL syntax.
  • 해시 인덱스의 기본 사항에 대해 설명합니다.Describes the fundamentals of hash indexes.
  • 적절한 버킷 수를 계산하는 방법에 대해 설명합니다.Describes how to estimate an appropriate bucket count.
  • 해시 인덱스를 디자인하고 관리하는 방법을 설명합니다.Describes how to design and manage your hash indexes.

필수 구성 요소Prerequisite

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

1.A. 메모리 액세스에 최적화된 인덱스 구문Syntax for memory-optimized indexes

A.1 구문에 대한 코드 샘플A.1 Code sample for syntax

이 하위 섹션에는 메모리 액세스에 최적화된 테이블에 해시 인덱스를 만드는 데 사용할 수 있는 구문을 보여 주는 하는 TRANSACT-SQL 코드 블록이 포함되어 있습니다.This subsection contains a Transact-SQL code block that demonstrates the available syntaxes to create a hash index on a memory-optimized table:

  • 샘플에는 해시 인덱스가 CREATE TABLE 문 내에 선언되어 있습니다.The sample shows the hash index is declared inside the CREATE TABLE statement.
<span data-ttu-id="4cdfc-118">DROP TABLE IF EXISTS SupportEventHash;</span><span class="sxs-lookup"><span data-stu-id="4cdfc-118">DROP TABLE IF EXISTS SupportEventHash;</span></span>  
<span data-ttu-id="4cdfc-119">go</span><span class="sxs-lookup"><span data-stu-id="4cdfc-119">go</span></span>  

<span data-ttu-id="4cdfc-120">CREATE TABLE SupportIncidentRating_Hash</span><span class="sxs-lookup"><span data-stu-id="4cdfc-120">CREATE TABLE SupportIncidentRating_Hash</span></span>  
<span data-ttu-id="4cdfc-121">(</span><span class="sxs-lookup"><span data-stu-id="4cdfc-121">(</span></span>  
  <span data-ttu-id="4cdfc-122">SupportIncidentRatingId   int      not null   identity(1,1)</span><span class="sxs-lookup"><span data-stu-id="4cdfc-122">SupportIncidentRatingId   int      not null   identity(1,1)</span></span>  
    <span data-ttu-id="4cdfc-123">PRIMARY KEY NONCLUSTERED,</span><span class="sxs-lookup"><span data-stu-id="4cdfc-123">PRIMARY KEY NONCLUSTERED,</span></span>  

  <span data-ttu-id="4cdfc-124">RatingLevel          int           not null,</span><span class="sxs-lookup"><span data-stu-id="4cdfc-124">RatingLevel          int           not null,</span></span>  

  <span data-ttu-id="4cdfc-125">SupportEngineerName  nvarchar(16)  not null,</span><span class="sxs-lookup"><span data-stu-id="4cdfc-125">SupportEngineerName  nvarchar(16)  not null,</span></span>  
  <span data-ttu-id="4cdfc-126">Description          nvarchar(64)      null,</span><span class="sxs-lookup"><span data-stu-id="4cdfc-126">Description          nvarchar(64)      null,</span></span>  

  <span data-ttu-id="4cdfc-127">INDEX ix_hash_SupportEngineerName</span><span class="sxs-lookup"><span data-stu-id="4cdfc-127">INDEX ix_hash_SupportEngineerName</span></span>  
    <span data-ttu-id="4cdfc-128">HASH (SupportEngineerName) WITH (BUCKET_COUNT = 100000)</span><span class="sxs-lookup"><span data-stu-id="4cdfc-128">HASH (SupportEngineerName) WITH (BUCKET_COUNT = 100000)</span></span>  
<span data-ttu-id="4cdfc-129">).</span><span class="sxs-lookup"><span data-stu-id="4cdfc-129">)</span></span>  
  <span data-ttu-id="4cdfc-130">WITH (</span><span class="sxs-lookup"><span data-stu-id="4cdfc-130">WITH (</span></span>  
    <span data-ttu-id="4cdfc-131">MEMORY_OPTIMIZED = ON,</span><span class="sxs-lookup"><span data-stu-id="4cdfc-131">MEMORY_OPTIMIZED = ON,</span></span>  
    <span data-ttu-id="4cdfc-132">DURABILITY = SCHEMA_ONLY);</span><span class="sxs-lookup"><span data-stu-id="4cdfc-132">DURABILITY = SCHEMA_ONLY);</span></span>  
<span data-ttu-id="4cdfc-133">go</span><span class="sxs-lookup"><span data-stu-id="4cdfc-133">go</span></span>  

사용 중인 데이터의 BUCKET_COUNT 가 올바른지 확인하려면 해시 인덱스 버킷 수 구성을 참조하세요.To determine the right BUCKET_COUNT for your data, see Configuring the hash index bucket count.

2.B. 해시 인덱스Hash indexes

B.1 성능 기본 사항B.1 Performance basics

해시 인덱스의 성능은 다음과 같습니다.The performance of a hash index is:

  • WHERE 절에서 해시 인덱스 키의 각 열에 대한 정확한 값을 지정할 때 뛰어납니다.Excellent when the WHERE clause specifies an exact value for each column in the hash index key.
  • WHERE 절에서 인덱스 키에 있는 값 범위 를 찾을 때 저하됩니다.Poor when the WHERE clause looks for a range of values in the index key.
  • WHERE 절에서 두 열로 된 해시 인덱스 키에서 첫 번째 열에 특정 값을 지정하지만 키의 두 번째 열에 값을 지정하지 않으면 저하됩니다.Poor when the WHERE clause specifies one specific value for the first column of a two column hash index key, but does not specify a value for the second column of the key.

B.2 선언 제한 사항B.2 Declaration limitations

해시 인덱스는 메모리 액세스에 최적화된 테이블에 대해서만 존재할 수 있습니다.A hash index can exist only on a memory-optimized table. 디스크 기반 테이블에는 있을 수 없습니다.It cannot exist on a disk-based table.

해시 인덱스를 다음으로 선언할 수 있습니다.A hash index can be declared as:

  • UNIQUE(또는 Nonunique를 기본값으로 설정할 수 있음)UNIQUE, or can default to Nonunique.
  • NONCLUSTERED, 기본값입니다.NONCLUSTERED, which is the default.

다음은 CREATE TABLE 문 외부에 해시 인덱스를 만드는 구문의 예입니다.Here is an example of the syntax to create a hash index outside of the CREATE TABLE statement:

ALTER TABLE MyTable_memop  
  ADD INDEX ix_hash_Column2 UNIQUE  
    HASH (Column2) WITH (BUCKET_COUNT = 64);  

B.3 버킷 및 해시 함수B.3 Buckets and hash function

해시 인덱스는 버킷 배열이라고 하는 키 값을 앵커합니다.A hash index anchors its key values in what we call a bucket array:

  • 각 버킷은 8바이트이며, 키 항목의 링크 목록의 메모리 주소를 저장하는 데 사용됩니다.Each bucket is 8 bytes, which are used to store the memory address of a link list of key entries.
  • 각 항목은 인덱스 키와, 기본 메모리 액세스에 최적화된 테이블에서 해당 행의 주소에 대한 값입니다.Each entry is a value for an index key, plus the address of its corresponding row in the underlying memory-optimized table.
    • 각 항목은 항목의 링크 목록에서 현재 버킷에 연결된 그 다음 항목을 가리킵니다.Each entry points to the next entry in a link list of entries, all chained to the current bucket.

해싱 알고리즘은 모든 고유 키 값을 해당 버킷 간에 균등하게 분산하려고 시도하지만 완벽한 균일성은 달성하기 힘든 이상적인 상황입니다.The hashing algorithm tries to spread all the unique or distinct key values evenly among its buckets, but total evenness is an unreached ideal. 주어진 키 값의 모든 인스턴스는 동일한 버킷에 연결됩니다.All instances of any given key value are chained to the same bucket. 버킷은 다른 키 값의 모든 인스턴스에서도 혼합될 수 있습니다.The bucket might also have mixed in all instances of a different key value.

  • 이 혼합을 해시 충돌이라고 합니다.This mixture is called a hash collision. 충돌은 일반적으로 발생하지만 좋은 현상은 아닙니다.Collisions are common but are not ideal.
  • 현실적인 목표는 버킷의 30%에 2개의 서로 다른 키 값을 포함하는 것입니다.A realistic goal is for 30% of the buckets contain two different key values.

해시 인덱스에서 보유할 수 있는 버킷 수를 결정합니다.You declare how many buckets a hash index shall have.

  • 테이블 행 또는 고유 값에 대한 버킷 비율이 낮을 수록 평균 버킷 링크 목록이 길어집니다.The lower the ratio of buckets to table rows or to distinct values, the longer the average bucket link list will be.
  • 링크 목록이 짧을 경우 성능 속도가 훨씬 빠릅니다.Short link lists perform faster than long link lists.

SQL Server에는 모든 해시 인덱스에 사용할 하나의 해시 함수가 있습니다.SQL Server has one hash function it uses for all hash indexes:

  • 해시 함수는 결정적으로, 동일한 입력 키 값이 제공되면 동일한 버킷 슬롯을 일관되게 출력합니다.The hash function is deterministic: given the same input key value, it consistently outputs the same bucket slot.
  • 반복된 호출이 있을 경우 해시 함수는 출력 시 평평한 선형 분포가 아닌 포아송 또는 벨 곡선 분포를 형성하는 경향이 있습니다.With repeated calls, the outputs of the hash function tend to form a Poisson or bell curve distribution, not a flat linear distribution.

해시 인덱스와 버킷의 상호 작용은 다음 그림에 요약되어 있습니다.The interplay of the hash index and the buckets is summarized in the following image.

hekaton_tables_23dhekaton_tables_23d

B.4 행 버전 및 가비지 수집B.4 Row versions and garbage collection

메모리 액세스에 최적화된 테이블에서 행이 SQL UPDATE의 영향을 받을 경우 테이블에서는 행의 업데이트된 버전을 만듭니다.In a memory-optimized table, when a row is affected by an SQL UPDATE, the table creates an updated version of the row. 업데이트 트랜잭션 동안 다른 세션에서는 이전 버전의 행을 읽을 수 있어 행 잠금과 관련 있는 성능 속도가 느려지는 것을 방지할 수 있습니다.During the update transaction, other sessions might be able to read the older version of the row and thereby avoid the performance slowdown associated with a row lock.

해시 인덱스에는 업데이트를 수용하기 위해 서로 다른 버전의 항목이 있을 수도 있습니다.The hash index might also have different versions of its entries to accommodate the update.

나중에 이전 버전이 더 이상 필요 없게 되면 GC(가비지 수집) 스레드가 버킷과 링크 목록을 트래버스하여 이전 항목을 정리합니다.Later when the older versions are no longer needed, a garbage collection (GC) thread traverses the buckets and their link lists to clean away old entries. 링크 목록 체인 길이가 짧은 경우 GC 스레드 성능은 향상됩니다.The GC thread performs better if the link list chain lengths are short.

3.C. 해시 인덱스 버킷 수 구성Configuring the hash index bucket count

해시 인덱스 버킷 수는 인덱스를 만들 때 지정되며, ALTER TABLE...ALTER INDEX REBUILD 구문을 사용하여 변경할 수 있습니다.The hash index bucket count is specified at index create time, and can be changed using the ALTER TABLE...ALTER INDEX REBUILD syntax.

대부분의 경우 버킷 수는 인덱스 키에 있는 고유한 값 수의 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.
특정 인덱스 키에 얼마나 많은 값이 지정될지 항상 예측할 수 있는 것은 아닙니다.You may not always be able to predict how many values a particular index key may have or will have. 성능은 일반적으로 BUCKET_COUNT 값이 실제 키 값 수의 10배 이내인 경우에 적절하게 유지되며 과대 평가하는 것이 과소 평가하는 것보다 더 좋습니다.Performance is usually still good if the BUCKET_COUNT value is within 10 times of the actual number of key values, and overestimating is generally better than underestimating.

버킷이 너무 적으면 다음과 같은 단점이 있습니다.Too few buckets has the following drawbacks:

  • 고유 키 값의 해시 충돌이 더 많아 집니다.More hash collisions of distinct key values.
    • 각 고유 값에서 다른 고유 값과 동일한 버킷을 강제로 공유합니다.Each distinct value is forced to share the same bucket with a different distinct value.
    • 버킷 당 평균 체인 길이가 증가합니다.The average chain length per bucket grows.
    • 버킷 체인이 길어질수록 인덱스에서 같음 조회 속도는 느려집니다.The longer the bucket chain, the slower the speed of equality lookups in the index and .

버킷이 너무 많으면 다음과 같은 단점이 있습니다.Too many buckets has the following drawbacks:

  • 버킷 수가 너무 높으면 빈 버킷이 더 많이 생성될 수 있습니다.Too high a bucket count might result in more empty buckets.
    • 빈 버킷은 전체 인덱스 검색 성능에 영향을 줍니다.Empty buckets impact the performance of full index scans. 전체 인덱스 검색을 정기적으로 수행하는 경우 고유한 인덱스 키 값의 수에 가까운 버킷 수를 선택하는 것이 좋습니다.If those are performed regularly, consider picking a bucket count close to the number of distinct index key values.
    • 각 버킷은 8바이트만 사용하지만 빈 버킷도 메모리를 사용합니다.Empty buckets use memory, though each bucket uses only 8 bytes.
참고

버킷을 더 많이 추가한다고 해서 중복 값을 공유하는 항목 연결이 줄어들지는 않습니다.Adding more buckets does nothing to reduce the chaining together of entries that share a duplicate value. 값 중복 속도는 버킷 수를 계산하기 위함이 아니라 해시가 적절한 인덱스 유형인지 결정하는 데 사용됩니다.The rate of value duplication is used to decide whether a hash is the appropriate index type, not to calculate the bucket count.

C.1 실제 수C.1 Practical numbers

BUCKET_COUNT 가 기본 범위에서 약간 위에 있거나 약간 아래에 있더라도 해시 인덱스의 성능은 지속할 수 있거나 허용 가능합니다.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. 문제가 되는 상황은 발생하지 않습니다.No crisis is created.

메모리 액세스에 최적화된 테이블에서 증가할 것으로 예측되는 행 수와 비교적 동일한 BUCKET_COUNT 를 해시 인덱스에 지정합니다.Give 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 you predict the quantity 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.

C.2 인덱스에 중복 값이 너무 많은 경우?C.2 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,

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

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

D.1 체인 및 빈 버킷 통계 모니터링D.1 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.

D.2 체인 및 빈 버킷 데모D.2 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분 내에 루프에서 행을 262144개 삽입합니다.The loop INSERTs 262144 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.

D.3 상충 관계 균형 조정D.3 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:

  • 메모리 사용률 vs.Quantity of memory utilization; versus
  • 같음 테스트 및 삽입 작업의 성능Performance of equality tests, and of 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.

5.E. 해시 인덱스의 장점Strengths of hash indexes

해시 인덱스는 다음과 같은 상황에서 비클러스터형 인덱스에서 선호됩니다.A hash index is preferable over a nonclustered index when:

  • 쿼리에서 WHERE 절을 다음과 같이 같음으로 사용하여 인덱싱된 열 테스트Queries test the indexed column by use of a WHERE clause with an equality, as in the following:
<span data-ttu-id="4cdfc-309">SELECT col9 FROM TableZ</span><span class="sxs-lookup"><span data-stu-id="4cdfc-309">SELECT col9 FROM TableZ</span></span>  
    <span data-ttu-id="4cdfc-310">WHERE Z_Id = 2174;</span><span class="sxs-lookup"><span data-stu-id="4cdfc-310">WHERE Z_Id = 2174;</span></span>  

E.1 다중 열 해시 인덱스 키E.1 Multi-column hash index keys

2열 인덱스는 비클러스터형 인덱스 또는 해시 인덱스일 수 있습니다.Your two column index could be a nonclustered index or a hash index. 인덱스 열이 col1 및 col2라고 가정해 보겠습니다.Suppose the index columns are col1 and col2. 다음 SQL SELECT 문이 주어진 경우 비클러스터형 인덱스만 쿼리 최적화 프로그램에 유용합니다.Given the following SQL SELECT statement, only the nonclustered index would be useful to the query optimizer:

SELECT col1, col3  
    FROM MyTable_memop  
    WHERE col1 = 'dn';  

해시 인덱스는 WHERE 절이 있어야 해당 키의 각 열에 대한 같음 테스트를 지정할 수 있습니다.The hash index needs the WHERE clause to specify an equality test for each of the columns in its key. 그렇지 않으면 해시 인덱스는 최적화 프로그램에 유용하지 않습니다.Else the hash index is not useful to the optimizer.

WHERE 절이 인덱스 키의 두 번째 열만 지정하는 경우에는 두 인덱스 유형 모두 유용하지 않습니다.Neither index type is useful if the WHERE clause specifies only the second column in the index key.