이 항목에서는 SQL Server Management Studio 또는 Transact-SQL을 사용하여 SQL Server 2014에서 조각난 인덱스를 다시 구성하거나 다시 작성하는 방법을 설명합니다. SQL Server 데이터베이스 엔진은 기본 데이터에 대한 삽입, 업데이트 또는 삭제 작업이 수행 될 때마다 인덱스를 자동으로 유지 관리합니다. 시간이 지남에 따라 이러한 수정으로 인해 인덱스의 정보가 데이터베이스에 분산될 수 있습니다(조각화됨). 인덱스에 키 값에 따라 논리 순서가 데이터 파일 내의 실제 순서와 일치하지 않는 페이지가 있는 경우 조각화가 존재합니다. 많이 조각난 인덱스는 쿼리 성능을 저하시키고 애플리케이션이 느리게 응답하도록 할 수 있습니다.
인덱스 재구성 또는 다시 빌드를 통해 인덱스 조각화를 해결할 수 있습니다. 파티션 구성표를 기반으로 하는 분할된 인덱스의 경우 전체 인덱스 또는 인덱스의 단일 파티션에서 이러한 메서드 중 하나를 사용할 수 있습니다. 인덱스를 다시 작성하면 이 인덱스가 삭제된 다음 다시 생성됩니다. 이렇게 하면 조각화를 제거하고, 지정된 채우기 비율 또는 기존 채우기 비율 설정을 기준으로 페이지를 압축하여 디스크 공간을 회수하고, 인덱스 행을 연속된 페이지로 다시 정렬할 수 있습니다. ALL을 지정하면 테이블의 모든 인덱스가 삭제되고 단일 트랜잭션에서 다시 작성됩니다. 인덱스를 다시 구성할 때는 최소한의 시스템 리소스가 사용됩니다. 이때는 왼쪽에서 오른쪽으로 표시되는 리프 노드의 논리적 순서에 맞도록 리프 수준 페이지를 물리적으로 다시 정렬하여 테이블 및 뷰의 클러스터형 및 비클러스터형 인덱스의 리프 수준에 대한 조각 모음을 수행합니다. 다시 구성 작업을 수행하면 인덱스 페이지도 압축됩니다. 이때 압축은 기존 채우기 비율 값을 기준으로 수행됩니다.
이 항목에서
시작하기 전 주의 사항:
인덱스의 조각화를 확인하려면:
인덱스 다시 구성 또는 다시 작성하려면:
시작하기 전에
조각화 감지
사용할 디프래그 방법을 결정하기 위한 첫 번째 단계는 인덱스를 분석하여 조각화 정도를 확인하는 것입니다. 시스템 함수 sys.dm_db_index_physical_stats 사용하면 특정 인덱스의 조각화, 테이블 또는 인덱싱된 뷰의 모든 인덱스, 데이터베이스의 모든 인덱스 또는 모든 데이터베이스의 모든 인덱스를 검색할 수 있습니다. 분할된 인덱스의 경우 sys.dm_db_index_physical_stats 각 파티션에 대한 조각화 정보도 제공합니다.
sys.dm_db_index_physical_stats 함수에서 반환된 결과 집합에는 다음 열이 포함됩니다.
| 칼럼 | 설명 |
|---|---|
| avg_fragmentation_in_percent | 논리 조각화의 백분율(인덱스의 순서가 다른 페이지)입니다. |
| fragment_count | 인덱스의 조각 수(물리적으로 연속된 리프 페이지)입니다. |
| 페이지 단위 평균 단편 크기 | 인덱스의 한 조각에 있는 평균 페이지 수입니다. |
조각화 수준을 알고 나면 다음 표를 사용하여 조각화를 수정하는 가장 좋은 방법을 결정합니다.
| avg_fragmentation_in_percent 값 | 정정 성명 |
|---|---|
| > 5% 및 < = 30% | ALTER INDEX 재구성 (REORGANIZE) |
| > 30% | ALTER INDEX REBUILD WITH(ONLINE = ON) 1 |
1 인덱스 다시 작성은 온라인 또는 오프라인으로 실행할 수 있습니다. 인덱스 재구성은 항상 온라인으로 실행됩니다. 다시 구성 옵션과 유사한 가용성을 얻으려면 온라인으로 인덱스를 다시 작성해야 합니다.
팁 (조언)
이러한 값은 ALTER INDEX REORGANIZE와 ALTER INDEX REBUILD 사이를 전환해야 하는 지점을 결정하기 위한 대략적인 지침을 제공합니다. 그러나 실제 값은 경우에 따라 다를 수 있습니다. 환경에 가장 적합한 임계값을 결정하기 위해 실험하는 것이 중요합니다. 예를 들어 지정된 인덱스가 주로 검사 작업에 사용되는 경우 조각화를 제거하면 이러한 작업의 성능이 향상될 수 있습니다. 주로 검색 작업에 사용되는 인덱스의 성능 이점은 눈에 띄지 않습니다. 마찬가지로 클러스터형 인덱스가 없는 테이블인 힙에서 조각화를 제거하는 것은 비클러스터형 인덱스 검색 작업에 특히 유용하지만 조회 작업에는 거의 영향을 주지 않습니다.
이러한 소량의 조각화를 제거하는 이점은 인덱스를 다시 구성하거나 다시 빌드하는 비용보다 거의 항상 훨씬 더 크기 때문에 매우 낮은 수준의 조각화(5% 미만)는 일반적으로 이러한 명령 중 하나에서 다루지 않아야 합니다.
비고
작은 인덱스를 다시 작성하거나 다시 구성해도 조각화가 감소하지 않는 경우가 많습니다. 작은 인덱스의 페이지는 때때로 혼합된 익스텐트에 저장될 수 있습니다. 혼합 익스텐트를 최대 8개의 개체에서 공유하므로 다시 구성하거나 다시 빌드한 후에는 작은 인덱스의 조각화가 줄어들지 않을 수 있습니다.
인덱스 조각 모음 고려 사항
특정 조건에서 클러스터형 인덱스 다시 작성은 비클러스터형 인덱스 레코드에 포함된 물리적 또는 논리적 식별자를 변경해야 하는 경우 클러스터링 키를 참조하는 비클러스터형 인덱스가 자동으로 다시 작성됩니다.
테이블에서 모든 비클러스터형 인덱스를 자동으로 다시 작성하도록 강제하는 시나리오:
- 테이블에 클러스터형 인덱스 만들기
- 클러스터형 인덱스 제거로 인해 테이블이 힙으로 저장됨
- 열을 포함하거나 제외하도록 클러스터링 키 변경
모든 비클러스터형 인덱스를 테이블에서 자동으로 다시 작성할 필요가 없는 시나리오:
- 고유한 클러스터형 인덱스 다시 작성
- 고유하지 않은 클러스터형 인덱스 다시 작성
- 클러스터형 인덱스로 분할 체계를 적용하거나 클러스터형 인덱스를 다른 파일 그룹으로 이동하는 등 인덱스 스키마 변경
한계 및 제한사항
익스텐트가 128개가 넘는 인덱스는 논리적 및 물리적 두 단계로 다시 작성됩니다. 논리적 단계에서 인덱스에 사용되는 기존 할당 단위는 할당 취소로 표시되고 데이터 행은 복사 및 정렬된 다음 다시 빌드된 인덱스를 저장하기 위해 만든 새 할당 단위로 이동됩니다. 물리적 단계에서는 이전에 할당 취소 상태로 표시된 할당 단위가 백그라운드로 실행되는 짧은 트랜잭션을 통해 물리적으로 삭제됩니다. 이 단계는 잠금을 많이 필요로 하지 않습니다. 익스텐트에 대한 자세한 내용은 페이지 및 익스텐트 아키텍처 가이드를 참조하세요.
작업 ALTER INDEX REORGANIZE 에서 파일 그룹 내의 다른 파일이 아닌 동일한 파일에 임시 작업 페이지만 할당할 수 있으므로 인덱스가 포함된 데이터 파일에 사용 가능한 공간이 있어야 합니다. 따라서 파일 그룹에 사용 가능한 무료 페이지가 있을 수 있지만 사용자는 여전히 오류 1105가 발생할 수 있습니다. Could not allocate space for object '###' in database '###' because the '###' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
파티션이 1,000개 이상인 테이블에서 정렬되지 않은 인덱스를 만들고 다시 작성할 수 있지만 권장되지는 않습니다. 그러면 작업 중에 성능이 저하되거나 메모리가 과도하게 소비될 수 있습니다.
인덱스가 있는 파일 그룹이 오프라인이거나 읽기 전용으로 설정된 경우 인덱스를 다시 구성하거나 다시 작성할 수 없습니다. 키워드 ALL 를 지정하고 하나 이상의 인덱스가 오프라인 또는 읽기 전용 파일 그룹에 있는 경우 문이 실패합니다.
안전
권한
테이블 또는 보기에 대한 ALTER 권한이 필요합니다. 사용자는 sysadmin 고정 서버 역할의 멤버 또는 db_ddladmin 및 db_owner 고정 데이터베이스 역할의 멤버여야 합니다.
SQL Server Management Studio 사용
인덱스의 조각화를 확인하려면
개체 탐색기에서 인덱스의 조각화를 확인할 테이블이 포함된 데이터베이스를 확장합니다.
테이블 폴더를 확장합니다.
인덱스의 조각화를 확인할 테이블을 확장합니다.
인덱스 폴더를 확장합니다.
조각화를 확인하려는 인덱스를 마우스 오른쪽 단추로 클릭하고 속성을 선택하십시오.
페이지 선택에서 조각화를 선택합니다.
조각 화 페이지에서 사용할 수 있는 정보는 다음과 같습니다.
페이지 충만
인덱스 페이지의 평균 충만도를 백분율로 나타냅니다. 100% 인덱스 페이지가 완전히 가득 찼다는 것을 의미합니다. %는 평균적으로 각 인덱스 페이지가 절반 정도 채워져 있다는 것을 의미합니다.총 조각화
논리적 조각화 백분율입니다. 이는 순서대로 저장되지 않은 인덱스의 페이지 수를 나타냅니다.평균 행 크기
리프 수준 행의 평균 크기입니다.깊이
리프 수준을 포함하여 인덱스의 수준 수입니다.전달된 레코드
다른 데이터 위치에 대한 전달 포인터가 있는 힙의 레코드 수입니다. (이 상태는 업데이트 중에 새 행을 원래 위치에 저장할 공간이 충분하지 않은 경우에 발생합니다.)고스트 행
삭제된 것으로 표시되었지만 아직 제거되지 않은 행의 수입니다. 이러한 행은 서버가 사용 중이 아닌 경우 정리 스레드에 의해 제거됩니다. 이 값에는 아직 해결되지 않은 스냅샷 격리 트랜잭션으로 인해 보존되고 있는 행이 포함되지 않습니다.인덱스 유형
인덱스의 형식입니다. 가능한 값은 클러스터형 인덱스, 비클러스터형 인덱스 및 기본 XML입니다. 테이블은 인덱스 없이 힙으로 저장할 수도 있지만 이 인덱스 속성 페이지를 열 수 없습니다.리프 레벨 행
리프 수준 행의 수입니다.최대 행 크기
최대 리프 수준 행 크기입니다.최소 행 크기
최소 리프 수준 행 크기입니다.페이지
총 데이터 페이지 수입니다.파티션 ID
인덱스가 포함된 b-트리의 파티션 ID입니다.버전 가상 행
미해결 스냅샷 격리 트랜잭션으로 인해 보존 중인 고스트 레코드의 수입니다.
Transact-SQL 사용
인덱스의 조각화를 확인하려면
개체 탐색기에서 데이터베이스 엔진인스턴스에 연결합니다.
표준 도구 모음에서 새 쿼리를 클릭합니다.
다음 예를 복사하여 쿼리 창에 붙여 넣고 실행을 클릭합니다.
USE AdventureWorks2012; GO -- Find the average fragmentation percentage of all indexes -- in the HumanResources.Employee table. SELECT a.index_id, name, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorks2012'), OBJECT_ID(N'HumanResources.Employee'), 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_Employee_BusinessEntityID 0 2 IX_Employee_OrganizationalNode 0 3 IX_Employee_OrganizationalLevel_OrganizationalNode 0 5 AK_Employee_LoginID 66.6666666666667 6 AK_Employee_NationalIDNumber 50 7 AK_Employee_rowguid 0 (6 row(s) affected)
자세한 내용은 sys.dm_db_index_physical_stats(Transact-SQL)를 참조하세요.
SQL Server Management Studio 사용
인덱스 다시 구성 또는 다시 작성
개체 탐색기에서 인덱스를 다시 구성할 테이블이 포함된 데이터베이스를 확장합니다.
테이블 폴더를 확장합니다.
인덱스를 다시 구성할 테이블을 확장합니다.
인덱스 폴더를 확장합니다.
다시 구성할 인덱스를 마우스 오른쪽 단추로 클릭하고 다시 구성을 선택합니다.
인덱스 다시 구성 대화 상자에서 올바른 인덱스가 다시 구성될 인덱스에 있는지 확인하고 확인을 클릭합니다.
큰 개체 열 데이터 압축 확인란을 선택하여 LOB(Large Object) 데이터가 포함된 모든 페이지도 압축되도록 지정합니다.
확인을 클릭합니다.
테이블의 모든 인덱스를 다시 구성하려면
개체 탐색기에서 인덱스를 다시 구성할 테이블이 포함된 데이터베이스를 확장합니다.
테이블 폴더를 확장합니다.
인덱스를 다시 구성할 테이블을 확장합니다.
인덱스 폴더를 마우스 오른쪽 단추로 클릭하고 다시 구성을 선택합니다.
인덱스 다시 구성 대화 상자에서 다시 구성할 인덱스에 올바른 인덱스가 있는지 확인합니다. 다시 구성할 인덱스 표에서 인덱스를 제거하려면 인덱스를 선택한 다음 Delete 키를 누릅니다.
큰 개체 열 데이터 압축 확인란을 선택하여 LOB(Large Object) 데이터가 포함된 모든 페이지도 압축되도록 지정합니다.
확인을 클릭합니다.
인덱스 다시 작성
개체 탐색기에서 인덱스를 다시 구성할 테이블이 포함된 데이터베이스를 확장합니다.
테이블 폴더를 확장합니다.
인덱스를 다시 구성할 테이블을 확장합니다.
인덱스 폴더를 확장합니다.
다시 구성할 인덱스를 마우스 오른쪽 단추로 클릭하고 다시 구성을 선택합니다.
인덱스 다시 작성 대화 상자에서 올바른 인덱스가 다시 작성할 인덱스에 있는지 확인하고 확인을 클릭합니다.
큰 개체 열 데이터 압축 확인란을 선택하여 LOB(Large Object) 데이터가 포함된 모든 페이지도 압축되도록 지정합니다.
확인을 클릭합니다.
Transact-SQL 사용
조각 모음된 인덱스 다시 구성하려면
개체 탐색기에서 데이터베이스 엔진인스턴스에 연결합니다.
표준 도구 모음에서 새 쿼리를 클릭합니다.
다음 예를 복사하여 쿼리 창에 붙여 넣고 실행을 클릭합니다.
USE AdventureWorks2012; GO -- Reorganize the IX_Employee_OrganizationalLevel_OrganizationalNode index on the HumanResources.Employee table. ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode ON HumanResources.Employee REORGANIZE ; GO
테이블의 모든 인덱스를 다시 구성하려면
개체 탐색기에서 데이터베이스 엔진인스턴스에 연결합니다.
표준 도구 모음에서 새 쿼리를 클릭합니다.
다음 예를 복사하여 쿼리 창에 붙여 넣고 실행을 클릭합니다.
USE AdventureWorks2012; GO -- Reorganize all indexes on the HumanResources.Employee table. ALTER INDEX ALL ON HumanResources.Employee REORGANIZE ; GO
조각 모음된 인덱스 다시 작성
개체 탐색기에서 데이터베이스 엔진인스턴스에 연결합니다.
표준 도구 모음에서 새 쿼리를 클릭합니다.
다음 예를 복사하여 쿼리 창에 붙여 넣고 실행을 클릭합니다. 이 예제에서는 테이블에서 단일 인덱스를 다시 작성합니다
Employee.USE AdventureWorks2012; GO ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee REBUILD; GO
테이블의 모든 인덱스를 다시 작성하려면
개체 탐색기에서 데이터베이스 엔진인스턴스에 연결합니다.
표준 도구 모음에서 새 쿼리를 클릭합니다.
다음 예제를 복사하여 쿼리에 붙여넣습니다. 이 예제에서는 키워드
ALL를 지정합니다. 그러면 테이블과 연결된 모든 인덱스가 다시 작성됩니다. 3개의 옵션이 지정됩니다.USE AdventureWorks2012; GO ALTER INDEX ALL ON Production.Product REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON); GO
자세한 내용은 ALTER INDEX(Transact-SQL)를 참조하세요.