ALTER INDEX(Transact-SQL)

아티클
05/23/2024

적용 대상:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System(PDW)

인덱스를 사용하지 않도록 설정, 다시 빌드 또는 다시 구성하거나 인덱스에 대한 옵션을 설정하여 기존 테이블 또는 뷰 인덱스(rowstore, columnstore, 또는 XML)를 수정합니다.

Transact-SQL 구문 표기 규칙

구문

SQL Server, Azure SQL Database 및 Azure SQL Managed Instance에 대한 구문입니다.

ALTER INDEX { index_name | ALL } ON <object>
{
      REBUILD {
            [ PARTITION = ALL [ WITH ( <rebuild_index_option> [ , ...n ] ) ] ]
          | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_index_option> [ , ...n ] ) ] ]
      }
    | DISABLE
    | REORGANIZE  [ PARTITION = partition_number ] [ WITH ( <reorganize_option>  ) ]
    | SET ( <set_index_option> [ , ...n ] )
    | RESUME [ WITH (<resumable_index_option> [ , ...n ] ) ]
    | PAUSE
    | ABORT
}
[ ; ]

<object> ::=
{
    { database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }
}

<rebuild_index_option> ::=
{
      PAD_INDEX = { ON | OFF }
    | FILLFACTOR = fillfactor
    | SORT_IN_TEMPDB = { ON | OFF }
    | IGNORE_DUP_KEY = { ON | OFF }
    | STATISTICS_NORECOMPUTE = { ON | OFF }
    | STATISTICS_INCREMENTAL = { ON | OFF }
    | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
    | RESUMABLE = { ON | OFF }
    | MAX_DURATION = <time> [ MINUTES ]
    | ALLOW_ROW_LOCKS = { ON | OFF }
    | ALLOW_PAGE_LOCKS = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
    | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
        [ ON PARTITIONS ( { <partition_number> [ TO <partition_number> ] } [ , ...n ] ) ]
    | XML_COMPRESSION = { ON | OFF }
        [ ON PARTITIONS ( { <partition_number> [ TO <partition_number> ] } [ , ...n ] ) ] }

<single_partition_rebuild_index_option> ::=
{
      SORT_IN_TEMPDB = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
    | RESUMABLE = { ON | OFF }
    | MAX_DURATION = <time> [ MINUTES ]
    | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
    | XML_COMPRESSION = { ON | OFF }
    | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
}

<reorganize_option> ::=
{
       LOB_COMPACTION = { ON | OFF }
    |  COMPRESS_ALL_ROW_GROUPS =  { ON | OFF }
}

<set_index_option> ::=
{
      ALLOW_ROW_LOCKS = { ON | OFF }
    | ALLOW_PAGE_LOCKS = { ON | OFF }
    | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
    | IGNORE_DUP_KEY = { ON | OFF }
    | STATISTICS_NORECOMPUTE = { ON | OFF }
    | COMPRESSION_DELAY = { 0 | delay [ Minutes ] }
}

<resumable_index_option> ::=
 {
    MAXDOP = max_degree_of_parallelism
    | MAX_DURATION = <time> [ MINUTES ]
    | <low_priority_lock_wait>
 }

<low_priority_lock_wait> ::=
{
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ] ,
                          ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )
}

Azure Synapse Analytics 및 Analytics Platform System(PDW)에 대한 구문입니다.

ALTER INDEX { index_name | ALL }
    ON [ schema_name. ] table_name
{
      REBUILD {
            [ PARTITION = ALL [ WITH ( <rebuild_index_option> ) ] ]
          | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_index_option> ) ] ]
      }
    | DISABLE
    | REORGANIZE [ PARTITION = partition_number ]
}
[ ; ]

<rebuild_index_option> ::=
{
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
        [ ON PARTITIONS ( { <partition_number> [ TO <partition_number> ] } [ , ...n ] ) ]
    | XML_COMPRESSION = { ON | OFF }
        [ ON PARTITIONS ( { <partition_number> [ TO <partition_number> ] } [ , ...n ] ) ]
}

<single_partition_rebuild_index_option> ::=
{
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
    | XML_COMPRESSION = { ON | OFF }
}

참고 항목

SQL Server 2014(12.x) 이전 버전의 Transact-SQL 구문을 보려면 이전 버전 설명서를 참조하세요.

인수

index_name

인덱스의 이름입니다. 인덱스 이름은 테이블이나 뷰에서 고유해야 하지만 데이터베이스 내에서 고유할 필요는 없습니다. 인덱스 이름은 식별자 규칙을 따라야 합니다.

ALL

인덱스 유형에 관계없이 테이블이나 뷰에 연결된 모든 인덱스를 지정합니다. 지정하면 ALL 하나 이상의 인덱스가 오프라인 또는 읽기 전용 파일 그룹에 있거나 하나 이상의 인덱스 형식에서 지정된 작업이 허용되지 않는 경우 문이 실패합니다. 다음 표에서는 인덱스 작업과 허용되지 않는 인덱스 유형을 나열합니다.

이 작업에 키워드 ALL 사용	테이블에 다음 인덱스가 하나 이상 있으면 실패함
`REBUILD WITH ONLINE = ON`	XML 인덱스 공간 인덱스 Columnstore 인덱스 ¹
`REBUILD PARTITION = <partition_number>`	분할되지 않은 인덱스, XML 인덱스, 공간 인덱스 또는 비활성 인덱스
`REORGANIZE`	다음으로 `ALLOW_PAGE_LOCKS` 설정된 인덱스 `OFF`
`REORGANIZE PARTITION = <partition_number>`	분할되지 않은 인덱스, XML 인덱스, 공간 인덱스 또는 비활성 인덱스
`IGNORE_DUP_KEY = ON`	XML 인덱스 공간 인덱스 Columnstore 인덱스 ¹
`ONLINE = ON`	XML 인덱스 공간 인덱스 Columnstore 인덱스 ¹
`RESUMABLE = ON`²	키워드(keyword) 지원 `ALL` 되지 않는 다시 시작하는 인덱스

¹ SQL Server 2012(11.x) 이상 버전 및 Azure SQL Database에 적용됩니다.

² SQL Server 2017(14.x) 이상 버전 및 Azure SQL Database에 적용

지정PARTITION = <partition_number>한 경우 ALL 모든 인덱스를 정렬해야 합니다. 즉, 해당 파티션 함수를 기준으로 인덱스가 분할됩니다. 함께 PARTITION 사용하면 ALL 동일한 <partition_number> 인덱스 파티션이 모두 다시 작성되거나 다시 구성됩니다. 분할된 인덱스에 대한 자세한 내용은 분할된 테이블 및 인덱스를 참조 하세요.

온라인으로 수행할 수 있는 인덱스 작업에 대한 자세한 내용은 온라인 인덱스 작업에 대한 지침을 참조 하세요.

database_name

데이터베이스의 이름입니다.

schema_name

테이블이나 뷰가 속한 스키마의 이름입니다.

table_or_view_name

인덱스와 관련된 테이블이나 뷰의 이름입니다. 개체에 대한 인덱스 보고서를 표시하려면 sys.indexes 카탈로그 뷰를 사용합니다.

SQL Database는 database_name 현재 데이터베이스이거나 database_name table_or_view_name tempdb 시작하는 경우 세 부분으로 구성된 이름 형식 <database_name>.[schema_name].<table_or_view_name> 을 지원합니다#.

REBUILD [ WITH ( <rebuild_index_option> [ ,... n ] ) ]

적용 대상: SQL Server 2012(11.x) 이상 버전 및 Azure SQL Database

동일한 열, 인덱스 형식, 고유성 특성 및 정렬 순서를 사용하여 인덱스를 다시 작성하도록 지정합니다. 이 절은 DBCC DBREINDEX와 동일합니다. REBUILD는 비활성 인덱스를 활성화합니다. 클러스터형 인덱스를 다시 빌드해도 키워드(keyword) ALL 지정하지 않으면 연결된 비클러스터형 인덱스가 다시 작성되지 않습니다. 인덱스 옵션을 지정하지 않으면 sys.indexes에 저장된 기존 인덱스 옵션 값이 적용됩니다. sys.indexes에 값이 저장되지 않은 인덱스 옵션의 경우에는 옵션의 인수 정의에 표시된 기본값이 적용됩니다.

지정되고 기본 테이블이 힙 REBUILD 인 경우 ALL 이 작업은 테이블에 영향을 주지 않습니다. 테이블에 연결된 비클러스터형 인덱스는 모두 다시 작성됩니다.

데이터베이스 복구 모델이 대량 로그 또는 단순으로 설정되어 있으면 REBUILD 작업이 최소한으로 로그될 수 있습니다.

참고

기본 XML 인덱스를 다시 작성할 때는 인덱스 작업 중에 기본 사용자 테이블을 사용할 수 없습니다.

columnstore 인덱스의 경우 REBUILD 작업의 특성은 다음과 같습니다.

정렬 순서를 사용하지 않습니다.
REBUILD가 수행되는 동안 테이블 또는 파티션에 대한 배타적 잠금을 획득합니다. 데이터는 "오프라인"이며 사용 NOLOCK중REBUILD에도 RCSI(커밋된 스냅샷 격리) 또는 SI(스냅샷 격리)를 읽을 수 없습니다.
모든 데이터를 columnstore로 다시 압축합니다. REBUILD가 수행되는 동안 columnstore 인덱스의 두 복사본이 존재합니다. REBUILD가 완료되면 SQL Server에서 원래 columnstore 인덱스를 삭제합니다.

자세한 내용은 쿼리 성능을 향상시키고 리소스 소비를 줄이기 위해 인덱스 기본 테넌트 최적화를 참조하세요.

PARTITION

인덱스의 파티션을 하나만 다시 작성하거나 다시 구성되도록 지정합니다. PARTITIONindex_name 분할된 인덱스가 아닌 경우 지정할 수 없습니다.

PARTITION = ALL 는 모든 파티션을 다시 빌드합니다.

Warning

파티션이 1,000개 이상인 테이블에서 정렬되지 않은 인덱스를 만들고 다시 작성할 수 있지만 지원되지는 않습니다. 이렇게 하면 이러한 작업 중에 성능이 저하되거나 메모리가 과도하게 소비될 수 있습니다. Microsoft는 파티션 수가 1,000개를 초과하는 경우에는 정렬된 인덱스만 사용하는 것을 권장합니다.

partition_number

다시 작성하거나 다시 구성할 분할된 인덱스의 파티션 번호입니다. partition_number는 변수를 참조할 수 있는 상수 식입니다. 여기에는 사용자 정의 형식 변수 또는 함수와 사용자 정의 함수가 포함될 수 있지만 Transact-SQL 문은 참조할 수 없습니다. partition_number를 지정하지 않으면 해당 문이 실패합니다.
WITH ( <single_partition_rebuild_index_option> )

SORT_IN_TEMPDB, MAXDOP, DATA_COMPRESSION, XML_COMPRESSION은 단일 파티션(PARTITION = partition_number)을 REBUILD할 때 지정할 수 있는 옵션입니다. 단일 파티션 REBUILD 작업에서는 XML 인덱스를 지정할 수 없습니다.

DISABLE

인덱스를 비활성 및 데이터베이스 엔진에서 사용할 수 없음으로 표시합니다. 모든 인덱스를 비활성화할 수 있습니다. 비활성 인덱스의 인덱스 정의는 기본 인덱스 데이터 없이 시스템 카탈로그에 유지됩니다. 클러스터형 인덱스를 비활성화하면 사용자가 기본 테이블 데이터에 액세스하지 못합니다. 인덱스를 활성화하려면 ALTER INDEX REBUILD 또는 CREATE INDEX WITH DROP_EXISTING을 사용합니다. 자세한 내용은 인덱스 및 제약 조건 사용 안 함 및 인덱스 및 제약 조건 사용을 참조하세요.

rowstore 인덱스 재구성(REORGANIZE)

rowstore 인덱스의 경우 인 REORGANIZE 덱스 리프 수준을 다시 구성하도록 지정합니다. 작업은 REORGANIZE 다음과 같습니다.

항상 온라인으로 수행됩니다. 즉, 장기간 차단 테이블 잠금이 유지되지 않으며 ALTER INDEX REORGANIZE 트랜잭션 중 기본 테이블에 대한 쿼리나 업데이트를 계속할 수 있습니다.
비활성화된 인덱스에 대해서는 허용되지 않음
로 설정된 OFF경우 ALLOW_PAGE_LOCKS 허용되지 않습니다.
트랜잭션 내에서 수행되고 트랜잭션이 롤백되는 경우 롤백되지 않습니다.

참고 항목

ALTER INDEX REORGANIZE가 기본 암시적 트랜잭션 모드 대신 명시적 트랜잭션(예: BEGIN TRAN ... COMMIT/ROLLBACK 내부의 ALTER INDEX)을 사용하는 경우 REORGANIZE의 잠금 동작이 더 제한되어 차단이 발생할 수 있습니다. 암시적 트랜잭션에 대한 자세한 내용은 SET IMPLICIT_TRANSACTIONS 참조하세요.

자세한 내용은 쿼리 성능을 향상시키고 리소스 소비를 줄이기 위해 인덱스 기본 테넌트 최적화를 참조하세요.

REORGANIZE WITH ( LOB_COMPACTION = { ON | OFF } )

rowstore 인덱스에 적용됩니다.

LOB_COMPACTION = ON

image, text, ntext, varchar(max), nvarchar(max), varbinary(max), xml과 같은 큰 개체(LOB) 데이터 형식의 데이터를 포함하는 모든 페이지를 압축하도록 지정합니다. 이 데이터를 압축하면 디스크상의 데이터 크기를 줄일 수 있습니다.
클러스터형 인덱스의 경우 이렇게 하면 테이블에 포함된 모든 LOB 열이 압축됩니다.
비클러스터형 인덱스의 경우 이렇게 하면 인덱스에서 키가 아닌(포괄) 열인 LOB 열이 모두 압축됩니다.
REORGANIZE ALL은 모든 인덱스에 대해 LOB_COMPACTION을 수행합니다. 각 인덱스에 대해 클러스터형 인덱스의 모든 LOB 열, 기본 테이블 또는 비클러스터형 인덱스에 포함된 열이 압축됩니다.

LOB_COMPACTION = OFF

큰 개체 데이터가 포함된 페이지가 압축되지 않습니다.
OFF 은 힙에 영향을 주지 않습니다.

columnstore 인덱스 재구성(REORGANIZE)

columnstore 인덱스의 REORGANIZE 경우 각 CLOSED 델타 행 그룹을 압축된 행 그룹으로 columnstore로 압축합니다. REORGANIZE 작업은 항상 온라인으로 수행됩니다. 즉, 장기간 차단 테이블 잠금이 유지되지 않으며 ALTER INDEX REORGANIZE 트랜잭션 중 기본 테이블에 대한 쿼리나 업데이트를 계속할 수 있습니다. 자세한 내용은 쿼리 성능을 향상시키고 리소스 소비를 줄이기 위해 인덱스 기본 테넌트 최적화를 참조하세요.

REORGANIZE 는 델타 행 그룹을 압축된 행 그룹으로 이동하기 CLOSED 위해 필요하지 않습니다. TM(백그라운드 튜플 이동기) 프로세스가 주기적으로 절전 모드 해제되어 델타 행 그룹을 압축 CLOSED 합니다. 튜플 이동기가 뒤처질 때 사용하는 REORGANIZE 것이 좋습니다. REORGANIZE 는 행 그룹을 더 적극적으로 압축할 수 있습니다.
모든 OPEN 행 그룹과 CLOSED 행 그룹을 압축하려면 이 섹션의 REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS) 옵션을 참조하세요.

SQL Server(SQL Server 2016(13.x)부터) 및 Azure SQL Database REORGANIZE 의 columnstore 인덱스의 경우 온라인에서 다음과 같은 추가 조각 모음 최적화를 수행합니다.

행의 10% 이상이 논리적으로 삭제된 경우 rowgroup에서 행을 물리적으로 제거합니다. 삭제된 바이트는 물리적 미디어에서 회수됩니다. 예를 들어 100만 행의 압축된 행 그룹에 100,000개의 행이 삭제된 경우 SQL Server는 삭제된 행을 제거하고 행 그룹을 900k 행으로 다시 압축합니다. 즉, 삭제된 행을 제거하여 스토리지를 절약합니다.
하나 이상의 압축된 rowgroup을 결합하여 rowgroup당 행 수를 최대 1,048,576개로 증가시킵니다. 예를 들어 102,400개 행의 일괄 처리 5개 일괄 처리를 대량으로 가져오는 경우 5개의 압축된 행 그룹이 표시됩니다. 실행하는 REORGANIZE경우 이러한 행 그룹은 512,000개 행 크기의 압축된 1개 행 그룹으로 병합됩니다. 이때 사전 크기 또는 메모리 제한이 없는 것으로 가정합니다.
10% 이상의 행이 논리적으로 삭제된 행 그룹의 경우 SQL Server는 이 행 그룹을 하나 이상의 행 그룹과 결합하려고 합니다. 예를 들어 rowgroup 1이 행 500,000개를 사용하여 압축된다면 rowgroup 21은 최대 1,048,576개의 행을 사용하여 압축됩니다. 즉, rowgroup 21은 삭제된 행 60%와 남은 행 409,830개를 포함합니다. SQL Server는 이러한 두 행 그룹을 결합하여 909,830개의 행을 포함한 새 행 그룹을 압축하는 방법을 선호합니다.

REORGANIZE WITH ( COMPRESS_ALL_ROW_GROUPS = { ON | OFF } )

columnstore 인덱스에 적용됩니다.

적용 대상: SQL Server (SQL Server 2016(13.x)부터 시작) 및 Azure SQL Database

COMPRESS_ALL_ROW_GROUPS 는 columnstore에 행 그룹을 강제 OPEN 적용하거나 CLOSED 델타하는 방법을 제공합니다. 이 옵션을 사용할 때 델타 행 그룹을 비우기 위해 columnstore 인덱스를 다시 작성할 필요는 없습니다. 이 옵션은 다른 제거 및 병합 조각 모음 기능과 함께 결합할 경우 대부분의 상황에서 더 이상 인덱스를 다시 작성할 필요가 없습니다.

ON 는 크기 및 상태(CLOSED 또는 OPEN)에 관계없이 모든 행 그룹을 columnstore로 강제합니다.
OFF 는 모든 CLOSED 행 그룹을 columnstore로 강제합니다.

자세한 내용은 쿼리 성능을 향상시키고 리소스 소비를 줄이기 위해 인덱스 기본 테넌트 최적화를 참조하세요.

SET ( <set_index 옵션> [ ,... n ] )

인덱스를 다시 작성하거나 다시 구성하지 않고 인덱스 옵션을 지정합니다. SET 사용할 수 없는 인덱스로 지정할 수 없습니다.

PAD_INDEX = { ON | OFF }

인덱스 패딩을 지정합니다. 기본값은 OFF입니다.

ON

FILLFACTOR로 지정된 사용 가능한 공간의 비율이 인덱스의 중간 수준 페이지에 적용됩니다. 동시에 PAD_INDEXON지정되지 않은 경우 FILLFACTOR sys.indexes에 저장된 채우기 비율 값이 사용됩니다.
OFF 또는 fillfactor를 지정하지 않음

중간 수준 페이지는 용량 한도 가까이 채워집니다. 이로 인해 중간 페이지의 키 집합을 기준으로 인덱스에 포함될 수 있는 최대 크기의 행 하나 이상을 위한 충분한 공간이 남겨집니다.

자세한 내용은 CREATE INDEX를 참조하세요.

FILLFACTOR = fillfactor

인덱스를 만들거나 변경할 때 데이터베이스 엔진이 각 인덱스 페이지의 리프 수준을 채우는 비율을 지정합니다. fillfactor 값은 1에서 100 사이의 정수 값이어야 합니다. 기본값은 0입니다. 채우기 비율 값 0과 100은 모든 면에서 동일합니다.

명시적 FILLFACTOR 설정은 인덱스를 처음 만들거나 다시 빌드할 때만 적용됩니다. 데이터베이스 엔진에서는 페이지에 지정된 비율의 빈 공간을 동적으로 유지하지 않습니다. 자세한 내용은 CREATE INDEX를 참조하세요.

채우기 비율 설정을 보려면 sys.indexes의 fill_factor를 사용합니다.

중요

데이터베이스 엔진에서는 클러스터형 인덱스를 만들 때 데이터를 다시 배포하므로 FILLFACTOR 값을 사용하여 클러스터형 인덱스를 만들거나 변경하면 데이터가 차지하는 스토리지 공간 크기에 영향을 줍니다.

SORT_IN_TEMPDB = { ON | OFF }

정렬 결과를 저장할지 여부를 지정합니다 tempdb. 기본값은 OFF Azure SQL Database 하이퍼스케일을 제외한 것입니다. 하이퍼스케일의 모든 인덱스 다시 빌드 작업에서 SORT_IN_TEMPDB는 다시 시작 가능한 인덱스 다시 빌드를 사용하지 않는 한, 지정한 옵션과 관계없이 항상 ON입니다.

ON

인덱스 작성에 사용되는 중간 정렬 결과는 에 저장 tempdb됩니다. 사용자 데이터베이스와 다른 디스크 집합에 있는 경우 tempdb 인덱스를 만드는 데 필요한 시간이 줄어들 수 있습니다. 그러나 인덱스 작성 중에 사용되는 디스크 공간의 크기는 커집니다.
OFF

중간 정렬 결과가 인덱스와 같은 데이터베이스에 저장됩니다.

정렬 작업이 필요하지 않거나 메모리에서 정렬을 수행할 수 있으면 SORT_IN_TEMPDB 옵션이 무시됩니다.

자세한 내용은 인덱스에 대한 SORT_IN_TEMPDB 옵션을 참조하세요.

IGNORE_DUP_KEY = { ON | OFF }

삽입 작업에서 고유 인덱스에 중복된 키 값을 삽입하려는 경우에 대한 오류 응답을 지정합니다. IGNORE_DUP_KEY 옵션은 인덱스를 만들거나 다시 빌드한 후의 삽입 작업에만 적용됩니다. 기본값은 OFF입니다.

ON

중복된 키 값이 고유 인덱스에 삽입되는 경우 경고 메시지가 나타나고 고유성 제약 조건을 위반하는 행만 실패합니다.
OFF

중복된 키 값이 고유 인덱스에 삽입되는 경우 오류 메시지가 나타나고 전체 INSERT 작업이 롤백됩니다.

IGNORE_DUP_KEY 뷰에서 만든 인덱스, 고유하지 않은 인덱스, XML 인덱스, 공간 인덱스 및 필터링된 인덱스에 대해 설정할 ON 수 없습니다.

IGNORE_DUP_KEY를 보려면 sys.indexes를 사용합니다.

이전 버전과 호환되는 구문에서 WITH IGNORE_DUP_KEY는 WITH IGNORE_DUP_KEY = ON과 동일합니다.

STATISTICS_NORECOMPUTE = { ON | OFF }

지정된 인덱스와 관련된 통계에 대해 자동 통계 업데이트 옵션을 AUTO_STATISTICS_UPDATE사용하지 않도록 설정하거나 사용하도록 설정합니다. 기본값은 OFF입니다.

ON

인덱스를 다시 작성한 후 자동 통계 업데이트를 사용할 수 없습니다.
OFF

자동 통계 업데이트는 인덱스를 다시 빌드한 후에 사용하도록 설정됩니다.

자동 통계 업데이트를 복원하려면 절 없이 STATISTICS_NORECOMPUTE />로 OFF설정하거나 실행 UPDATE STATISTICS 합니다 NORECOMPUTE .

Warning

통계 자동 업데이트를 사용하지 않도록 설정하면 쿼리 최적화 프로그램에서 테이블과 관련된 쿼리에 대한 최적의 실행 계획을 선택하지 못할 수 있습니다. 이 옵션은 정규화된 데이터베이스 관리자만 사용해야 합니다.

이 설정은 다시 빌드 작업 중에 인덱스 관련 통계의 fullscan을 사용하여 자동 업데이트를 방지하지 않습니다.

STATISTICS_INCREMENTAL = {ON | OFF}

적용 대상: SQL Server 2014(12.x) 이상 버전 및 Azure SQL Database

생성 ON되는 통계는 파티션 통계당입니다. OFF통계 트리가 삭제되고 SQL Server가 통계를 다시 계산합니다. 기본값은 OFF입니다.

파티션별 통계가 지원되지 않으면 옵션이 무시되고 경고가 생성됩니다. 다음 통계 유형에 대해서는 증분 통계가 지원되지 않습니다.

기본 테이블을 기준으로 파티션 정렬되지 않은 인덱스를 사용하여 작성된 통계
가용성 그룹 읽기 가능한 보조 데이터베이스에 대해 작성된 통계
읽기 전용 데이터베이스에 대해 작성된 통계
필터링된 인덱스에 대해 작성된 통계
뷰에 대해 작성된 통계
내부 테이블에 대해 작성된 통계
공간 인덱스 또는 XML 인덱스를 사용하여 작성된 통계

ONLINE = { ON | OFF } <rebuild_index_option에 적용>

인덱스 작업 중 쿼리 및 데이터 수정에 기본 테이블과 관련 인덱스를 사용할 수 있는지 여부를 지정합니다. 기본값은 OFF입니다.

XML 인덱스 또는 공간 인덱 ONLINE = OFF 스에 대해서만 지원되며 오류로 ON 설정된 경우에만 ONLINE 발생합니다.

Important

온라인 인덱스 작업은 Microsoft SQL Server의 모든 버전에서 사용할 수 없습니다. SQL Server 버전에서 지원되는 기능 목록은 SQL Server 2022의 버전과 지원하는 기능을 참조하세요.

ON

인덱스 작업 중에 장기 테이블 잠금이 유지되지 않습니다. 인덱스 작업의 주 단계 중 내재된 공유(IS) 잠금만 원본 테이블에 유지됩니다. 따라서 기본 테이블 및 인덱스를 계속 쿼리 또는 업데이트할 수 있습니다. 작업을 시작할 때 공유(S) 잠금은 원본 개체에 잠시 유지됩니다. 작업이 끝나면 비클러스터형 인덱스가 만들어지는 경우 S 잠금이 원본에서 잠시 유지됩니다. 스키마 수정(Sch-M) 잠금은 클러스터형 인덱스를 만들거나 온라인으로 삭제하고 클러스터형 또는 비클러스터형 인덱스를 다시 작성할 때 획득됩니다. ONLINE 는 인덱스가 로컬 임시 테이블에 생성되는 시점으로 설정할 ON 수 없습니다.
OFF

인덱스 작업 중에 테이블 잠금이 적용됩니다. 클러스터형 인덱스, 공간 인덱스 또는 XML 인덱스를 생성, 다시 작성 또는 삭제하거나 비클러스터형 인덱스를 다시 작성 또는 삭제하는 오프라인 인덱스 작업은 테이블에 대해 SCH-M(스키마 수정) 잠금을 획득합니다. 이렇게 하면 작업 중에 모든 사용자가 기본 테이블에 액세스할 수 없게 됩니다. 비클러스터형 인덱스를 만드는 오프라인 인덱스 작업을 통해 테이블의 S(공유) 잠금을 획득합니다. 이렇게 하면 기본 테이블에 대한 업데이트를 방지하지만 문과 같은 SELECT 읽기 작업을 허용합니다.

자세한 내용은 온라인으로 인덱스 작업 수행을 참조하세요.

전역 임시 테이블의 인덱스를 비롯한 인덱스를 온라인으로 다시 빌드할 수 있습니다. 단, 다음 사례는 예외입니다.

XML 인덱스
로컬 임시 테이블의 인덱스
뷰의 초기 고유 클러스터형 인덱스
columnstore 인덱스
기본 테이블이 LOB 데이터 형식(image, ntext, text) 및 공간 데이터 형식을 포함하는 경우 클러스터형 인덱스입니다.
varchar(max) 및 varbinary(max) 열은 인덱스의 일부일 수 없습니다. SQL Server(SQL Server 2012(11.x)부터) 및 Azure SQL Database에서 테이블에 varchar(max) 또는 varbinary(max) 열이 포함된 경우 ONLINE 옵션을 사용하여 다른 열이 포함된 클러스터형 인덱스를 빌드하거나 다시 빌드할 수 있습니다. Azure SQL Database는 기본 테이블에 varchar(max) 또는 varbinary(max) 열이 포함된 경우 ONLINE 옵션을 허용하지 않습니다.

자세한 내용은 온라인 인덱스 작업의 작동 원리를 참조하세요.

다음 XEvents는 온라인 인덱스 다시 작성과 관련이 ALTER TABLE ... SWITCH PARTITION 있습니다.

lock_request_priority_state
process_killed_by_abort_blockers
ddl_with_wait_at_low_priority

온라인 인덱스 작업에 대한 기존 XEvent progress_report_online_index_operation 에는 다음이 포함됩니다 partition_numberpartition_id.

RESUMABLE = { ON | OFF}

적용 대상: SQL Server 2017(14.x) 이상 버전 및 Azure SQL Database

온라인 인덱스 작업이 다시 시작될 수 있는지 여부를 지정합니다.

켜기

인덱스 작업이 다시 시작될 수 있습니다.
OFF

인덱스 작업이 다시 시작될 수 없습니다.

MAX_DURATION = time [ MINUTES ] 와 함께 `RESUMABLE = ON` 사용됨(필수 `ONLINE = ON`)

적용 대상: SQL Server 2017(14.x) 이상 버전 및 Azure SQL Database

다시 시작할 수 있는 온라인 인덱스 작업이 일시 중지하기 전에 실행된 시간을 나타냅니다(분 단위로 지정된 정수 값).

중요

온라인으로 수행할 수 있는 인덱스 작업에 대한 자세한 내용은 온라인 인덱스 작업에 대한 지침을 참조 하세요.

참고 항목

다시 시작하는 온라인 인덱스 다시 작성은 columnstore 인덱스에 지원되지 않습니다.

ALLOW_ROW_LOCKS = { ON | OFF }

행 잠금의 허용 여부를 지정합니다. 기본값은 ON입니다.

ON

인덱스에 액세스할 때 행 잠금이 허용됩니다. 행 잠금을 사용하는 시점은 데이터베이스 엔진이 결정합니다.
OFF

행 잠금은 사용되지 않습니다.

ALLOW_PAGE_LOCKS = { ON | OFF }

페이지 잠금의 허용 여부를 지정합니다. 기본값은 ON입니다.

ON

인덱스에 액세스할 때 페이지 잠금이 허용됩니다. 페이지 잠금을 사용하는 시점은 데이터베이스 엔진이 결정합니다.
OFF

페이지 잠금은 사용되지 않습니다.

참고 항목

인덱스는 로 설정된 OFF경우 ALLOW_PAGE_LOCKS 다시 구성할 수 없습니다.

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

적용 대상: SQL Server 2019(15.x) 이상 버전 및 Azure SQL Database

마지막 페이지 삽입 경합에 최적화할지 여부를 지정합니다. 기본값은 OFF입니다. 자세한 내용은 순차 키를 참조하세요.

MAXDOP = max_degree_of_parallelism

인덱스 작업 동안 max degree of parallelism 구성 옵션을 재정의합니다. 자세한 내용은 최대 병렬 처리 수준 구성(서버 구성 옵션)을 참조하세요. 병렬 계획 실행에 사용되는 프로세서 수를 제한하는 데 사용합니다 MAXDOP . 최대값은 64개입니다.

Important

MAXDOP 이 옵션은 모든 XML 인덱스에 대해 구문적으로 지원되지만 공간 인덱스 또는 기본 XML 인덱 ALTER INDEX 스에 대해서는 현재 단일 프로세서만 사용합니다.

max_degree_of_parallelism은 다음 중 하나일 수 있습니다.

1: 병렬 계획 생성을 표시하지 않습니다.
>1: 병렬 인덱스 작업에 사용되는 프로세서의 최대 수를 지정된 수로 제한합니다.
0 (기본값): 현재 시스템 워크로드에 따라 실제 프로세서 수 이하를 사용합니다.

자세한 내용은 병렬 인덱스 작업 구성을 참조하세요.

참고

병렬 인덱스 작업은 일부 SQL Server 버전에서 사용할 수 있습니다. SQL Server 버전에서 지원되는 기능 목록은 SQL Server 2022의 버전과 지원하는 기능을 참조하세요.

COMPRESSION_DELAY = { 0 | duration [ minutes ] }

적용 대상: SQL Server (SQL Server 2016(13.x)부터 시작)

디스크 기반 테이블의 경우 지연은 SQL Server가 압축된 행 그룹으로 압축하기 전에 델타 행 그룹이 CLOSED 델타 행 그룹에 다시 기본 최소 시간(분)을 지정합니다. 디스크 기반 테이블은 개별 행의 삽입 및 업데이트 시간을 추적하지 않으므로 SQL Server는 상태의 델타 행 그룹에 지연을 CLOSED 적용합니다.

기본값은 0분입니다.

사용 COMPRESSION_DELAY시기에 대한 권장 사항은 실시간 운영 분석에 대한 Columnstore 시작을 참조 하세요.

DATA_COMPRESSION

지정된 인덱스, 파티션 번호 또는 파티션 범위에 대한 데이터 압축 옵션을 지정합니다. 옵션은 다음과 같습니다.

없음

인덱스 또는 지정된 파티션이 압축되지 않습니다. columnstore 인덱스에는 적용되지 않습니다.
ROW

인덱스 또는 지정된 파티션이 행 압축을 사용하여 압축됩니다. columnstore 인덱스에는 적용되지 않습니다.
PAGE

인덱스 또는 지정된 파티션이 페이지 압축을 사용하여 압축됩니다. columnstore 인덱스에는 적용되지 않습니다.
COLUMNSTORE

적용 대상: SQL Server 2014(12.x) 이상 버전 및 Azure SQL Database

클러스터형 columnstore 인덱스 및 비클러스터형 columnstore 인덱스를 모두 포함하는 columnstore 인덱스에만 적용됩니다. COLUMNSTORE 는 옵션으로 COLUMNSTORE_ARCHIVE 압축된 인덱스 또는 지정된 파티션의 압축을 풉니다. 데이터가 복원되면 모든 columnstore 인덱스에 사용되는 columnstore 압축을 사용하여 계속 압축됩니다.
COLUMNSTORE_ARCHIVE

적용 대상: SQL Server 2014(12.x) 이상 버전 및 Azure SQL Database

클러스터형 columnstore 인덱스 및 비클러스터형 columnstore 인덱스를 모두 포함하는 columnstore 인덱스에만 적용됩니다. COLUMNSTORE_ARCHIVE 는 지정된 파티션을 더 작은 크기로 압축합니다. 보다 적은 스토리지 크기가 필요한 기타 상황에서 보관하는 데 사용할 수 있으며 저장 및 검색에 더 많은 시간을 이용할 수 있습니다.

압축에 대한 자세한 내용은 데이터 압축을 참조하세요.

XML_COMPRESSION

적용 대상: SQL Server 2022(16.x) 이상 버전, Azure SQL Database 및 Azure SQL Managed Instance.

하나 이상의 xml 데이터 형식 열을 포함하는 지정된 인덱스에 대한 XML 압축 옵션을 지정합니다. 옵션은 다음과 같습니다.

켜기

인덱스 또는 지정된 파티션이 XML 압축을 사용하여 압축됩니다.
OFF

인덱스 또는 지정된 파티션이 압축되지 않습니다.

ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,... n ] )

DATA_COMPRESSION 또는 XML_COMPRESSION 설정이 적용되는 파티션을 지정합니다. 인덱스가 분할되지 않으면 인수에서 ON PARTITIONS 오류가 발생합니다. ON PARTITIONS 절을 제공하지 않으면 DATA_COMPRESSION 또는 XML_COMPRESSION 옵션이 분할된 인덱스의 모든 파티션에 적용됩니다.

<partition_number_expression>은 다음과 같은 방법으로 지정할 수 있습니다.

파티션의 번호를 지정합니다(예: ON PARTITIONS (2)).
여러 개별 파티션의 파티션 번호를 쉼표로 구분하여 지정합니다(예: ON PARTITIONS (1, 5)).
범위와 개별 파티션을 모두 지정합니다(예: ON PARTITIONS (2, 4, 6 TO 8)).

<range>는 TO라는 단어로 구분된 파티션 번호로 지정할 수 있습니다(예: ON PARTITIONS (6 TO 8)).

여러 파티션에 대해 서로 다른 데이터 압축 유형을 설정하려면 DATA_COMPRESSION 옵션을 두 번 이상 지정합니다. 예를 들면 다음과 같습니다.

REBUILD WITH
(
DATA_COMPRESSION = NONE ON PARTITIONS (1),
DATA_COMPRESSION = ROW ON PARTITIONS (2, 4, 6 TO 8),
DATA_COMPRESSION = PAGE ON PARTITIONS (3, 5)
);

XML_COMPRESSION 옵션을 두 번 이상 지정할 수도 있습니다. 예를 들면 다음과 같습니다.

REBUILD WITH
(
  XML_COMPRESSION = OFF ON PARTITIONS (1),
  XML_COMPRESSION = ON ON PARTITIONS (2, 4, 6 TO 8),
  XML_COMPRESSION = OFF ON PARTITIONS (3, 5)
);

ONLINE = { ON | OFF } <single_partition_rebuild_index_option에 적용>

기본 테이블의 인덱스 또는 인덱스 파티션을 온라인 또는 오프라인으로 다시 작성할 수 있는지 여부를 지정합니다. REBUILD ... ONLINE = ON을 수행하는 경우 인덱스 작업 중에 이 테이블의 데이터를 쿼리 및 데이터 수정에 사용할 수 있습니다. 기본값은 OFF입니다.

ON

인덱스 작업 중에 장기 테이블 잠금이 유지되지 않습니다. 인덱스 작업의 주 단계 중 내재된 공유(IS) 잠금만 원본 테이블에 유지됩니다. 인덱스 다시 작성을 시작할 때 테이블에 대한 Sch-S(스키마 안정성) 잠금이 필요하며, 온라인 인덱스 다시 작성이 끝날 때 테이블에 Sch-M(스키마 수정) 잠금이 필요합니다. 두 메타데이터 잠금 모두 지속 시간이 짧지만 Sch-M 잠금은 특히 모든 차단 트랜잭션이 완료될 때까지 기다려야 합니다. 대기 시간 동안 Sch-M 잠금은 동일 테이블에 액세스할 때 이 잠금 뒤에서 기다리는 다른 모든 트랜잭션을 차단합니다.

참고

온라인 인덱스를 다시 빌드할 때 low_priority_lock_wait 옵션을 설정할 수 있습니다. 온라인 인덱스 작업의 WAIT_AT_LOW_PRIORITY를 참조하세요.
OFF

인덱스 작업 중에 테이블 잠금이 적용됩니다. 이렇게 하면 작업 중에 모든 사용자가 기본 테이블에 액세스할 수 없게 됩니다.

RESUME

적용 대상: SQL Server 2017(14.x) 이상 버전 및 Azure SQL Database

수동으로 일시 중지되거나 실패로 인해 발생한 인덱스 작업을 다시 시작합니다.

RESUMABLE = ON에 사용되는 MAX_DURATION

다시 시작된 후 다시 시작할 수 있는 온라인 인덱스 작업이 실행되는 시간(분 단위로 지정한 정수 값)입니다. 시간이 만료된 후 다시 시작할 수 있는 작업이 여전히 실행 중인 경우 일시 중지됩니다.
WAIT_AT_LOW_PRIORITY 및 .와 함께 RESUMABLE = ONONLINE = ON사용됩니다.

일시 중지 후 온라인 인덱스 다시 작성을 다시 시작하려면 이 테이블에 대한 작업이 차단되기를 기다려야 합니다. WAIT_AT_LOW_PRIORITY 는 온라인 인덱스 다시 작성 작업이 낮은 우선 순위 잠금을 대기하므로 온라인 인덱스 빌드 작업이 대기하는 동안 다른 작업이 계속 진행되도록 합니다. WAIT_AT_LOW_PRIORITY 옵션을 생략하면 WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE)와 동일합니다. 자세한 내용은 WAIT_AT_LOW_PRIORITY를 참조하세요.

일시 중지

적용 대상: SQL Server 2017(14.x) 이상 버전 및 Azure SQL Database

다시 시작 가능한 온라인 인덱스 다시 작성 작업을 일시 중지합니다.

중단

적용 대상: SQL Server 2017(14.x) 이상 버전 및 Azure SQL Database

다시 시작한 것으로 선언된 실행 중이거나 일시 중지된 인덱스 작업을 중단합니다. 다시 시작 가능한 인덱스 다시 빌드 작업을 종료하려면 ABORT 명령을 명시적으로 실행해야 합니다. 다시 시작할 수 있는 인덱스 작업이 실패하거나 일시 중지해도 실행이 종료되지 않으며, 그 대신 작업이 무한한 일시 중지 상태로 남아 있습니다.

설명

인덱스를 다시 분할하거나 다른 파일 그룹으로 이동하는 데는 ALTER INDEX를 사용할 수 없습니다. 이 문을 사용하여 열 추가 또는 삭제, 열 순서 변경과 같은 인덱스 정의를 수정할 수 없습니다. 이러한 작업을 수행하려면 DROP_EXISTING 절과 CREATE INDEX를 함께 사용하세요.

옵션을 명시적으로 지정하지 않으면 현재 설정이 적용됩니다. 예를 들어 절에 REBUILD 설정이 지정되지 않은 경우 FILLFACTOR 다시 빌드 프로세스 중에 시스템 카탈로그에 저장된 채우기 비율 값이 사용됩니다. 현재 인덱스 옵션 설정을 보려면 sys.indexes를 사용하세요.

ONLINE, MAXDOP, SORT_IN_TEMPDB 값은 시스템 카탈로그에 저장되지 않습니다. 인덱스 문에서 지정하지 않으면 해당 옵션의 기본값이 사용됩니다.

다중 프로세서 컴퓨터에서는 다른 쿼리의 경우와 마찬가지로 ALTER INDEX REBUILD가 자동으로 프로세서를 더 사용하여 인덱스 수정과 관련된 정렬 및 검색 작업을 수행합니다. 병렬 처리 값의 최대 수준을 사용하거나 사용하지 않고 LOB_COMPACTION실행하는 ALTER INDEX REORGANIZE경우 단일 스레드 작업입니다. 자세한 내용은 병렬 인덱스 작업 구성을 참조하세요.

Important

인덱스가 있는 파일 그룹이 오프라인이거나 읽기 전용으로 설정된 경우 인덱스를 다시 구성하거나 다시 작성할 수 없습니다. 키워드(keyword) ALL 지정되고 하나 이상의 인덱스가 오프라인 또는 읽기 전용 파일 그룹에 있으면 문이 실패합니다.

인덱스 다시 작성

인덱스를 다시 작성하면 이 인덱스가 삭제된 다음 다시 생성됩니다. 이렇게 하면 조각화를 제거하고, 지정된 채우기 비율 또는 기존 채우기 비율 설정을 기준으로 페이지를 압축하여 디스크 공간을 회수하고, 인덱스 행을 연속된 페이지로 다시 정렬할 수 있습니다. ALL 지정되면 테이블의 모든 인덱스가 삭제되고 단일 트랜잭션에서 다시 작성됩니다. 외래 키 제약 조건은 미리 삭제하지 않아도 됩니다. 익스텐트가 128개 이상인 인덱스를 다시 작성하면 데이터베이스 엔진에서 실제 페이지 할당 취소와 해당 관련 잠금이 트랜잭션 커밋 후까지 지연됩니다.

자세한 내용은 쿼리 성능을 향상시키고 리소스 소비를 줄이기 위해 인덱스 기본 테넌트 최적화를 참조하세요.

인덱스 다시 구성

인덱스를 다시 구성할 때는 최소한의 시스템 리소스가 사용됩니다. 이때는 왼쪽에서 오른쪽으로 표시되는 리프 노드의 논리적 순서에 맞도록 리프 수준 페이지를 물리적으로 다시 정렬하여 테이블 및 뷰의 클러스터형 및 비클러스터형 인덱스의 리프 수준에 대한 조각 모음을 수행합니다. 다시 구성 작업을 수행하면 인덱스 페이지도 압축됩니다. 이때 압축은 기존 채우기 비율 값을 기준으로 수행됩니다.

ALL을 지정하면 테이블에서 관계형 인덱스, 클러스터형 및 비클러스터형 모두와 XML 인덱스가 다시 구성됩니다. 지정할 ALL때 몇 가지 제한 사항이 적용됩니다. 이 문서의 인수 섹션에 있는 정의를 ALL 참조하세요.

자세한 내용은 쿼리 성능을 향상시키고 리소스 소비를 줄이기 위해 인덱스 기본 테넌트 최적화를 참조하세요.

Important

정렬된 클러스터형 columnstore 인덱 ALTER INDEX REORGANIZE 스가 있는 Azure Synapse Analytics 테이블의 경우 데이터를 다시 정렬하지 않습니다. 데이터를 다시 정렬하려면 ALTER INDEX REBUILD를 사용하세요.

인덱스 사용 안 함

인덱스를 비활성화하면 사용자가 인덱스에 액세스할 수 없으며 클러스터형 인덱스의 경우 기본 테이블 데이터에도 액세스할 수 없습니다. 인덱스 정의는 시스템 카탈로그에 유지됩니다. 뷰의 비클러스터형 인덱스 또는 클러스터형 인덱스를 비활성화하면 인덱스 데이터가 물리적으로 삭제됩니다. 클러스터형 인덱스를 비활성화하면 데이터에 액세스할 수 없지만 인덱스가 삭제되거나 다시 작성될 때까지는 데이터가 B-트리에서 유지 관리되지 않는 상태로 남아 있습니다. 활성 또는 비활성 인덱스의 상태를 보려면 sys.indexes 카탈로그 뷰의 is_disabled 열을 쿼리합니다.

참고

SQL Server 설명서는 인덱스를 지칭할 때 B-트리라는 용어를 사용합니다. rowstore 인덱스에서 SQL Server는 B+ 트리를 구현합니다. 이는 columnstore 인덱스나 메모리 내 데이터 저장소에는 적용되지 않습니다. 자세한 내용은 SQL Server 및 Azure SQL 인덱스 아키텍처 및 디자인 가이드를 참조하세요.

트랜잭션 복제 게시의 테이블에서는 기본 키 열과 연결된 인덱스를 해제할 수 없습니다. 이러한 인덱스는 복제에 필요합니다. 인덱스를 해제하려면 먼저 게시에서 테이블을 삭제해야 합니다. 자세한 내용은 데이터 및 데이터베이스 개체 게시를 참조하세요.

ALTER INDEX REBUILD 문 또는 CREATE INDEX WITH DROP_EXISTING 문을 사용하여 인덱스를 사용하도록 설정합니다. 비활성화된 클러스터형 인덱스 다시 작성은 옵션을 ONLINE />로 설정하여 ON수행할 수 없습니다. 자세한 내용은 인덱스 및 제약 조건 사용 안 함을 참조 하세요.

옵션 설정

해당 인덱스 다시 작성 또는 재구성 없이 지정된 인덱스 옵션ALLOW_ROW_LOCKSALLOW_PAGE_LOCKS, , OPTIMIZE_FOR_SEQUENTIAL_KEYIGNORE_DUP_KEY및 STATISTICS_NORECOMPUTE 지정된 인덱스 옵션을 설정할 수 있습니다. 수정된 값은 인덱스에 바로 적용됩니다. 이러한 설정을 보려면 sys.indexes를 사용하세요. 자세한 내용은 인덱스 옵션 설정을 참조하세요.

행 및 페이지 잠금 옵션

ALLOW_ROW_LOCKS = ON이고 ALLOW_PAGE_LOCK = ON이면 인덱스에 액세스할 때 행 수준, 페이지 수준 및 테이블 수준 잠금이 허용됩니다. 데이터베이스 엔진은 적절한 잠금을 선택하고 행 또는 페이지 잠금에서 테이블 잠금으로 잠금을 에스컬레이션할 수 있습니다.

ALLOW_ROW_LOCKS = OFF이고 ALLOW_PAGE_LOCK = OFF이면 인덱스에 액세스할 때 테이블 수준 잠금만 허용됩니다.

행 또는 페이지 잠금 옵션을 설정할 때 지정된 경우 ALL 설정은 모든 인덱스에 적용됩니다. 기본 테이블이 힙인 경우 다음과 같은 방식으로 설정이 적용됩니다.

옵션	세부 정보
`ALLOW_ROW_LOCKS = ON or OFF`	힙 및 연결된 비클러스터형 인덱스
`ALLOW_PAGE_LOCKS = ON`	힙 및 연결된 비클러스터형 인덱스
`ALLOW_PAGE_LOCKS = OFF`	비클러스터형 인덱스 전체. 즉, 비클러스터형 인덱스에는 모든 페이지 잠금이 허용되지 않습니다. 힙에서는 페이지에 대한 공유(S), 업데이트(U), 배타(X) 잠금만 허용되지 않습니다. 데이터베이스 엔진에서는 내부에서 사용하기 위해 의도 페이지 잠금(IS, IU 또는 IX)을 획득할 수 있습니다.

온라인 인덱스 작업

인덱스를 다시 빌드하고 ONLINE 옵션을 설정하면 ON쿼리 및 데이터 수정에 기본 개체, 테이블 및 관련 인덱스를 사용할 수 있습니다. 단일 파티션에 있는 인덱스 부분을 온라인으로 다시 작성할 수도 있습니다. 배타적 테이블 잠금은 변경 프로세스 중에 짧은 시간 동안만 유지됩니다.

인덱스를 다시 구성하는 과정은 항상 온라인으로 수행됩니다. 이 프로세스는 잠금을 장기간 유지하지 않으므로 실행 중인 업데이트나 쿼리를 차단하지 않습니다.

다음 작업을 수행할 때만 동일한 테이블 또는 테이블 파티션에서 동시 온라인 인덱스 작업을 수행할 수 있습니다.

여러 개의 비클러스터형 인덱스 생성
동일한 테이블에서 여러 인덱스 다시 구성
동일한 테이블에서 겹치지 않는 인덱스를 다시 작성하는 동안 여러 인덱스 다시 구성

동시에 수행된 다른 온라인 인덱스 작업이 모두 실패합니다. 예를 들어, 동일한 테이블에서 두 개 이상의 인덱스를 다시 작성할 수 없습니다. 또는 동일한 테이블에서 기존 인덱스를 다시 작성하면서 새 인덱스를 생성할 수 없습니다.

자세한 내용은 온라인으로 인덱스 작업 수행을 참조하세요.

다시 시작 가능한 인덱스 작업

적용 대상: SQL Server 2017(14.x) 이상 버전 및 Azure SQL Database

온라인 인덱스 다시 작성은 RESUMABLE = ON 옵션을 사용하여 resumable로 지정됩니다.

RESUMABLE 옵션은 지정된 인덱스에 대해 메타데이터에서 지속되며 현재 DDL 문의 기간에만 적용됩니다. 그러므로 다시 시작이 가능하도록 하려면 RESUMABLE = ON 절을 명시적으로 지정해야 합니다.
MAX_DURATION 옵션은 RESUMABLE = ON 옵션 또는 low_priority_lock_wait 옵션에서 지원됩니다.
- MAX_DURATION for RESUMABLE 옵션은 다시 작성 중인 인덱스에 대한 시간 간격을 지정합니다. 이 시간이 사용되면 인덱스 다시 작성이 일시 중지되거나 실행이 완료됩니다. 사용자는 일시 중지된 인덱스에 대한 다시 작성이 다시 시작될 수 있는 시기를 결정합니다. MAX_DURATION의 분 단위 time은 0분보다 크거나 1주일(7 * 24 * 60 = 10,080분) 이하여야 합니다. 인덱스 작업을 오랫동안 일시 중지하면 데이터베이스 디스크 용량뿐만 아니라 특정 테이블의 DML 성능에 영향을 줄 수 있습니다. 두 인덱스(원래 인덱스 및 새로 만든 인덱스)는 디스크 공간이 필요하고 DML 작업 중에 업데이트해야 하기 때문일 수 있습니다. 옵션을 생략하면 MAX_DURATION 인덱스 작업이 완료될 때까지 또는 오류가 발생할 때까지 계속됩니다.
- low_priority_lock_wait 인수 옵션을 사용하면 인덱스 작업이 Sch-M 잠금에 대해 차단될 경우 계속할 수 있는 방법을 결정할 수 있습니다.
동일한 매개 변수를 지정하여 원래 ALTER INDEX REBUILD 문을 다시 실행하면 일시 중지된 인덱스 다시 빌드 작업이 다시 시작됩니다. ALTER INDEX RESUME 문을 실행하여 일시 중지된 인덱스 다시 빌드 작업을 다시 시작할 수도 있습니다.
다시 시작 가능한 인덱스의 경우 SORT_IN_TEMPDB = ON 옵션이 지원되지 않습니다.
RESUMABLE = ON 상태의 DDL 명령은 명시적 트랜잭션 내에서 실행할 수 없습니다(BEGIN TRAN ... COMMIT 블록에 포함될 수 없음).
일시 중지된 인덱스 작업만이 다시 시작될 수 있습니다.
일시 중지된 인덱스 작업을 다시 시작하면 값을 새 값으로 변경할 MAXDOP 수 있습니다. 일시 중지된 인덱스 작업을 다시 시작하는 경우 지정되지 않은 경우 MAXDOP 마지막 MAXDOP 값이 수행됩니다. 인덱스 다시 MAXDOP 작성 작업에 대한 옵션이 전혀 지정되지 않은 경우 기본값이 사용됩니다.
인덱스 작업을 즉시 일시 중지하려면 진행 중인 명령을 중지하거나(Ctrl-C) ALTER INDEX PAUSE 명령이나 KILL <session_id> 명령을 실행할 수 있습니다. 명령이 일시 중지되면 옵션을 사용하여 RESUME 다시 시작될 수 있습니다.
ABORT 명령은 원래 인덱스 다시 빌드를 호스트한 세션을 종료하고 인덱스 작업을 중단합니다.
다음을 제외하고 다시 시작 가능한 인덱스 다시 작성을 위해 필요한 추가 리소스는 없습니다.
- 인덱스가 일시 중지되는 시간을 포함하여 인덱스가 빌드되는 데 필요한 추가 공간
- DDL 수정을 하지 못하게 하는 DDL 상태
고스트 클린up은 인덱스 일시 중지 단계 중에 실행되지만 인덱스 실행 중에 일시 중지됩니다. 다음 기능은 다시 시작 가능한 인덱스 다시 작성 작업에 대해 비활성화됩니다.
- RESUMABLE = ON 상태에서는 비활성 인덱스를 다시 빌드할 수 없습니다.
- ALTER INDEX REBUILD ALL 명령
- 인덱스 다시 빌드를 사용한 ALTER TABLE
- RESUMABLE = ON 상태의 DDL 명령은 명시적 트랜잭션 내에서 실행할 수 없습니다(BEGIN TRAN ... COMMIT 블록에 포함될 수 없음).
- 계산된 인덱스 또는 TIMESTAMP 열을 키 열로 다시 작성합니다.
기본 테이블에 LOB 열이 포함된 경우 클러스터형 인덱스를 다시 작성하려면 이 작업 시작 시 Sch-M 잠금이 필요합니다.

참고 항목

DDL 명령은 완료, 일시 중지 또는 실패할 때까지 실행됩니다. 명령이 일시 중지되면 작업이 일시 중지되고 인덱스 만들기가 완료되지 않았음을 나타내는 오류가 발생합니다. 현재 인덱스 상태에 대한 더 자세한 내용은 sys.index_resumable_operations에서 가져올 수 있습니다. 이전과 마찬가지로 실패의 경우 오류도 발생합니다.

온라인 인덱스 작업에 대한 WAIT_AT_LOW_PRIORITY

적용 대상: SQL Server 2014(12.x) 이상 버전 및 Azure SQL Database

low_priority_lock_wait 구문을 사용하여 WAIT_AT_LOW_PRIORITY 동작을 지정할 수 있습니다. WAIT_AT_LOW_PRIORITY는 ONLINE = ON에서만 사용할 수 있습니다.

온라인 인덱스 다시 작성을 위해 DDL 문을 실행하려면 특정 테이블에서 실행 중인 모든 활성 차단 트랜잭션이 완료되어야 합니다. 온라인 인덱스 다시 작성이 실행되면 이 테이블에서 실행을 시작할 준비가 되어 있는 모든 새로운 트랜잭션이 차단됩니다. 온라인 인덱스 다시 작성에 대한 잠금 기간은 짧지만 지정된 테이블의 열려 있는 모든 트랜잭션이 완료될 때까지 대기하고 새 트랜잭션이 시작되지 않도록 차단하면 처리량에 상당한 영향을 주어 워크로드가 느려지거나 시간 제한이 발생하고 기본 테이블에 대한 액세스가 크게 제한될 수 있습니다.

DBA는 WAIT_AT_LOW_PRIORITY 옵션을 사용하여 온라인 인덱스 다시 빌드에 필요한 Sch-S(스키마 안정성) 및 Sch-M(스키마 수정) 잠금을 관리하고 두 가지 옵션 중 하나를 선택할 수 있습니다. 두 경우 모두, 대기 시간(MAX_DURATION = n [minutes]) 중에 차단 활동이 없으면 대기 없이 온라인 인덱스 다시 빌드가 즉시 실행되고 DDL 문이 완료됩니다.

WAIT_AT_LOW_PRIORITY 는 온라인 인덱스 다시 작성 작업이 낮은 우선 순위 잠금을 대기하므로 온라인 인덱스 빌드 작업이 대기하는 동안 다른 작업이 계속 진행되도록 합니다. WAIT AT LOW PRIORITY 옵션을 생략하면 WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE)와 동일합니다.

MAX_DURATION = time [ MINUTES ]

DDL 명령을 실행할 때 온라인 인덱스 다시 작성 잠금이 낮은 우선 순위로 대기하는 대기 시간(분 단위로 지정된 정수 값)입니다. 작업이 시간 동안 MAX_DURATION 차단되면 지정된 ABORT_AFTER_WAIT 작업이 실행됩니다. MAX_DURATION 시간은 항상 분 단위이며 단어를 MINUTES 생략할 수 있습니다.

ABORT_AFTER_WAIT = [ NONE | SELF | BLOCKERS ]

NONE

보통(일반) 우선 순위로 잠금을 계속 대기합니다.
SELF

어떤 동작도 수행하지 않고 현재 실행 중인 온라인 인덱스 다시 작성 DDL 작업을 종료합니다. 이 옵션은 SELF 0과 MAX_DURATION 함께 사용할 수 없습니다.
BLOCKERS

작업을 계속할 수 있도록 온라인 인덱스 다시 작성 DDL 작업을 차단하는 모든 사용자 트랜잭션을 종료합니다. BLOCKERS 옵션을 사용하려면 로그인에 ALTER ANY CONNECTION 권한이 있어야 합니다.

공간 인덱스 제한 사항

공간 인덱스를 다시 작성할 때는 공간 인덱스에 스키마 잠금이 유지되기 때문에 인덱스 작업 중에 기본 사용자 테이블을 사용할 수 없습니다.

사용자 테이블의 제약 조건은 PRIMARY KEY 해당 테이블의 열에 공간 인덱스가 정의되어 있는 동안 수정할 수 없습니다. 제약 조건을 PRIMARY KEY 변경하려면 먼저 테이블의 모든 공간 인덱스를 삭제합니다. 제약 조건을 수정한 PRIMARY KEY 후 각 공간 인덱스를 다시 만들 수 있습니다.

단일 파티션 다시 작성 작업에서는 공간 인덱스를 지정할 수 없습니다. 하지만 전체 파티션을 다시 작성할 때는 공간 인덱스를 지정할 수 있습니다.

공간 인덱스에 지정된 옵션(예: BOUNDING_BOX 또는 GRID)을 변경하려면 DROP_EXISTING = ON을 지정하는 CREATE SPATIAL INDEX 문을 사용하거나 해당 공간 인덱스를 삭제하고 새로 만들 수 있습니다. 예제는 CREATE SPATIAL INDEX를 참조하세요.

데이터 압축

데이터 압축에 대한 자세한 내용은 데이터 압축을 참조 하세요.

변경 PAGE 및 ROW 압축이 테이블, 인덱스 또는 파티션에 미치는 영향을 평가하려면 sp_estimate_data_compression_savings 저장 프로시저를 사용합니다.

다음은 분할된 인덱스에 적용되는 제한 사항입니다.

ALTER INDEX ALL ...을 사용하는 경우 테이블에 정렬되지 않은 인덱스가 있으면 단일 파티션의 압축 설정을 변경할 수 없습니다.
ALTER INDEX <index> ... REBUILD PARTITION ... 구문은 인덱스의 지정된 파티션을 다시 빌드합니다.
ALTER INDEX <index> ... REBUILD WITH ... 구문은 인덱스의 모든 파티션을 다시 빌드합니다.

통계

테이블에서 ALTER INDEX ALL ...을 실행하면 인덱스와 연결된 통계만 업데이트됩니다. 인덱스 대신 테이블에 대해 만들어진 자동 또는 수동 통계는 업데이트되지 않습니다.

사용 권한

ALTER INDEX를 실행하려면 최소한 테이블이나 뷰에 대한 ALTER 권한이 필요합니다.

버전 참고 사항

SQL Database는 파일 그룹 및 파일 스트림 옵션을 사용하지 않습니다.
Columnstore 인덱스는 SQL Server 2012(11.x) 이전에는 사용할 수 없습니다.
다시 시작 가능한 인덱스 작업은 SQL Server 2017(14.x) 및 Azure SQL Database부터 사용할 수 있습니다.

기본 구문 예제

ALTER INDEX index1 ON table1 REBUILD;
ALTER INDEX ALL ON table1 REBUILD;
ALTER INDEX ALL ON dbo.table1 REBUILD;

예: Columnstore 인덱스

이 예제는 columnstore 인덱스에는 적용되지 않습니다.

A. REORGANIZE 데모

이 예제에서는 ALTER INDEX REORGANIZE 명령의 작동 방식을 보여 줍니다. 여러 개의 행 그룹이 있는 테이블을 만든 다음, REORGANIZE가 행 그룹을 병합하는 방법을 보여 줍니다.

-- Create a database
CREATE DATABASE [columnstore];
GO

-- Create a rowstore staging table
CREATE TABLE [staging] (
    AccountKey INT NOT NULL,
    AccountDescription NVARCHAR(50),
    AccountType NVARCHAR(50),
    AccountCodeAlternateKey INT
);

-- Insert 10 million rows into the staging table.
DECLARE @loop INT;
DECLARE @AccountDescription VARCHAR(50);
DECLARE @AccountKey INT;
DECLARE @AccountType VARCHAR(50);
DECLARE @AccountCode INT;

SELECT @loop = 0

BEGIN TRANSACTION

WHILE (@loop < 300000)
BEGIN
    SELECT @AccountKey = CAST(RAND() * 10000000 AS INT);
    SELECT @AccountDescription = 'accountdesc ' + CONVERT(VARCHAR(20), @AccountKey);
    SELECT @AccountType = 'AccountType ' + CONVERT(VARCHAR(20), @AccountKey);
    SELECT @AccountCode = CAST(RAND() * 10000000 AS INT);

    INSERT INTO staging
    VALUES (
        @AccountKey,
        @AccountDescription,
        @AccountType,
        @AccountCode
     );

    SELECT @loop = @loop + 1;
END

COMMIT

-- Create a table for the clustered columnstore index
CREATE TABLE cci_target (
    AccountKey INT NOT NULL,
    AccountDescription NVARCHAR(50),
    AccountType NVARCHAR(50),
    AccountCodeAlternateKey INT
);

-- Convert the table to a clustered columnstore index named inxcci_cci_target;
CREATE CLUSTERED COLUMNSTORE INDEX idxcci_cci_target ON cci_target;

TABLOCK 옵션을 사용하여 행을 병렬로 삽입합니다. SQL Server 2016(13.x) INSERT INTO 부터 작업을 사용할 때 TABLOCK 병렬로 실행할 수 있습니다.

INSERT INTO cci_target WITH (TABLOCK)
SELECT TOP 300000 * FROM staging;

이 명령을 실행하여 델타 행 그룹을 확인 OPEN 합니다. rowgroup 수는 병렬 처리 수준에 따라 달라집니다.

SELECT *
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id  = object_id('cci_target');

이 명령을 실행하여 모든 CLOSEDOPEN 행 그룹과 행 그룹을 columnstore로 강제 적용합니다.

ALTER INDEX idxcci_cci_target ON cci_target REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

이 명령을 다시 실행하면 작은 행 그룹이 하나의 압축된 행 그룹으로 병합되는 것을 볼 수 있습니다.

ALTER INDEX idxcci_cci_target ON cci_target REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

B. 닫힌(CLOSED) 델타 rowgroup을 columnstore으로 압축

이 예제에서는 각 CLOSED 델타 행 그룹을 압축된 행 그룹으로 columnstore로 압축하는 옵션을 사용합니다REORGANIZE. 이는 필요하지 않지만 튜플 이동기가 행 그룹을 충분히 빠르게 압축 CLOSED 하지 않는 경우에 유용합니다.

AdventureWorksDW2022 샘플 데이터베이스에서 두 예제를 모두 실행할 수 있습니다.

이 샘플은 모든 파티션에서 실행됩니다 REORGANIZE .

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE;

이 샘플은 특정 파티션에서 실행됩니다 REORGANIZE .

-- REORGANIZE a specific partition
ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE PARTITION = 0;

C. 모든 열린(OPEN) 및 닫힌(CLOSED) 델타 rowgroup을 columnstore으로 압축

적용 대상: SQL Server (SQL Server 2016(13.x)부터 시작) 및 Azure SQL Database

이 명령은 REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON) 각 OPEN 행 그룹과 CLOSED 델타 행 그룹을 columnstore로 압축된 행 그룹으로 압축합니다. 이렇게 하면 deltastore가 비워지고 모든 행이 columnstore로 압축되도록 강제 적용합니다. 이러한 작업은 행을 하나 이상의 델타 행 그룹에 저장하므로 이 기능은 많은 삽입 작업을 수행한 후 특히 유용합니다.

REORGANIZE 행 그룹을 결합하여 행 그룹을 최대 행 <수 = 1,024,576개까지 채웁니다. 따라서 모든 OPEN 행 그룹과 CLOSED 행 그룹을 압축할 때는 몇 개의 행만 있는 압축된 행 그룹이 많지 않습니다. rowgroup을 채우면서도 압축된 크기를 가능하면 줄이고 쿼리 성능을 개선하기를 원할 것입니다.

다음 예에서는 AdventureWorksDW2022 데이터베이스를 사용합니다.

다음은 모든 OPENCLOSED 행 그룹과 델타 행 그룹을 columnstore 인덱스로 이동하는 예제입니다.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

다음은 모든 OPENCLOSED 행 그룹과 델타 행 그룹을 특정 파티션의 columnstore 인덱스로 이동하는 예제입니다.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE PARTITION = 0 WITH (COMPRESS_ALL_ROW_GROUPS = ON);

D. 온라인에서 columnstore 인덱스를 조각 모음

SQL Server 2012(11.x) 및 SQL Server 2014(12.x)에는 적용되지 않습니다.

SQL Server 2016(13.x) REORGANIZE 부터 델타 행 그룹을 columnstore로 압축하는 것 이상을 수행합니다. 또한 온라인 조각 모음도 수행합니다. 먼저, rowgroup 행 수의 10% 이상이 삭제된 경우 삭제된 행을 물리적으로 제거하여 columnstore의 크기를 줄입니다. 그런 다음, rowgroup들을 함께 결합하여 rowgroup당 최대 1,024,576개의 행을 포함하는 더 큰 rowgroup을 형성합니다. 변경된 모든 행 그룹은 다시 압축됩니다.

참고 항목

SQL Server 2016(13.x)부터는 삭제된 행을 물리적으로 제거하고 행 그룹을 병합하기 때문에 REORGANIZE columnstore 인덱스를 다시 작성할 필요가 없습니다. 이 COMPRESS_ALL_ROW_GROUPS 옵션은 이전에 다시 빌드로만 수행할 수 있었던 columnstore로 모든 OPEN 또는 CLOSED 델타 행 그룹을 강제합니다. REORGANIZE 는 온라인 상태이며 백그라운드에서 발생하므로 작업이 수행되면 쿼리를 계속할 수 있습니다.

다음 예제에서는 테이블에서 논리적으로 REORGANIZE 삭제된 행을 물리적으로 제거하고 행 그룹을 병합하여 인덱스를 조각 모음하는 방법을 수행합니다.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE;

E. 오프라인으로 클러스터형 columnstore 인덱스 다시 작성

적용 대상: SQL Server (SQL Server 2012(11.x)부터 시작)

팁

SQL Server 2016(13.x) 부터 및 Azure SQL Database에서는 columnstore 인덱스에 ALTER INDEX REBUILD 대신 ALTER INDEX REORGANIZE를 사용하는 것이 좋습니다.

참고 항목

SQL Server 2012(11.x) 및 SQL Server 2014(12.x) REORGANIZE 에서는 행 그룹을 columnstore로 압축 CLOSED 하는 데만 사용됩니다. 조각 모음 작업을 수행하고 모든 델타 rowgroup을 columnstore으로 강제 적용하는 유일한 방법은 인덱스를 다시 작성하는 것뿐입니다.

이 예제에서는 클러스터형 columnstore 인덱스를 다시 작성하고 모든 델타 rowgroup을 columnstore으로 강제 적용하는 방법을 보여줍니다. 첫 단계에서는 클러스터형 columnstore 인덱스가 있는 AdventureWorksDW2022 데이터베이스의 FactInternetSales2 테이블을 준비하고 첫 번째 네 열에서 데이터를 삽입합니다.

CREATE TABLE dbo.FactInternetSales2 (
    ProductKey [int] NOT NULL,
    OrderDateKey [int] NOT NULL,
    DueDateKey [int] NOT NULL,
    ShipDateKey [int] NOT NULL);

CREATE CLUSTERED COLUMNSTORE INDEX cci_FactInternetSales2
ON dbo.FactInternetSales2;

INSERT INTO dbo.FactInternetSales2
SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey
FROM dbo.FactInternetSales;

SELECT * FROM sys.column_store_row_groups;

결과에는 하나의 OPEN 행 그룹이 표시됩니다. 즉, SQL Server는 행 그룹을 닫고 데이터를 columnstore로 이동하기 전에 더 많은 행이 추가되기를 기다립니다. 다음 명령문은 모든 행을 columnstore로 강제 적용하는 클러스터형 columnstore 인덱스를 다시 작성합니다.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REBUILD;
SELECT * FROM sys.column_store_row_groups;

명령문의 SELECT 결과는 rowgroup이 COMPRESSED있음을 보여 줍니다. 즉, 행 그룹의 열 세그먼트가 이제 columnstore에 압축되고 저장됩니다.

F. 오프라인으로 클러스터형 columnstore의 파티션 다시 작성

적용 대상: SQL Server 2012(11.x) 이상 버전

대규모 클러스터형 columnstore 인덱스의 파티션을 다시 빌드하려면 파티션 옵션을 지정하고 ALTER INDEX REBUILD를 사용합니다. 이 예제에서는 파티션 12를 다시 작성합니다. SQL Server 2016(13.x)부터 REBUILD를 REORGANIZE로 대체하는 것이 좋습니다.

ALTER INDEX cci_fact3
ON fact3
REBUILD PARTITION = 12;

G. 보관 압축을 사용하도록 클러스터형 columnstore 인덱스 변경

적용 안 됨: SQL Server 2012(11.x)

데이터 압축 옵션을 사용하여 COLUMNSTORE_ARCHIVE 클러스터형 columnstore 인덱스의 크기를 더욱 줄일 수 있습니다. 이 방법은 저렴한 스토리지에 보관하려는 오래된 데이터에 실용적입니다. 압축 해제가 일반 COLUMNSTORE 압축보다 느리기 때문에 자주 액세스하지 않는 데이터에만 이 값을 사용하는 것이 좋습니다.

다음 예에서는 보관 압축을 사용하기 위해 클러스터형 columnstore 인덱스를 다시 작성한 다음 보관 압축을 제거하는 방법을 보여 줍니다. 최종 결과는 columnstore 압축만 사용합니다.

먼저, 클러스터형 columnstore 인덱스가 있는 테이블을 만들어 예제를 준비합니다. 그런 다음, 보관 압축을 사용하여 테이블을 추가로 압축합니다.

--Prepare the example by creating a table with a clustered columnstore index.
CREATE TABLE SimpleTable (
    ProductKey [int] NOT NULL,
    OrderDateKey [int] NOT NULL,
    DueDateKey [int] NOT NULL,
    ShipDateKey [int] NOT NULL
);

CREATE CLUSTERED INDEX cci_SimpleTable ON SimpleTable (ProductKey);

CREATE CLUSTERED COLUMNSTORE INDEX cci_SimpleTable
ON SimpleTable
WITH (DROP_EXISTING = ON);

--Compress the table further by using archival compression.
ALTER INDEX cci_SimpleTable ON SimpleTable
REBUILD
WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);
GO

이 샘플은 보관 압축을 제거하고 columnstore 압축만 사용합니다.

ALTER INDEX cci_SimpleTable ON SimpleTable
REBUILD
WITH (DATA_COMPRESSION = COLUMNSTORE);
GO

예제: Rowstore 인덱스

A. 인덱스 다시 작성

다음 예에서는 AdventureWorks2022 데이터베이스에 있는 Employee 테이블의 단일 인덱스를 다시 작성합니다.

ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee REBUILD;

B. 테이블의 모든 인덱스 다시 작성 및 옵션 지정

다음 예제에서는 키워드(keyword) ALL지정합니다. 이 키워드는 AdventureWorks2022 데이터베이스의 Production.Product 테이블에 연결된 인덱스를 모두 다시 작성합니다. 3개의 옵션이 지정됩니다.

ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);

다음 예에서는 낮은 우선 순위 잠금 옵션을 포함하여 ONLINE 옵션을 추가하고 행 압축 옵션을 추가합니다.

적용 대상: SQL Server 2014(12.x) 이상 버전 및 Azure SQL Database

ALTER INDEX ALL ON Production.Product
REBUILD WITH
(
    FILLFACTOR = 80,
    SORT_IN_TEMPDB = ON,
    STATISTICS_NORECOMPUTE = ON,
    ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 4 MINUTES, ABORT_AFTER_WAIT = BLOCKERS ) ),
    DATA_COMPRESSION = ROW
);

C. 인덱스 다시 구성과 LOB 압축

다음 예에서는 AdventureWorks2022 데이터베이스의 단일 클러스터형 인덱스를 다시 구성합니다. 인덱스에 리프 수준의 LOB 데이터 형식이 포함되어 있으므로 해당 문은 큰 개체 데이터가 포함된 페이지도 모두 압축합니다. 기본값이 ON이므로 WITH (LOB_COMPACTION = ON) 옵션은 지정하지 않아도 됩니다.

ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto REORGANIZE WITH (LOB_COMPACTION = ON);

D. 인덱스에서 옵션 설정

다음 예에서는 AK_SalesOrderHeader_SalesOrderNumber 데이터베이스의 AdventureWorks2022 인덱스에 몇 가지 옵션을 설정합니다.

ALTER INDEX AK_SalesOrderHeader_SalesOrderNumber ON
    Sales.SalesOrderHeader
SET (
    STATISTICS_NORECOMPUTE = ON,
    IGNORE_DUP_KEY = ON,
    ALLOW_PAGE_LOCKS = ON
    ) ;
GO

E. 인덱스 비활성화

다음 예에서는 Employee 데이터베이스에 있는 AdventureWorks2022 테이블의 비클러스터형 인덱스를 비활성화합니다.

ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee DISABLE;

F. 제약 조건 사용 안 함

다음 예제에서는 데이터베이스에서 PRIMARY KEY 인덱 AdventureWorks2022 스를 사용하지 않도록 설정 PRIMARY KEY 하여 제약 조건을 사용하지 않도록 설정합니다. FOREIGN KEY 기본 테이블에 대한 제약 조건이 자동으로 비활성화되고 경고 메시지가 표시됩니다.

ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department DISABLE;

결과 집합에서 다음과 같은 경고 메시지를 반환합니다.

Warning: Foreign key 'FK_EmployeeDepartmentHistory_Department_DepartmentID'
on table 'EmployeeDepartmentHistory' referencing table 'Department'
was disabled as a result of disabling the index 'PK_Department_DepartmentID'.

G. 제약 조건 사용

다음 예제에서는 예제 F에서 사용하지 않도록 설정된 제약 조건 및 FOREIGN KEY 제약 조건을 사용하도록 설정합니다PRIMARY KEY.

PRIMARY KEY 인덱스 다시 작성을 통해 제약 조건을 사용할 수 PRIMARY KEY 있습니다.

ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department REBUILD;

FOREIGN KEY 그런 다음 제약 조건이 활성화됩니다.

ALTER TABLE HumanResources.EmployeeDepartmentHistory
CHECK CONSTRAINT FK_EmployeeDepartmentHistory_Department_DepartmentID;
GO

H. 분할된 인덱스 다시 작성

다음 예에서는 AdventureWorks2022 데이터베이스에 있는 분할된 인덱스 5의 단일 파티션인 파티션 번호 IX_TransactionHistory_TransactionDate를 다시 작성합니다. 파티션 5가 ONLINE=ON으로 다시 작성되고 낮은 우선 순위 잠금에 대한 10분 대기 시간이 인덱스 다시 작성 작업으로 획득된 모든 잠금에 개별적으로 적용됩니다. 이 시간 동안에 인덱스 다시 작성을 완료하기 위해 잠금을 얻을 수 없으면 ABORT_AFTER_WAIT = SELF 때문에 다시 작성 작업 문 자체가 중단됩니다.

적용 대상: SQL Server 2014(12.x) 이상 버전 및 Azure SQL Database

-- Verify the partitioned indexes.
SELECT *
FROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID(N'Production.TransactionHistory'), NULL , NULL, NULL);
GO
--Rebuild only partition 5.
ALTER INDEX IX_TransactionHistory_TransactionDate
ON Production.TransactionHistory
REBUILD Partition = 5
   WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10 minutes, ABORT_AFTER_WAIT = SELF)));
GO

9\. 인덱스의 압축 설정 변경

다음 예에서는 분할되지 않은 rowstore 테이블에 인덱스를 다시 작성합니다.

ALTER INDEX IX_INDEX1
ON T1
REBUILD
WITH (DATA_COMPRESSION = PAGE);
GO

J. XML 압축을 사용하여 인덱스의 설정을 변경

적용 대상: SQL Server 2022(16.x) 이상 버전, Azure SQL Database 및 Azure SQL Managed Instance.

다음 예에서는 분할되지 않은 rowstore 테이블에 인덱스를 다시 작성합니다.

ALTER INDEX IX_INDEX1
ON T1
REBUILD
WITH (XML_COMPRESSION = ON);
GO

더 많은 데이터 압축 예제는 데이터 압축을 참조 하세요.

11. 온라인으로 다시 시작 가능한 인덱스 다시 작성

적용 대상: SQL Server 2017(14.x) 이상 버전 및 Azure SQL Database

다음 예제에서는 온라인 다시 시작 가능한 인덱스 다시 작성을 사용하는 방법을 보여줍니다.

를 사용하여 온라인 인덱스 다시 작성을 다시 시작 가능한 작업으로 MAXDOP = 1실행합니다. 인덱스 작업이 일시 중지된 후 동일한 명령을 다시 실행하면 인덱스 다시 작성 작업이 자동으로 다시 시작됩니다.

ALTER INDEX test_idx on test_table REBUILD WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);

240분으로 설정된 온라인 인덱스 다시 작성을 다시 시작 가능한 작업으로 MAX_DURATION 실행합니다.

ALTER INDEX test_idx on test_table REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);

실행 중인 다시 시작 가능한 온라인 인덱스 다시 작성을 일시 중지합니다.

ALTER INDEX test_idx on test_table PAUSE;

4로 설정된 새 값을 지정하는 다시 시작 가능한 작업으로 실행된 인덱스 다시 작성을 위해 온라인 인덱스 MAXDOP 다시 작성을 다시 시작합니다.

ALTER INDEX test_idx on test_table RESUME WITH (MAXDOP = 4);

다시 시작 가능한 것으로 실행된 인덱스 온라인 다시 작성에 대해 온라인 인덱스 다시 작성 작업을 다시 시작합니다. 2로 설정하고 MAXDOP 다시 시작하는 인덱스의 실행 시간을 240분으로 설정하고, 잠금에서 인덱스가 차단되는 경우 10분 동안 기다린 후 모든 차단기를 종료합니다.

ALTER INDEX test_idx on test_table
    RESUME WITH (MAXDOP = 2, MAX_DURATION = 240 MINUTES,
    WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10, ABORT_AFTER_WAIT = BLOCKERS));

실행 중이거나 일시 중지된 다시 시작 가능한 인덱스 다시 작성 작업을 중단합니다.

ALTER INDEX test_idx on test_table ABORT;

Share via

ALTER INDEX(Transact-SQL)

구문

인수

index_name

ALL

database_name

schema_name

table_or_view_name

REBUILD [ WITH ( <rebuild_index_option> [ ,... n ] ) ]

PARTITION

DISABLE

rowstore 인덱스 재구성(REORGANIZE)

REORGANIZE WITH ( LOB_COMPACTION = { ON | OFF } )

columnstore 인덱스 재구성(REORGANIZE)

REORGANIZE WITH ( COMPRESS_ALL_ROW_GROUPS = { ON | OFF } )

SET ( <set_index 옵션> [ ,... n ] )

PAD_INDEX = { ON | OFF }

FILLFACTOR = fillfactor

SORT_IN_TEMPDB = { ON | OFF }

IGNORE_DUP_KEY = { ON | OFF }

STATISTICS_NORECOMPUTE = { ON | OFF }

STATISTICS_INCREMENTAL = {ON | OFF}

ONLINE = { ON | OFF } <rebuild_index_option에 적용>

RESUMABLE = { ON | OFF}

MAX_DURATION = time [ MINUTES ] 와 함께 RESUMABLE = ON 사용됨(필수 ONLINE = ON)

ALLOW_ROW_LOCKS = { ON | OFF }

ALLOW_PAGE_LOCKS = { ON | OFF }

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

MAXDOP = max_degree_of_parallelism

COMPRESSION_DELAY = { 0 | duration [ minutes ] }

DATA_COMPRESSION

XML_COMPRESSION

ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,... n ] )

ONLINE = { ON | OFF } <single_partition_rebuild_index_option에 적용>

RESUME

일시 중지

중단

설명

인덱스 다시 작성

인덱스 다시 구성

인덱스 사용 안 함

옵션 설정

행 및 페이지 잠금 옵션

온라인 인덱스 작업

다시 시작 가능한 인덱스 작업

온라인 인덱스 작업에 대한 WAIT_AT_LOW_PRIORITY

MAX_DURATION = time [ MINUTES ]

ABORT_AFTER_WAIT = [ NONE | SELF | BLOCKERS ]

공간 인덱스 제한 사항

데이터 압축

통계

사용 권한

버전 참고 사항

기본 구문 예제

예: Columnstore 인덱스

A. REORGANIZE 데모

B. 닫힌(CLOSED) 델타 rowgroup을 columnstore으로 압축

C. 모든 열린(OPEN) 및 닫힌(CLOSED) 델타 rowgroup을 columnstore으로 압축

D. 온라인에서 columnstore 인덱스를 조각 모음

E. 오프라인으로 클러스터형 columnstore 인덱스 다시 작성

F. 오프라인으로 클러스터형 columnstore의 파티션 다시 작성

G. 보관 압축을 사용하도록 클러스터형 columnstore 인덱스 변경

예제: Rowstore 인덱스

A. 인덱스 다시 작성

B. 테이블의 모든 인덱스 다시 작성 및 옵션 지정

C. 인덱스 다시 구성과 LOB 압축

D. 인덱스에서 옵션 설정

E. 인덱스 비활성화

F. 제약 조건 사용 안 함

G. 제약 조건 사용

H. 분할된 인덱스 다시 작성

9\. 인덱스의 압축 설정 변경

J. XML 압축을 사용하여 인덱스의 설정을 변경

11. 온라인으로 다시 시작 가능한 인덱스 다시 작성

관련 콘텐츠

피드백

피드백

추가 리소스

MAX_DURATION = time [ MINUTES ] 와 함께 `RESUMABLE = ON` 사용됨(필수 `ONLINE = ON`)