인덱스 다시 구성 및 다시 작성
업데이트: 2006년 7월 17일
SQL Server 2005 데이터베이스 엔진에서는 기본 데이터에 삽입, 업데이트 또는 삭제 작업을 수행할 때마다 인덱스를 자동으로 유지 관리합니다. 이러한 수정이 거듭되면 시간이 흐름에 따라 인덱스의 정보가 조각화되어 데이터베이스 내에 흩어지게 될 수 있습니다. 조각화는 키 값을 기준으로 하는 인덱스의 논리적 페이지 순서가 데이터 파일 내의 물리적 순서와 일치하지 않을 때 나타납니다. 심하게 조각화된 인덱스는 쿼리 성능을 저하시키고 응용 프로그램의 응답을 늦출 수 있습니다. 자세한 내용은 Microsoft 웹 사이트를 참조하십시오.
SQL Server 2005에서는 인덱스를 다시 구성하거나 다시 작성하여 인덱스 조각화 문제를 해결할 수 있습니다. 파티션 구성표에 작성한 분할된 인덱스의 경우 전체 인덱스나 인덱스의 단일 파티션에 이러한 방법 중 하나를 사용할 수 있습니다.
조각화 검색
사용할 조각 모음 방법을 결정하기 위한 첫 번째 단계는 인덱스를 분석하여 조각화 수준을 확인하는 것입니다. sys.dm_db_index_physical_stats 시스템 함수를 사용하여 특정 인덱스, 테이블이나 인덱싱된 뷰의 모든 인덱스, 데이터베이스의 모든 인덱스 또는 모든 데이터베이스 내 모든 인덱스에서 조각화를 검색할 수 있습니다. 분할된 인덱스의 경우 sys.dm_db_index_physical_stats에서도 각 파티션의 조각화 정보를 제공합니다.
조각화 계산 알고리즘은 SQL Server 2000보다 SQL Server 2005에서 더 정확합니다. 따라서 조각화 값이 더 높게 표시됩니다. 예를 들어 SQL Server 2000에서는 11페이지와 13페이지가 같은 익스텐트에 있지만 12페이지는 없을 경우 테이블이 조각화되지 않습니다. 그러나 이러한 두 페이지에 액세스하려면 두 가지 물리적 I/O 작업이 필요하므로 SQL Server 2005에서는 조각화로 계산됩니다.
sys.dm_db_index_physical_stats 함수에서 반환한 결과 집합은 다음 열을 포함합니다.
| 열 | 설명 |
|---|---|
avg_fragmentation_in_percent |
논리적 조각화(인덱스에서 순서가 잘못된 페이지) 비율 |
fragment_count |
인덱스의 조각(물리적으로 연속되는 리프 페이지) 수 |
avg_fragment_size_in_pages |
인덱스 한 조각의 평균 페이지 수 |
조각화 수준을 파악하고 나면 다음 테이블을 사용하여 가장 적합한 조각화 수정 방법을 결정합니다.
| avg_fragmentation_in_percent 값 | 수정문 |
|---|---|
> 5% 및 < = 30% |
ALTER INDEX REORGANIZE |
> 30% |
ALTER INDEX REBUILD WITH (ONLINE = ON)* |
* 온라인 또는 오프라인으로 인덱스를 다시 작성할 수 있습니다. 인덱스를 다시 구성하는 과정은 항상 온라인으로 실행됩니다. 다시 구성할 때와 비슷한 가용성을 얻으려면 온라인으로 인덱스를 다시 작성해야 합니다.
이러한 값은 ALTER INDEX REORGANIZE와 ALTER INDEX REBUILD를 전환해야 하는 시점을 확인하기 위한 대략적인 지침을 제공합니다. 그러나 실제 값은 경우에 따라 달라질 수 있습니다. 실험을 통해 환경에 맞는 임계값을 확인하는 것이 중요합니다.
대체로 작은 양의 조각화를 제거할 경우 얻게 되는 이점보다 인덱스를 다시 구성하거나 다시 작성하는 비용이 훨씬 크기 때문에 두 명령 중 하나를 사용하여 매우 낮은 수준의 조각화(5% 미만)를 처리하는 것은 바람직하지 않습니다.
[!참고] 일반적으로 작은 인덱스는 자주 다시 작성하거나 다시 구성하더라도 조각화가 줄어들지 않습니다. 작은 인덱스의 페이지는 혼합 익스텐트에 저장됩니다. 혼합 익스텐트는 최대 8개의 개체에서 공유하므로 작은 인덱스를 다시 구성하거나 다시 작성해도 인덱스의 조각화가 줄어들지 않을 수 있습니다. 혼합 익스텐트에 대한 자세한 내용은 페이지 및 익스텐트를 참조하십시오.
예
다음 예에서는 sys.dm_db_index_physical_stats 동적 관리 함수를 쿼리하여 Production.Product 테이블의 모든 인덱스에 대한 평균 조각화를 반환합니다. 이전 테이블을 사용하여 PK_Product_ProductID를 다시 구성하고 다른 인덱스를 다시 작성하는 것이 좋습니다.
USE AdventureWorks;
GO
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'Production.Product'),
NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
GO
이 문은 다음과 비슷한 결과 집합을 반환합니다.
index_id name avg_fragmentation_in_percent
----------- --------------------------- ----------------------------
1 PK_Product_ProductID 15.076923076923077
2 AK_Product_ProductNumber 50.0
3 AK_Product_Name 66.666666666666657
4 AK_Product_rowguid 50.0
(4 row(s) affected)
인덱스 다시 구성
하나 이상의 인덱스를 다시 구성하려면 ALTER INDEX 문에 REORGANIZE 절을 사용합니다. 이 문은 DBCC INDEXDEFRAG 문을 대체합니다. 분할된 인덱스의 단일 파티션을 다시 구성하려면 ALTER INDEX의 PARTITION 절을 사용합니다.
인덱스를 다시 구성하면 리프 노드의 논리적 순서(왼쪽에서 오른쪽으로)와 일치하도록 리프 수준 페이지가 다시 정렬되어 테이블과 뷰의 클러스터형 및 비클러스터형 인덱스의 리프 수준이 조각 모음됩니다. 페이지를 정렬하면 인덱스 검색 성능이 향상됩니다. 인덱스 다시 구성 작업은 인덱스에 할당된 기존 페이지 내에서 수행되기 때문에 페이지가 새로 할당되지 않습니다. 인덱스가 두 개 이상의 파일에 존재할 경우 파일은 한 번에 한 개씩 다시 구성되며 파일 간에 페이지를 마이그레이션할 수 없습니다.
또한 다시 구성 작업을 수행하면 인덱스 페이지가 압축됩니다. 이러한 압축을 통해 생성된 빈 페이지는 제거되어 사용 가능한 디스크 공간이 늘어납니다. 이때 압축은 sys.indexes 카탈로그 뷰의 채우기 비율 값을 기준으로 수행됩니다.
다시 구성 프로세스는 최소한의 시스템 리소스를 사용하며 자동으로 온라인으로 수행됩니다. 이 프로세스는 차단 잠금을 오래 보유하지 않으므로 쿼리나 업데이트의 실행을 차단하지 않습니다.
인덱스가 심하게 조각화되지 않은 경우에는 인덱스를 다시 구성합니다. 조각화 지침은 이전 테이블을 참조하십시오. 하지만 인덱스가 심하게 조각화된 경우에는 인덱스를 다시 작성하면 보다 나은 결과를 얻게 됩니다.
LOB(Large Object) 데이터 형식 압축
하나 이상의 인덱스를 다시 구성하면 인덱스가 다시 구성될 때 기본적으로 클러스터형 인덱스나 기본 테이블에 포함된 LOB(Large Object) 데이터 형식이 압축됩니다. image, text, ntext, varchar(max), nvarchar(max), varbinary(max) 및 xml 데이터 형식은 LOB 데이터 형식입니다. 이 데이터를 압축하면 디스크 공간을 더 효율적으로 사용할 수 있습니다.
- 지정된 클러스터형 인덱스를 다시 구성하면 클러스터형 인덱스의 리프 수준(데이터 행)에 포함된 모든 LOB 열이 압축됩니다.
- 비클러스터형 인덱스를 다시 구성하면 인덱스 내의 키가 아닌 포괄 열인 모든 LOB 열이 압축됩니다.
- ALL을 지정하면 지정된 테이블이나 뷰와 연결된 모든 인덱스가 다시 구성되며 클러스터형 인덱스, 기본 테이블 또는 포괄 열이 있는 비클러스터형 인덱스와 연결된 모든 LOB 열이 압축됩니다.
- LOB 열이 없는 경우 LOB_COMPACTION 절은 무시됩니다.
인덱스 다시 작성
인덱스를 다시 작성하면 기존 인덱스가 삭제되고 새 인덱스가 생성됩니다. 이 과정에서 조각화가 제거되며 새로 지정한 채우기 비율 설정이나 기존의 채우기 비율 설정으로 페이지를 압축하여 디스크 공간을 확보하고 필요한 만큼 새 페이지를 할당하여 인덱스 행을 연속되는 페이지에 다시 정렬합니다. 그러면 요청한 데이터를 얻는 데 필요한 페이지 읽기 횟수를 줄일 수 있으므로 디스크 성능이 향상됩니다.
다음 방법을 사용하여 클러스터형 및 비클러스터형 인덱스를 다시 작성할 수 있습니다.
- ALTER INDEX에 REBUILD 절 사용. 이 문은 DBCC DBREINDEX 문을 대체합니다.
- CREATE INDEX에 DROP_EXISTING 절 사용
이들 방법은 모두 동일한 기능을 수행하지만 다음 테이블에서 볼 수 있는 것과 같이 각각 장점과 단점이 있습니다.
| 기능 | ALTER INDEX REBUILD | CREATE INDEX WITH DROP_EXISTING |
|---|---|---|
키 열을 추가 또는 제거하거나 열 순서를 변경하거나 열 정렬 순서를 변경하여 인덱스 정의를 변경할 수 있습니다.* |
아니요 |
예** |
인덱스 옵션을 설정하거나 수정할 수 있습니다. |
예 |
예 |
단일 트랜잭션으로 두 개 이상의 인덱스를 다시 작성할 수 있습니다. |
예 |
아니요 |
쿼리나 업데이트의 실행을 차단하지 않고 대부분의 인덱스 유형을 온라인으로 다시 작성할 수 있습니다. |
예 |
예 |
분할된 인덱스를 다시 분할할 수 있습니다. |
아니요 |
예 |
인덱스를 다른 파일 그룹으로 이동할 수 있습니다. |
아니요 |
예 |
추가 임시 디스크 공간이 필요합니다. |
예 |
예 |
클러스터형 인덱스를 다시 작성하면 연결된 비클러스터형 인덱스가 다시 작성됩니다. |
아니요 키워드 ALL이 지정되지 않은 경우 |
아니요 인덱스 정의가 변경되지 않은 경우 |
PRIMARY KEY 및 UNIQUE 제약 조건을 적용하는 인덱스를 제약 조건을 삭제 및 다시 생성하지 않고 다시 작성할 수 있습니다. |
예 |
예 |
단일 인덱스 파티션을 다시 작성할 수 있습니다. |
예 |
아니요 |
* 인덱스 정의에 CLUSTERED를 지정하여 비클러스터형 인덱스를 클러스터형 인덱스 유형으로 변환할 수 있습니다. 이 작업은 ONLINE 옵션을 OFF로 설정한 후 수행해야 합니다. ONLINE 설정에 관계없이 클러스터형에서 비클러스터형으로의 변환은 지원되지 않습니다.
** 동일한 이름, 열 및 정렬 순서를 사용하여 인덱스를 다시 생성할 경우 정렬 작업이 생략될 수 있습니다. 다시 작성 작업은 인덱스를 작성할 때 행이 정렬되었는지 확인합니다.
DROP INDEX 문으로 먼저 인덱스를 삭제한 후 별도의 CREATE INDEX 문으로 인덱스를 다시 생성하여 인덱스를 다시 작성할 수도 있습니다. 별도의 문으로 이러한 작업을 수행하면 여러 단점이 있으므로 이 방법은 권장되지 않습니다.
다시 작성 작업 중 디스크 공간을 절약하기 위해 비클러스터형 인덱스 비활성화
비클러스터형 인덱스를 비활성화하면 인덱스 데이터 행은 삭제되지만 인덱스 정의는 메타데이터에 유지됩니다. 인덱스는 다시 작성할 때 활성화됩니다. 비클러스터형 인덱스를 비활성화하지 않으면 다시 작성 작업을 수행할 때 기존 인덱스와 새 인덱스를 모두 저장할 수 있는 크기의 임시 디스크 공간이 필요합니다. 그러나 별도의 트랜잭션에서 비클러스터형 인덱스를 비활성화하고 다시 작성하면 인덱스를 비활성화하여 생긴 사용 가능한 디스크 공간을 이후에 인덱스를 다시 작성하거나 다른 작업을 수행할 때 다시 사용할 수 있습니다. 정렬을 위한 임시 디스크 공간 이외에는 추가 공간이 필요하지 않습니다. 이러한 임시 디스크 공간은 대개 인덱스 크기의 20%입니다. 비클러스터형 인덱스가 기본 키에 있을 경우 모든 활성화된 참조 FOREIGN KEY 제약 조건이 자동으로 비활성화됩니다. 이러한 제약 조건은 인덱스가 다시 작성된 후 수동으로 활성화해야 합니다. 자세한 내용은 인덱스 비활성화 및 인덱스 및 제약 조건 활성화에 대한 지침을 참조하십시오.
큰 인덱스 다시 작성
128 익스텐트 이상의 인덱스는 논리적 단계와 물리적 단계의 두 가지 단계를 거쳐 다시 작성됩니다. 논리적 단계에서는 인덱스에 의해 사용되는 기존 할당 단위가 할당 취소 상태로 표시되며 데이터 행이 복사되어 정렬된 후 다시 작성된 인덱스를 저장하기 위해 생성된 새 할당 단위로 옮겨집니다. 물리적 단계에서는 이전에 할당 취소 상태로 표시된 할당 단위가 백그라운드로 실행되는 짧은 트랜잭션을 통해 물리적으로 삭제됩니다. 이 단계는 잠금을 많이 필요로 하지 않습니다. 자세한 내용은 큰 개체 삭제 및 다시 작성을 참조하십시오.
인덱스 옵션 설정
인덱스를 다시 구성할 때는 인덱스 옵션을 지정할 수 없습니다. 하지만 ALTER INDEX REBUILD 또는 CREATE INDEX WITH DROP_EXISTING 중 하나를 사용하여 인덱스를 다시 작성할 때는 다음과 같은 인덱스 옵션을 설정할 수 있습니다.
PAD_INDEX |
DROP_EXISTING(CREATE INDEX 전용) |
FILLFACTOR |
ONLINE |
SORT_IN_TEMPDB |
ALLOW_ROW_LOCKS |
IGNORE_DUP_KEY |
ALLOW_PAGE_LOCKS |
STATISTICS_NORECOMPUTE |
MAXDOP |
[!참고] 정렬 작업이 필요하지 않거나 메모리에서 정렬을 수행할 수 있으면 SORT_IN_TEMPDB 옵션이 무시됩니다.
또한 ALTER INDEX 문에 SET 절을 사용하면 인덱스를 다시 작성하지 않고 다음 인덱스 옵션을 설정할 수 있습니다.
ALLOW_PAGE_LOCKS |
IGNORE_DUP_KEY |
ALLOW_ROW_LOCKS |
STATISTICS_NORECOMPUTE |
자세한 내용은 인덱스 옵션 설정을 참조하십시오.
인덱스를 다시 작성하거나 다시 구성하려면
인덱스를 삭제하고 다시 생성하여 한 단계로 인덱스를 다시 작성하려면
예
1. 인덱스 다시 작성
다음 예에서는 단일 인덱스를 다시 작성합니다.
USE AdventureWorks;
GO
ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee
REBUILD;
GO
2. 테이블의 모든 인덱스 다시 작성 및 옵션 지정
다음 예에서는 ALL 키워드를 지정합니다. 이 키워드는 테이블에 연결된 인덱스를 모두 다시 작성합니다. 3개의 옵션이 지정됩니다.
USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);
GO
3. 인덱스 다시 구성과 LOB 압축
다음 예에서는 단일 클러스터형 인덱스를 다시 구성합니다. 인덱스에 리프 수준의 LOB 데이터 형식이 포함되어 있으므로 해당 문은 LOB(Large Object) 개체 데이터를 포함하는 페이지도 모두 압축합니다. 기본값이 ON이기 때문에 WITH (LOB_Compaction) 옵션을 지정할 필요가 없습니다.
USE AdventureWorks;
GO
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE ;
GO
참고 항목
개념
관련 자료
ALTER INDEX(Transact-SQL)
인덱스 디스크 공간 요구 사항 확인
sys.dm_db_index_physical_stats
sys.dm_db_index_operational_stats
sys.indexes(Transact-SQL)
sys.dm_db_index_usage_stats
도움말 및 정보
변경 내역
| 릴리스 | 내역 |
|---|---|
2006년 7월 17일 |
|
2005년 12월 5일 |
|