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

적용 대상:Applies to: 예SQL ServerSQL Server(지원되는 모든 버전)yesSQL ServerSQL Server (all supported versions) 예Azure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database적용 대상:Applies to: 예SQL ServerSQL Server(지원되는 모든 버전)yesSQL ServerSQL Server (all supported versions) 예Azure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database

행을 함께 연결하는 인덱스이므로 모든 메모리 최적화 테이블에는 하나 이상의 인덱스가 있어야 합니다.All memory-optimized tables must have at least one index, because it is the indexes that connect the rows together. 메모리 최적화 테이블에서는 모든 인덱스 또한 메모리 최적화되어 있습니다.On a memory-optimized table, every index is also memory-optimized. 메모리 최적화 테이블의 인덱스와 디스크 기반 테이블의 기존 인덱스는 여러 방식에서 다릅니다.There are several ways in which an index on a memory-optimized table differs from a traditional index on a disk-base table:

  • 데이터 행은 페이지에 저장되지 않으므로 테이블의 모든 페이지를 가져오기 위해 참조할 수 있는 페이지 또는 익스텐트의 모음과 파티션 또는 할당 단위가 없습니다.Data rows are not stored on pages, so there is no collection of pages or extents, no partitions or allocation units that can be referenced to get all the pages for a table. 사용 가능한 인덱스 형식 중 하나에 대한 인덱스 페이지 개념이 있지만 디스크 기반 테이블의 인덱스와 다르게 저장됩니다.There is the concept of index pages for one of the available types of indexes, but they are stored differently than indexes for disk-based tables. fillfactor가 없으므로 페이지 내에서 기존 유형의 조각화가 발생하지 않습니다.They do not accrue the traditional type of fragmentation within a page, so they have no fillfactor.
  • 데이터 조작 중에 메모리 최적화 테이블의 인덱스에 대한 변경 내용은 디스크에 기록되지 않습니다.Changes made to indexes on memory-optimized tables during data manipulation are never written to disk. 데이터 행 및 데이터 변경 내용만 트랜잭션 로그에 기록됩니다.Only the data rows, and changes to the data, are written to the transaction log.
  • 데이터베이스가 다시 온라인이 될 때 메모리 액세스에 최적화된 인덱스가 다시 빌드됩니다.Memory-optimized indexes are rebuilt when the database is brought back online.

메모리 최적화 테이블의 모든 인덱스는 데이터베이스 복구 중에 인덱스 정의를 기반으로 만들어집니다.All indexes on memory-optimized tables are created based on the index definitions during database recovery.

인덱스는 다음 중 하나여야 합니다.The index must be one of the following:

  • 해시 인덱스Hash index
  • 메모리 최적화 비클러스터형 인덱스(B-트리의 기본 내부 구조를 의미)Memory-optimized Nonclustered index (meaning the default internal structure of a B-tree)

해시 인덱스는 메모리 최적화 테이블의 해시 인덱스에서 자세히 설명합니다.Hash indexes are discussed in more detail in Hash Indexes for Memory-Optimized Tables.
비클러스터형 인덱스는 메모리 최적화 테이블의 비클러스터형 인덱스에서 자세히 설명합니다.Nonclustered indexes are discussed in more detail in Nonclustered Index for Memory-Optimized Tables.
Columnstore 인덱스에 대해서는 다른 문서에서 설명합니다.Columnstore indexes are discussed in another article.

메모리 최적화 인덱스 구문Syntax for memory-optimized indexes

메모리 최적화 테이블에 대한 각 CREATE TABLE 문은 인덱스를 통해 명시적으로 또는 PRIMAY KEY 또는 UNIQUE 제약 조건을 통해 암시적으로 인덱스를 포함해야 합니다.Each CREATE TABLE statement for a memory-optimized table must include an index, either explicitly through an INDEX or implicitly through a PRIMAY KEY or UNIQUE constraint.

기본 DURABILITY = SCHEMA_AND_DATA를 사용하여 선언하려면 메모리 최적화 테이블에 기본 키가 있어야 합니다.To be declared with the default DURABILITY = SCHEMA_AND_DATA, the memory-optimized table must have a primary key. 다음 CREATE TABLE 문의 PRIMARY KEY NONCLUSTERED 절은 두 가지 요구 사항을 충족합니다.The PRIMARY KEY NONCLUSTERED clause in the following CREATE TABLE statement satisfies two requirements:

  • CREATE TABLE 문에서 하나의 인덱스의 최소 요구 사항을 충족할 인덱스를 제공합니다.Provides an index to meet the minimum requirement of one index in the CREATE TABLE statement.

  • SCHEMA_AND_DATA 절에 필요한 기본 키를 제공합니다.Provides the primary key that is required for the SCHEMA_AND_DATA clause.

    CREATE TABLE SupportEvent  
    (  
        SupportEventId   int NOT NULL  
            PRIMARY KEY NONCLUSTERED,  
        ...  
    )  
        WITH (  
            MEMORY_OPTIMIZED = ON,  
            DURABILITY = SCHEMA_AND_DATA);  
    

참고

SQL Server 2014(12.x)SQL Server 2014 (12.x) SQL Server 2016(13.x)SQL Server 2016 (13.x)에는 메모리 최적화 테이블 또는 테이블 형식당 8개의 인덱스 제한이 있습니다.and SQL Server 2016(13.x)SQL Server 2016 (13.x) have a limit of 8 indexes per memory-optimized table or table type. SQL Server 2017(14.x)SQL Server 2017 (14.x) 이상 및 Azure SQL DatabaseAzure SQL Database에서는 메모리 최적화 테이블 및 테이블 형식에 해당하는 인덱스 수 제한이 없어집니다.Starting with SQL Server 2017(14.x)SQL Server 2017 (14.x) and in Azure SQL DatabaseAzure SQL Database, there is no longer a limit on the number of indexes specific to memory-optimized tables and table types.

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

이 하위 섹션에는 메모리 최적화 테이블에 다양한 인덱스를 만드는 구문을 보여 주는 하는 TRANSACT-SQL 코드 블록이 포함되어 있습니다.This subsection contains a Transact-SQL code block that demonstrates the syntax to create various indexes on a memory-optimized table. 코드에서는 다음을 보여 줍니다.The code demonstrates the following:

  1. 메모리 최적화 테이블을 만듭니다.Create a memory-optimized table.

  2. ALTER TABLE 문을 사용하여 두 인덱스를 추가합니다.Use ALTER TABLE statements to add two indexes.

  3. 데이터 행을 몇 개 삽입합니다.INSERT a few rows of data.

    DROP TABLE IF EXISTS SupportEvent;  
    go  
    
    CREATE TABLE SupportEvent  
    (  
        SupportEventId   int               not null   identity(1,1)  
        PRIMARY KEY NONCLUSTERED,  
    
        StartDateTime        datetime2     not null,  
        CustomerName         nvarchar(16)  not null,  
        SupportEngineerName  nvarchar(16)      null,  
        Priority             int               null,  
        Description          nvarchar(64)      null  
    )  
        WITH (  
        MEMORY_OPTIMIZED = ON,  
        DURABILITY = SCHEMA_AND_DATA);  
    go  
    
        --------------------  
    
    ALTER TABLE SupportEvent  
        ADD CONSTRAINT constraintUnique_SDT_CN  
        UNIQUE NONCLUSTERED (StartDateTime DESC, CustomerName);  
    go  
    
    ALTER TABLE SupportEvent  
        ADD INDEX idx_hash_SupportEngineerName  
        HASH (SupportEngineerName) WITH (BUCKET_COUNT = 64);  -- Nonunique.  
    go  
    
        --------------------  
    
    INSERT INTO SupportEvent  
        (StartDateTime, CustomerName, SupportEngineerName, Priority, Description)  
        VALUES  
        ('2016-02-23 13:40:41:123', 'Abby', 'Zeke', 2, 'Display problem.'     ),  
        ('2016-02-24 13:40:41:323', 'Ben' , null  , 1, 'Cannot find help.'    ),  
        ('2016-02-25 13:40:41:523', 'Carl', 'Liz' , 2, 'Button is gray.'      ),  
        ('2016-02-26 13:40:41:723', 'Dave', 'Zeke', 2, 'Cannot unhide column.');  
    go 
    

중복된 인덱스 키 값Duplicate index key values

중복된 인덱스 키 값이 있으면 메모리 최적화 테이블의 성능이 저하될 수 있습니다.Duplicate values for an index key might reduce the performance of memory-optimized tables. 항목 체인을 트래버스하는 시스템의 중복 항목은 대부분의 인덱스 읽기 및 쓰기 작업에 대한 것입니다.Duplicates for the system to traverse entry chains for most index read and write operations. 중복 항목의 체인이 100개 항목을 초과할 경우 성능 저하가 측정될 수 있습니다.When a chain of duplicate entries exceeds 100 entries, the performance degradation can become measurable.

중복된 해시 값Duplicate hash values

이 문제는 해시 인덱스의 경우 더 두드러집니다.This problem is more visible in the case of hash indexes. 해시 인덱스의 경우 다음과 같은 고려 사항으로 인해 더 문제가 됩니다.Hash indexes suffer more due to the following considerations:

  • 해시 인덱스에 대한 작업당 비용이 더 적음The lower cost per operation for hash indexes.
  • 해시 충돌 체인이 있는 큰 중복 체인의 방해The interference of large duplicate chains with the hash collision chain.

인덱스에서 중복을 줄이려면 다음 조정을 수행합니다.To reduce duplication in an index, try the following adjustments:

  • 비클러스터형 인덱스를 사용합니다.Use a nonclustered index.
  • 인덱스 키의 마지막에 다른 열을 추가하여 중복 항목 수를 줄입니다.Add additional columns to the end of the index key, to reduce the number of duplicates.
    • 예를 들어, 기본 키에도 있는 열을 추가할 수 있습니다.For example, you could add columns that are also in the primary key.

해시 충돌에 대한 자세한 내용은 메모리 최적화 테이블의 해시 인덱스를 참조하세요.For more information about hash collisions, see Hash Indexes for Memory-Optimized Tables.

개선 예제Example improvement

다음은 인덱스에서 성능 비효율을 방지하는 방법의 예제입니다.Here is an example of how to avoid any performance inefficiency in your index.

CustomerId에 기본 키가 있는 Customers 테이블과 CustomerCategoryID 열의 인덱스를 고려합니다.Consider a Customers table that has a primary key on CustomerId, and has an index on column CustomerCategoryID. 일반적으로 많은 고객이 특정 범주에 속합니다.Typically there will be many customers in a given category. 이로 인해 인덱스의 주어진 키 안에 CustomerCategoryID에 대해 여러 중복된 값이 있게 됩니다.Thus there will be many duplicate values for CustomerCategoryID inside a given key of the index.

이 시나리오에서는 (CustomerCategoryID, CustomerId)에서 비클러스터형 인덱스를 사용하는 것이 모범 사례입니다.In this scenario, the best practice is to use a nonclustered index on (CustomerCategoryID, CustomerId). 이 인덱스는 CustomerCategoryID를 포함하는 조건자를 사용하는 쿼리에 사용할 수 있지만, 인덱스 키는 중복을 포함하지 않습니다.This index can be used for queries that use a predicate involving CustomerCategoryID, yet the index key does not contain duplication. 따라서 CustomerCategoryID 값 중복이나 인덱스의 추가 열에 따른 인덱스 유지 관리에서의 비효율이 발생하지 않습니다.Therefore, no inefficiencies in index maintenance are cause by either the duplicate CustomerCategoryID values, or by the extra column in the index.

다음 쿼리는 샘플 데이터베이스 CustomerCategoryID WideWorldImporters Sales.Customers의 테이블 에 있는에 대한 인덱스와 관련해 중복 인덱스 키 값의 평균 개수를 보여 줍니다.The following query shows the average number of duplicate index key values for the index on CustomerCategoryID in table Sales.Customers, in the sample database WideWorldImporters.

SELECT AVG(row_count) FROM
    (SELECT COUNT(*) AS row_count 
        FROM Sales.Customers
        GROUP BY CustomerCategoryID) a

고유 테이블 및 인덱스와 관련해 인덱스 키 중복 항목의 평균 개수를 구하려면 Sales.Customers 를 테이블 이름으로 바꾸고 CustomerCategoryID 를 인덱스 키 열 목록으로 바꿉니다.To evaluate the average number of index key duplicates for your own table and index, replace Sales.Customers with your table name, and replace CustomerCategoryID with the list of index key columns.

각 인덱스 유형을 사용하는 경우 비교Comparing when to use each index type

특정 쿼리의 특성에서 인덱스 유형이 적합한 선택인지 결정됩니다.The nature of your particular queries determines which type of index is the best choice.

해당 기능은 디스크 기반 테이블에서 기존 클러스터형 및 비클러스터형 인덱스의 기능과 유사하므로 기존 애플리케이션에서 메모리 최적화 테이블을 구현할 때 일반적으로 비클러스트형 인덱스로 시작하는 것이 좋습니다.When implementing memory-optimized tables in an existing application, the general recommendation is to start with nonclustered indexes, as their capabilities more closely resemble the capabilities of traditional clustered and nonclustered indexes on disk-based tables.

비클러스터형 인덱스 사용에 대한 권장 사항Recommendations for nonclustered index use

비클러스터형 인덱스는 다음의 경우 해시 인덱스 전체에서 선호됩니다.A nonclustered index is preferable over a hash index when:

  • 쿼리 시 인덱싱된 열에 ORDER BY 절이 포함됨Queries have an ORDER BY clause on the indexed column.
  • 다중 열 인덱스의 선행 열만 테스트하는 위치 쿼리Queries where only the leading column(s) of a multi-column index is tested.
  • 쿼리에서 WHERE 절을 다음과 같이 사용하여 인덱싱된 열 테스트:Queries test the indexed column by use of a WHERE clause with:
    • 같지 않음: WHERE StatusCode != 'Done'An inequality: WHERE StatusCode != 'Done'
    • 값 범위 검색: WHERE Quantity >= 100A value range scan: WHERE Quantity >= 100

다음 모든 SELECT 문에서는 비클러스터형 인덱스가 해시 인덱스보다 선호됩니다.In all the following SELECTs, a nonclustered index is preferable over a hash index:

SELECT CustomerName, Priority, Description 
FROM SupportEvent  
WHERE StartDateTime > DateAdd(day, -7, GetUtcDate());  

SELECT StartDateTime, CustomerName  
FROM SupportEvent  
ORDER BY StartDateTime DESC; -- ASC would cause a scan.

SELECT CustomerName  
FROM SupportEvent  
WHERE StartDateTime = '2016-02-26';  

해시 인덱스 사용에 대한 권장 사항Recommendations for hash index use

해시 인덱스는 주로 포인트 조회에 사용되고 범위 검색에는 사용되지 않습니다.Hash indexes are primarily used for point lookups and not for range scans.

해시 인덱스는 쿼리에서 같음 조건자를 사용할 경우 비클러스터형 인덱스보다 선호되고 WHERE 절은 다음 예제와 같이 모든 인덱스 키 열에 매핑됩니다.A hash index is preferable over a nonclustered index when queries use equality predicates, and the WHERE clause maps to all index key columns, as in the following example:

SELECT CustomerName 
FROM SupportEvent  
WHERE SupportEngineerName = 'Liz';

여러 열 인덱스Multi-column index

여러 열 인덱스는 비클러스터형 인덱스 또는 해시 인덱스일 수 있습니다.A multi-column index could be a nonclustered index or a hash index. 인덱스 열이 col1 및 col2라고 가정해 보겠습니다.Suppose the index columns are col1 and col2. 다음 SELECT 문이 주어진 경우 비클러스터형 인덱스만 쿼리 최적화 프로그램에 유용합니다.Given the following 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 query optimizer.

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

인덱스 사용 시나리오를 비교하기 위한 요약 테이블Summary table to compare index use scenarios

다음 표에서 다른 인덱스 유형에서 지원 되는 모든 작업을 나열 합니다.The following table lists all operations that are supported by the different index types. 는 인덱스가 요청을 효율적으로 처리할 수 있음을 의미하며 아니요는 인덱스를 사용하여 요청을 효과적으로 충족할 수 없음을 의미합니다.Yes means that the index can efficiently service the request, and No means that the index cannot efficiently satisfy the request.

작업(Operation)Operation 메모리 액세스에 최적화됨,Memory-optimized,
hashhash
메모리 액세스에 최적화됨,Memory-optimized,
비클러스터형nonclustered
디스크 기반,Disk-based,
(비)클러스터형(non)clustered
색인 검색은 모든 테이블 행을 검색합니다.Index Scan, retrieve all table rows. Yes Yes Yes
같음 조건자(=)에서 인덱스 검색Index seek on equality predicates (=). Yes
(전체 키는 필수)(Full key is required.)
Yes Yes
같지 않음 및 범위 조건자에서 인덱스 검색Index seek on inequality and range predicates
(>, <, <=, >=, BETWEEN).(>, <, <=, >=, BETWEEN).
No
(인덱스 검색의 결과)(Results in an index scan.)
1Yes 1 Yes
인덱스 정의와 일치하는 정렬 순서로 행을 검색합니다.Retrieve rows in a sort order that matches the index definition. No Yes Yes
인덱스 정의의 역순과 일치하는 정렬 순서로 행을 검색합니다.Retrieve rows in a sort-order that matches the reverse of the index definition. No No Yes
       

1 메모리 최적화 비클러스터형 인덱스의 경우 인덱스 검색을 수행하는 데 전체 키가 필요하지 않습니다.1 For a memory-optimized Nonclustered index, the full key is not required to perform an index seek.

자동 인덱스 및 통계 관리Automatic index and statistics management

Adaptive Index Defrag와 같은 솔루션을 사용하여 하나 이상의 데이터베이스에 대한 인덱스 조각 모음 및 통계 업데이트를 자동으로 관리합니다.Leverage solutions such as Adaptive Index Defrag to automatically manage index defragmentation and statistics updates for one or more databases. 이 절차는 다른 매개 변수 사이에서 조각화 수준에 따라 인덱스를 다시 작성하거나 다시 구성할지 여부를 자동으로 선택하고 통계를 선형 임계값으로 업데이트합니다.This procedure automatically chooses whether to rebuild or reorganize an index according to its fragmentation level, amongst other parameters, and update statistics with a linear threshold.

참고 항목See Also

SQL Server 인덱스 디자인 가이드 SQL Server Index Design Guide
메모리 최적화 테이블의 해시 인덱스 Hash Indexes for Memory-Optimized Tables
메모리 최적화 테이블의 비클러스터형 인덱스 Nonclustered Indexes for Memory-Optimized Tables
Adaptive Index DefragAdaptive Index Defrag