온라인 인덱스 작업에 대한 지침
적용 대상:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceMicrosoft Fabric의 SQL 데이터베이스
온라인 인덱스 작업을 수행할 때 다음 지침이 적용됩니다.
- 기본 테이블에 image, ntext및 textLOB(Large Object) 데이터 형식이 포함된 경우 클러스터형 인덱스는 오프라인으로 생성, 다시 작성 또는 삭제해야 합니다.
- 테이블에 LOB 데이터 형식을 사용하는 열이 있지만 이러한 열 중 어느 것도 키 또는 포함된 열로 인덱스 정의에 사용되지 않는 경우 비클러스터형 인덱스를 온라인으로 만들 수 있습니다.
- 로컬 임시 테이블의 인덱스는 온라인에서 만들거나 다시 작성하거나 삭제할 수 없습니다. 이 제한은 전역 임시 테이블의 인덱스에는 적용되지 않습니다.
-
CREATE INDEX 의
RESUMABLE절을 사용하거나 ALTER INDEX을 사용하여 온라인 인덱스 작업을 다시 시작 가능한 작업으로 시작할 수 있습니다. 예기치 않은 오류, 데이터베이스 장애 조치(failover) 또는ALTER INDEX PAUSE명령 후에 다시 시작 가능한 인덱스 작업을 다시 시작하고 중단된 위치에서 계속할 수 있습니다.
참고
온라인 인덱스 작업은 일부 Microsoft SQL Server 버전에서 사용할 수 없습니다. SQL Server 버전에서 지원되는 기능 목록은 SQL Server 2022의 버전과 지원하는 기능을 참조하세요.
다음 표에서는 온라인으로 수행 가능한 인덱스 작업, 이러한 온라인 작업에서 제외된 인덱스, 다시 시작 가능한 인덱스 제한을 보여줍니다. 추가 제한 사항도 포함됩니다.
| 온라인 인덱스 작업 | 제외된 인덱스 | 기타 제한 사항 |
|---|---|---|
ALTER INDEX REBUILD |
비활성화된 클러스터형 인덱스 또는 비활성화된 인덱싱 뷰 XML 인덱스 로컬 임시 테이블의 인덱스 |
키워드를 ALL로 지정하면 테이블에 제외된 인덱스가 들어 있는 경우 작업이 실패할 수 있습니다.비활성 인덱스 다시 작성에 대한 추가 제한이 적용됩니다. 자세한 내용은 인덱스 및 제약 조건 비활성화를 참조하세요. |
CREATE INDEX |
XML 인덱스 뷰의 초기 고유 클러스터형 인덱스 로컬 임시 테이블의 인덱스 |
|
CREATE INDEX WITH DROP_EXISTING |
비활성화된 클러스터형 인덱스 또는 비활성화된 인덱싱 뷰 로컬 임시 테이블의 인덱스 XML 인덱스 |
|
DROP INDEX |
비활성 인덱스 XML 인덱스 비클러스터형 인덱스 로컬 임시 테이블의 인덱스 |
단일 문에 여러 인덱스를 지정할 수 없습니다. |
ALTER TABLE ADD CONSTRAINT(PRIMARY KEY 또는 UNIQUE) |
로컬 임시 테이블의 인덱스 클러스터형 인덱스 |
한 번에 하나의 하위 절만 허용됩니다. 예를 들어 동일한 PRIMARY KEY 문에 UNIQUE 또는 ALTER TABLE 제약 조건을 추가 및 삭제할 수 없습니다. |
ALTER TABLE DROP CONSTRAINT(PRIMARY KEY 또는 UNIQUE) |
클러스터형 인덱스 |
온라인 인덱스 작업이 진행되는 동안에는 기본 테이블을 수정, 잘라내기 또는 삭제할 수 없습니다.
클러스터형 인덱스를 만들거나 삭제할 때 지정한 온라인 옵션 설정(ON 또는 OFF)은 다시 작성해야 하는 비클러스터형 인덱스에 적용됩니다. 예를 들어 클러스터형 인덱스가 CREATE INDEX WITH DROP_EXISTING, ONLINE=ON사용하여 온라인으로 빌드되는 경우 연결된 모든 비클러스터형 인덱스도 온라인으로 다시 만들어집니다.
인덱스 작성기와 동시 사용자 트랜잭션에서 UNIQUE 인덱스를 온라인으로 만들거나 다시 작성할 때 동일한 키를 삽입하려고 시도하여 고유성을 위반할 수 있습니다. 원본 테이블의 원래 행을 새 인덱스로 이동하기 전에 사용자가 입력한 행을 새 인덱스(대상)에 삽입하면 온라인 인덱스 작업이 실패합니다.
일반적이지는 않지만 온라인 인덱스 작업으로 인해 사용자 또는 애플리케이션 활동으로 인해 데이터베이스 업데이트와 상호 작용할 때 교착 상태가 발생할 수 있습니다. 이러한 드문 경우에서 사용자 또는 애플리케이션 활동이 교착 상태의 희생자로 선택됩니다.
여러 개의 새로운 비클러스터형 인덱스를 만들거나 비클러스터형 인덱스를 다시 구성할 때만 동일한 테이블 또는 뷰에서 동시 온라인 인덱스 DDL 작업을 수행할 수 있습니다. 동시에 수행된 다른 온라인 인덱스 작업이 모두 실패합니다. 예를 들어 동일한 테이블에서 기존 인덱스를 온라인으로 다시 작성하면서 새 인덱스를 온라인으로 생성할 수 없습니다.
인덱스에 큰 개체 형식의 열이 포함되어 있고 온라인 인덱스 작업이 시작되기 전에 동일한 트랜잭션이 데이터를 수정하는 경우 온라인 작업을 수행할 수 없습니다. 이 문제를 해결하려면 온라인 인덱스 작업을 트랜잭션 외부로 이동하거나 동일한 트랜잭션에서 데이터를 수정하기 전에 이동합니다.
디스크 공간 고려 사항
온라인 인덱스 작업에는 오프라인 인덱스 작업보다 더 많은 디스크 공간이 필요합니다.
- 인덱스 만들기 및 인덱스 다시 빌드 작업 동안 빌드(또는 다시 빌드)할 인덱스에 대해 추가 공간이 필요합니다. 일반적으로 이 추가 공간은 인덱스가 차지하는 현재 공간과 동일하지만 현재 또는 다시 빌드된 인덱스에서 사용되는 압축에 따라 크거나 작을 수 있습니다.
- 또한 임시 매핑 인덱스에 대해서도 디스크 공간이 필요합니다. 이 임시 인덱스는 클러스터형 인덱스를 만들거나 다시 작성하거나 삭제하는 온라인 인덱스 작업에 사용됩니다.
- 온라인 상태에서 클러스터형 인덱스를 삭제하는 작업은 온라인 상태에서 클러스터형 인덱스를 만드는 작업(또는 다시 빌드)에 필요한 공간과 동일한 크기가 필요합니다.
자세한 내용은 인덱스 DDL 작업에 대한디스크 공간 요구 사항을 참조하세요.
성능 고려 사항
온라인 인덱스 작업은 동시 사용자 업데이트 작업을 허용하지만 업데이트 작업이 많은 경우 인덱스 작업이 더 오래 걸릴 수 있습니다. 일반적으로 온라인 인덱스 작업은 동시 업데이트 작업 수준과 상관없이 같은 양의 오프라인 인덱스 작업보다 느립니다.
원본 구조와 대상 구조는 모두 온라인 인덱스 작업 중에 유지 관리되므로 삽입, 업데이트 및 삭제 트랜잭션에 대한 리소스 사용량이 증가하여 잠재적으로 두 배가 될 수 있습니다. 이로 인해 인덱스 작업 중에 성능이 저하되고 리소스 사용량, 특히 CPU 시간이 늘어나게 될 수 있습니다. 온라인 인덱스 작업은 완전히 기록됩니다.
온라인 작업을 권장하지만 환경 및 특정 요구 사항을 평가해야 합니다. 인덱스 작업을 오프라인으로 실행하는 것이 최적일 수 있습니다. 이렇게 하면 작업 중에 데이터에 대한 사용자 액세스가 제한되지만 작업이 더 빠르게 완료되고 더 적은 리소스를 사용합니다.
SQL Server 2016(13.x) 이상 버전을 실행하는 다중 프로세서 컴퓨터에서 인덱스 작업은 병렬 처리를 사용하여 인덱스 문과 연결된 검사 및 정렬 작업을 수행할 수 있습니다.
MAXDOP 인덱스 옵션을 사용하여 온라인 인덱스 작업의 병렬 처리 수준을 제어할 수 있습니다. 이런 방법으로 인덱스 작업에 사용되는 리소스와 동시 사용자의 리소스 간에 균형을 유지할 수 있습니다. 자세한 내용은 병렬 인덱스 작업 구성을 참조하세요. 병렬 인덱스 작업을 지원하는 SQL Server 버전에 대한 자세한 내용은 SQL Server 2022의 버전 및 지원되는 기능을 참조하세요.
공유(S) 잠금 또는 스키마 수정(Sch-M) 잠금은 인덱스 작업의 마지막 단계에서 유지되므로 BEGIN TRANSACTION ... COMMIT 블록과 같은 명시적 사용자 트랜잭션 내에서 온라인 인덱스 작업을 실행할 때는 주의해야 합니다. 이렇게 하면 트랜잭션이 끝날 때까지 잠금이 유지되고 다른 워크로드가 차단될 수 있습니다.
ALLOW_PAGE_LOCKS=OFF사용하여 인덱스 페이지 잠금을 사용하지 않도록 설정하면, MAXDOP이 1보다 클 때 온라인 인덱스 재구성이 인덱스 조각화를 증가시킬 수 있습니다. 자세한 내용은 작동 방법: 온라인 인덱스 다시 빌드 - 조각이 늘어날 수 있음을 참조하세요.
트랜잭션 로그 고려 사항
오프라인 또는 온라인에서 수행되는 대규모 인덱스 작업은 대량의 트랜잭션 로그를 생성할 수 있습니다. 오프라인 및 온라인 인덱스 다시 작성 작업이 모두 완전히 기록되기 때문입니다. 인덱스 작업을 롤백할 수 있도록 인덱스 작업이 완료될 때까지는 트랜잭션 로그를 자를 수 없지만, 인덱스 작업 중에 로그를 백업할 수 있습니다.
따라서 트랜잭션 로그에는 인덱스 작업을 수행하는 동안 인덱스 작업 트랜잭션 및 동시 사용자 트랜잭션을 모두 저장할 수 있는 충분한 공간이 있어야 합니다. 자세한 내용은 인덱스 작업 트랜잭션 로그 디스크 공간을 참조하세요.
ADR(가속화된 데이터베이스 복구) 사용하도록 설정된 경우 온라인 인덱스 작업으로 인해 트랜잭션 로그가 증가하지 않습니다.
영구 버전 저장소 고려 사항
ADR을 사용하도록 설정하면 인덱스 작업이 진행되는 동안 큰 인덱스를 온라인으로 만들거나 다시 빌드하면 PVS(영구 버전 저장소)의 크기가 크게 증가할 수 있습니다. 데이터베이스에 PVS를 확장할 수 있는 충분한 여유 공간이 있는지 확인합니다. 자세한 내용은 모니터링 및 가속화된 데이터베이스 복구문제 해결을 참조하세요.
다시 시작 가능한 인덱스 고려 사항
CREATE INDEX 및 ALTER INDEX 용 RESUMABLE 인덱스 옵션은 SQL Server(SQL Server 2017(14.x)부터 시작하는ALTER INDEX 및 SQL Server 2019(15.x)로 시작하는 CREATE INDEX), Azure SQL Database 및 Azure SQL Managed Instance에 적용됩니다. 자세한 내용은 CREATE INDEX 및 ALTER INDEX를 참조하세요.
RESUMABLE 옵션을 사용하려면 ONLINE 옵션도 사용해야 합니다. 다시 시작하는 인덱스 만들기 또는 다시 작성을 수행하는 경우 다음 지침이 적용됩니다.
- 인덱스 유지 관리 기간을 관리, 계획 및 확장하는 것을 더 잘 제어할 수 있습니다. 유지 관리 기간에 맞게 인덱스 만들기 및 다시 작성 작업을 여러 번 일시 중지 및 다시 시작할 수 있습니다.
- 처음부터 인덱스 작업을 다시 시작하지 않고도 인덱스 만들기 또는 다시 빌드 오류(예: 데이터베이스 장애 조치(failover) 또는 디스크 공간 부족)에서 복구할 수 있습니다.
- 인덱스 작업이 일시 중지된 경우 원래 인덱스와 새로 만든 인덱스 모두 디스크 공간이 필요하며 DML 작업 중에 업데이트해야 합니다.
-
SORT_IN_TEMPDB=ON옵션은 지원되지 않습니다. - 비활성 인덱스는 지원되지 않습니다.
팁
다시 시작 가능한 인덱스 작업에는 큰 트랜잭션이 필요하지 않으므로 이 작업 중에 로그가 자주 잘리고 로그가 크게 증가하지 않습니다. 인덱스 작업을 다시 시작하고 완료하는 데 필요한 데이터는 데이터베이스의 데이터 파일에 저장됩니다.
일반적으로 다시 시작 가능한 온라인 인덱스 작업과 실행 불가능한 온라인 인덱스 작업 간에는 성능 차이가 없습니다. 다시 시작 가능한 CREATE INDEX경우 더 작은 테이블에 대한 작업이 눈에 띄게 느려질 수 있는 일정한 오버헤드가 있습니다.
다시 시작 가능한 인덱스 작업이 일시 중지된 경우:
- 대부분 읽기 워크로드의 경우 성능 저하는 중요하지 않습니다.
- 업데이트가 많은 워크로드의 경우 워크로드 세부 사항에 따라 약간의 처리량 저하가 발생할 수 있습니다.
일반적으로 일시 중지 가능한 온라인 인덱스 생성 또는 재구성과 일시 중지 불가능한 온라인 인덱스 생성 또는 재구성 사이에 조각 모음 품질의 차이는 없습니다.
참고
온라인 인덱스 작업이 일시 중지되는 동안 일시 중지된 인덱스를 포함하는 테이블에 대한 테이블 수준 배타적(X) 잠금이 필요한 트랜잭션은 실패합니다. 예를 들어 INSERT ... WITH (TABLOCK) 작업에서 발생할 수 있습니다. 이 경우 오류 10637이 발생합니다.
Cannot perform this operation on '<object name>' with ID (<object ID>) as one or more indexes are currently in resumable index rebuild state. Please refer to sys.index_resumable_operations for more details.
오류 10637을 해결하려면 트랜잭션에서 TABLOCK 힌트를 제거하거나 인덱스 작업을 일시 중지하고 트랜잭션을 다시 시도하기 전에 완료될 때까지 기다립니다.
온라인 기본 옵션
데이터베이스 수준에서 ELEVATE_ONLINE 또는 ELEVATE_RESUMABLE 데이터베이스 범위 구성을 설정하여 온라인 및 다시 시작 가능한 인덱스 작업을 기본 옵션으로 설정할 수 있습니다. 이러한 기본 옵션을 사용하면 테이블 또는 인덱스가 실행되는 동안 액세스할 수 없는 오프라인 인덱스 작업을 실수로 시작하지 않도록 할 수 있습니다. 두 옵션 모두 데이터베이스 엔진이 특정 인덱스 작업을 온라인 또는 다시 시작 가능한 실행으로 자동으로 승격합니다.
옵션을 FAIL_UNSUPPORTED, WHEN_SUPPORTED또는 OFF설정할 수 있습니다.
ELEVATE_ONLINE 및 ELEVATE_RESUMABLE대해 다른 값을 설정할 수 있습니다. 자세한 내용은 ALTER DATABASE SCOPED CONFIGURATION을 참고하십시오.
ELEVATE_ONLINE 및 ELEVATE_RESUMABLE은 online 및 resumable 구문을 각각 지원하는 DDL 문에만 적용됩니다. 예를 들어 ELEVATE_ONLINE=FAIL_UNSUPPORTED사용하여 XML 인덱스를 만들려고 하면 XML 인덱스가 ONLINE 옵션을 지원하지 않으므로 작업이 오프라인으로 실행됩니다. 옵션은 ONLINE 또는 RESUMABLE 옵션을 지정하지 않고 제출된 DDL 문에만 영향을 줍니다. 예를 들어 ONLINE=OFF 또는 RESUMABLE=OFF를 사용하여 문을 제출하면 사용자가 FAIL_UNSUPPORTED 설정을 재정의하고, 오프라인으로 또는 다시 시작 가능하지 않은 방식으로 문을 실행합니다.
참고
ELEVATE_ONLINE 및 ELEVATE_RESUMABLE은 XML 인덱스 작업에 적용되지 않습니다.