쿼리 성능 향상 및 리소스 소비 감소를 위한 인덱스 유지 관리 최적화

적용 대상: SQL Server(지원되는 모든 버전) Azure SQL Database Azure SQL Managed Instance Analytics Platform System(PDW)

이 문서는 인덱스 유지 관리를 수행하는 시기와 방법을 결정하는 데 도움이 됩니다. 인덱스 조각화 및 페이지 밀도와 같은 개념 및 이러한 개념이 쿼리 성능 및 리소스 소비에 미치는 영향에 대해 설명합니다. 인덱스 유지 관리 방법, 인덱스 다시 구성인덱스 다시 작성에 대해 설명하고, 유지 관리에 필요한 리소스 소비와 잠재적인 성능 향상 사이에서 균형을 맞추는 인덱스 유지 관리 전략을 제안합니다.

참고

이 문서의 정보는 Azure Synapse Analytics의 전용 SQL 풀에는 적용되지 않습니다. Azure Synapse Analytics의 전용 SQL 풀에 대한 인덱스 유지 관리에 대한 자세한 내용은 Azure Synapse Analytics의 전용 SQL 풀 테이블 인덱싱을 참조하세요.

개념: 인덱스 조각화 및 페이지 밀도

인덱스 조각화란 무엇이고 성능에 어떤 영향을 미치는가:

  • B-트리(rowstore) 인덱스에서, 조각화는 인덱스의 키 값을 기반으로 인덱스 내의 논리적 순서가 인덱스 페이지의 물리적 순서와 일치하지 않는 페이지가 인덱스에 있는 경우 발생합니다.

    참고

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

  • 데이터베이스 엔진은 기본 데이터에 대한 삽입, 업데이트 또는 삭제 작업이 수행 될 때마다 인덱스를 자동으로 수정합니다. 예를 들어 테이블에 행을 추가하면 rowstore 인덱스의 기존 페이지가 분할되어 새 행을 삽입할 공간이 생길 수 있습니다. 이러한 수정이 거듭되면 시간이 흐름에 따라 인덱스의 데이터가 조각화되어 데이터베이스 내에 흩어지게 될 수 있습니다.

  • 전체 또는 범위 인덱스 검색을 사용하여 많은 페이지를 읽는 쿼리의 경우 심하게 조각화된 인덱스를 사용하면 쿼리 성능이 저하될 수 있습니다. 쿼리에 필요한 데이터를 읽기 위해 I/O가 추가로 필요할 수 있기 때문입니다. 쿼리에서 동일한 양의 데이터를 읽으려면 적은 수의 대규모 I/O 요청 대신 많은 수의 소규모 I/O 요청이 필요합니다.

  • 스토리지 하위 시스템의 순차적 I/O 성능이 무작위 I/O 성능보다 더 나은 경우 인덱스 조각화로 인해 성능이 저하될 수 있습니다. 조각화된 인덱스를 읽으려면 무작위 I/O가 더 많이 필요하기 때문입니다.

페이지 밀도(페이지 충만도라고도 함)란 무엇이며 성능에는 어떤 영향을 미치는가:

  • 데이터베이스의 각 페이지에는 가변적인 행 수가 포함될 수 있습니다. 행이 페이지의 모든 공간을 차지하는 경우 페이지 밀도는 100%입니다. 페이지가 비어있는 경우 페이지 밀도는 0%입니다. 밀도가 100%인 한 페이지가 새 행을 수용하기 위해 두 페이지로 분할되면 새로운 두 페이지의 밀도는 약50%가 됩니다.
  • 페이지 밀도가 낮은 경우 동일한 양의 데이터를 저장하려면 페이지가 더 많이 필요합니다. 즉, 데이터를 읽고 쓰는 데 더 많은 I/O가 필요하고, 데이터를 캐시하는 데 더 많은 메모리가 필요합니다. 메모리가 제한된 경우, 쿼리에 필요한 페이지가 더 적게 캐시되어 디스크 I/O가 더 많이 발생합니다. 따라서 페이지 밀도가 낮으면 성능에 부정적인 영향을 줍니다.
  • 데이터베이스 엔진이 페이지에 행을 추가하는 경우 인덱스의 채우기 인수 가 100(또는 이 컨텍스트에 해당하는 0)이 아닌 값으로 설정된 경우 페이지를 완전히 채우지 않습니다. 이로 인해 페이지 밀도가 낮아지고 마찬가지로 I/O 오버헤드가 추가되고 성능에 부정적인 영향을 줍니다.
  • 페이지 밀도가 낮으면 중간 B-트리 수준의 수를 늘릴 수 있습니다. 그러면 인덱스 검색 및 탐색에서 리프 수준 페이지를 찾는 데 드는 CPU 및 I/O 비용이 적당히 늘어납니다.
  • 쿼리 최적화 프로그램이 쿼리 계획을 컴파일할 때는 쿼리에 필요한 데이터를 읽는 데 필요한 I/O 비용이 고려됩니다. 페이지 밀도가 낮으면 읽을 페이지가 많아져서 I/O 비용이 더 높습니다. 이것은 쿼리 계획 선택에 영향을 줄 수 있습니다. 예를 들어, 시간이 지남에 따라 페이지 분할로 인해 페이지 밀도가 감소하면, 최적화 프로그램은 다른 성능 및 리소스 소비 프로필을 사용하여 동일한 쿼리에 대해 다른 계획을 컴파일할 수 있습니다.

많은 워크로드에서 페이지 밀도를 높이는 것이 조각화를 줄이는 것보다 성능에 더 긍정적인 영향을 줍니다.

페이지 밀도를 불필요하게 낮추지 않으려면, 페이지 분할 수가 많은 인덱스(예: 비연속 GUID 값을 포함하는 선행 열이 포함된 수정이 빈번한 인덱스)와 같은 특정한 경우를 제외하고, 채우기 비율을 100 또는 0 이외의 값으로 설정하지 않는 것이 좋습니다.

인덱스 조각화 및 페이지 밀도 측정

조각화와 페이지 밀도는 모두 인덱스 유지 관리를 수행할지 여부와 어떤 유지 관리 방법을 사용할지를 결정할 때 고려해야 할 요소에 속합니다.

조각화는 rowstorecolumnstore 인덱스에 대해 다르게 정의됩니다. rowstore 인덱스의 경우 sys.dm_db_index_physical_stats()를 사용하면 특정 인덱스, 테이블 또는 인덱싱된 뷰의 모든 인덱스, 데이터베이스의 모든 인덱스 또는 모든 데이터베이스의 모든 인덱스에 대한 조각화 및 페이지 밀도를 확인할 수 있습니다. 분할된 인덱스의 경우 sys.dm_db_index_physical_stats()를 사용하여 각 파티션에 대해 이러한 정보를 제공합니다.

sys.dm_db_index_physical_stats에서 반환된 결과 집합은 다음 열을 포함합니다.

Description
avg_fragmentation_in_percent 논리적 조각화(인덱스에서 순서가 잘못된 페이지)
avg_page_space_used_in_percent 평균 페이지 밀도

columnstore 인덱스의 압축된 행 그룹의 경우, 조각화는 전체 행 대비 삭제된 행의 비율로 정의되며 백분율로 표시됩니다. sys.dm_db_column_store_row_group_physical_stats를 사용하면 특정 인덱스, 테이블의 모든 인덱스 또는 데이터베이스의 모든 인덱스에서 행 그룹당 총 행 수 및 삭제된 행 수를 확인할 수 있습니다.

sys.dm_db_column_store_row_group_physical_stats에서 반환된 결과 집합은 다음 열을 포함합니다.

Description
total_rows 행 그룹에 물리적으로 저장된 행 수입니다. 압축된 행 그룹의 경우 여기에 삭제된 것으로 표시된 행이 포함됩니다.
deleted_rows 삭제되도록 표시된 압축된 행 그룹에 물리적으로 저장된 행의 수입니다. 델타 저장소에 있는 행 그룹의 경우 0입니다.

columnstore 인덱스의 압축된 행 그룹 조각화는 다음 수식을 사용하여 계산할 수 있습니다.

100.0*(ISNULL(deleted_rows,0))/NULLIF(total_rows,0)

rowstore 및 columnstore 인덱스 모두의 경우 다수의 행이 삭제되거나 업데이트된 후에는 인덱스 또는 힙 조각화 및 페이지 밀도를 검토하는 것이 특히 중요합니다. 힙의 경우, 업데이트가 빈번하면 전달 레코드의 확산을 방지하기 위해 조각화를 주기적으로 검토해야 할 수도 있습니다. 힙에 대한 자세한 내용은 힙(클러스터형 인덱스가 없는 테이블)을 참조하세요.

조각화 및 페이지 밀도를 확인하는 샘플 쿼리는 예제를 참조하세요.

인덱스 유지 관리 방법: 다시 구성 및 다시 작성

다음 중 한 가지 방법을 사용하여 인덱스 조각화를 줄이고 페이지 밀도를 높일 수 있습니다.

  • 인덱스 재구성
  • 인덱스 다시 작성

참고

분할된 인덱스의 경우, 모든 파티션 또는 인덱스의 단일 파티션에서 다음 중 한 가지 방법을 사용할 수 있습니다.

인덱스 재구성

인덱스 다시 구성은 인덱스를 다시 작성하는 것보다 리소스가 덜 사용됩니다. 따라서 인덱스 다시 작성을 사용해야 하는 특별한 이유가 없는 한 기본 인덱스 유지 관리 방법으로 사용해야 합니다. 다시 구성은 항상 온라인 작업입니다. 즉, 장기간 개체 수준 잠금이 유지되지 않으며 ALTER INDEX ... REORGANIZE 작업 중 기본 테이블에 대한 쿼리나 업데이트를 계속할 수 있습니다.

  • rowstore 인덱스의 경우 데이터베이스 엔진은 리프 노드의 논리적 순서(왼쪽에서 오른쪽)와 일치하도록 리프 수준 페이지를 물리적으로 다시 정렬하여 테이블 및 뷰에서 클러스터형 및 비클러스터형 인덱스의 리프 수준만 조각 모음합니다. 또한, 다시 구성은 인덱스 페이지를 압축하여 페이지 밀도를 인덱스의 채우기 비율과 동일하게 만듭니다. 채우기 비율 설정을 보려면 sys.indexes를 사용하세요. 구문 예제는 예 - rowstore 다시 구성을 참조하세요.
  • columnstore 인덱스를 사용하는 경우 시간 경과에 따라 데이터를 삽입, 업데이트 및 삭제함으로써 델타 저장소가 여러 개의 작은 행 그룹으로 분할될 수 있습니다. columnstore 인덱스 다시 구성을 수행하면, 델타 저장소 행 그룹이 columnstore의 압축된 행 그룹으로 강제로 들어가고 소규모의 압축된 행 그룹은 대규모의 행 그룹으로 결합됩니다. 다시 구성 작업은 columnstore에서 삭제된 것으로 표시된 행을 물리적으로도 제거합니다. columnstore 인덱스를 다시 구성하려면 데이터를 압축하기 위해 CPU 리소스가 추가로 필요할 수 있으며, 이로 인해 작업이 실행되는 동안 전체 시스템 성능이 저하될 수 있습니다. 하지만 데이터가 압축되고 나면 쿼리 성능이 향상됩니다. 구문 예제는 예 - columnstore 다시 구성을 참조하세요.

참고

SQL Server 2019(15.x), Azure SQL 데이터베이스 및 Azure SQL Managed Instance 튜플 이동기는 내부 임계값에 따라 일정 시간 동안 존재했던 더 작은 열린 델타 행 그룹을 자동으로 압축하거나 많은 수의 행이 있는 곳에서 압축된 행 그룹을 병합하는 백그라운드 병합 작업의 도움을 받습니다. 가 삭제되었습니다. 이번 변경 덕분에 지속적으로 columnstore 인덱스 품질이 향상됩니다. 대부분의 경우 이를 통해 ALTER INDEX ... REORGANIZE 명령 실행의 필요성이 사라집니다.

다시 구성 작업을 취소하거나 다른 방법으로 중단한 경우 해당 지점까지의 진행 상태가 데이터베이스에 유지됩니다. 대량 인덱스를 다시 구성하려면 완료될 때까지 여러 번 작업을 시작하고 중지할 수 있습니다.

인덱스 다시 작성

인덱스를 다시 작성하면 이 인덱스가 삭제된 다음 다시 생성됩니다. 인덱스 유형과 데이터베이스 엔진 버전에 따라 온라인이나 오프라인에서 다시 작성 작업을 수행할 수 있습니다. 오프라인 인덱스 다시 작성은 온라인 다시 작성보다 일반적으로 시간이 덜 걸리지만 다시 작성 작업 중에 개체 수준 잠금이 유지되기 때문에 테이블이나 뷰에 쿼리가 액세스할 수 없도록 차단됩니다.

온라인 인덱스 다시 작성은 다시 작성을 완료하기 위해 잠깐 동안 잠금을 유지해야 하는 작업이 끝날 때까지 개체 수준 잠금이 필요하지 않습니다. 데이터베이스 엔진 버전에 따라 온라인 인덱스 다시 작성을 다시 시작 가능한 작업으로 시작할 수 있습니다. 다시 시작 가능한 인덱스 다시 작성은 작업을 일시 중지하고, 해당 지점까지의 진행 상태를 유지할 수 있습니다. 다시 시작 가능한 다시 작성 작업은 일시 중지 또는 중단했다가 다시 시작하거나, 다시 작성을 완료할 필요가 없으면 중단할 수 있습니다.

Transact-SQL 구문에 대한 자세한 내용은 ALTER INDEX REBUILD를 참조하세요. 온라인 인덱스 다시 작성에 대한 자세한 내용은 온라인으로 인덱스 작업 수행을 참조하세요.

참고

인덱스가 온라인에서 다시 작성되는 동안, 인덱싱된 열의 데이터를 수정할 때마다 인덱스의 추가 복사본을 업데이트해야 합니다. 그러면 온라인 다시 작성 중에 데이터 수정 문의 성능이 약간 저하될 수 있습니다.

다시 시작 가능한 온라인 인덱스 작업이 일시 중지되면, 다시 시작 가능한 작업이 완료되거나 중단될 때까지 성능에 미치는 영향이 지속됩니다. 다시 시작 가능한 인덱스 작업을 완료하지 않으려면 일시 중지하는 대신 중단하세요.

사용 가능한 리소스 및 워크로드 패턴에 따라서는 ALTER INDEX REBUILD 문에 기본 MAXDOP 값보다 큰 값을 지정하면 CPU 사용률이 높아지는 대신 다시 작성 기간이 단축될 수 있습니다.

  • rowstore 인덱스의 경우 다시 작성하면 인덱스의 모든 수준에서 조각화가 제거되고 지정된 비율 또는 현재 채우기 비율에 따라 페이지가 압축됩니다. ALL을 지정하면 테이블의 모든 인덱스가 단일 작업으로 삭제되고 다시 작성됩니다. 익스텐트가 128개 이상인 인덱스가 다시 작성되면 데이터베이스 엔진은 페이지 할당 취소를 연기하고 다시 빌드가 완료될 때까지 연결된 잠금을 획득합니다. 구문 예제는 예 - rowstore 다시 작성을 참조하세요.

  • columnstore 인덱스의 경우, 다시 작성을 수행하면 조각화가 제거되고, 델타 저장소 행이 columnstore로 옮겨지고, 삭제 표시가 된 행이 물리적으로 삭제됩니다. 구문 예제는 예 - columnstore 다시 작성을 참조하세요.

    SQL Server 2016(13.x)부터 columnstore 인덱스를 다시 빌드하는 것은 일반적으로 온라인 작업으로 다시 빌드의 필수 사항을 수행하기 때문에 REORGANIZE 필요하지 않습니다.

인덱스 다시 작성을 사용하여 데이터 손상 복구

이전 버전의 SQL Server 경우에 따라 rowstore 비클러스터형 인덱스를 다시 빌드하여 인덱스의 데이터 손상으로 인한 불일치를 수정할 수 있습니다.

SQL Server 2008부터 비클러스터형 인덱스 오프라인을 다시 빌드하여 비클러스터형 인덱스에서 이러한 불일치를 복구할 수 있습니다. 단, 인덱스를 온라인으로 다시 작성하는 경우에는 비클러스터형 인덱스 간의 불일치를 해결할 수 없습니다. 온라인으로 다시 작성하는 메커니즘은 기존의 비클러스터형 인덱스를 기반으로 사용하기 때문에 불일치가 계속 유지됩니다. 인덱스를 오프라인으로 다시 작성하면 클러스터형 인덱스(또는 힙)를 강제로 검색하는 경우가 있기 때문에 비클러스터형 인덱스의 불일치 데이터를 클러스터형 인덱스 또는 힙의 데이터로 대체할 수 있습니다.

클러스터형 인덱스 또는 힙이 데이터 원본으로 사용되도록 하려면 비클러스터형 인덱스를 다시 작성하는 대신 삭제하고 다시 만듭니다. 이전 버전의 경우처럼 영향을 받은 데이터를 백업에서 복원하여 불일치를 해결하는 것이 좋습니다. 단, 비클러스터형 인덱스의 경우에는 오프라인으로 다시 작성하거나 만들어서 불일치를 해결할 수 있습니다. 자세한 내용은 DBCC CHECKDB(Transact-SQL)를 참조하세요

자동 인덱스 및 통계 관리

Adaptive Index Defrag와 같은 솔루션을 활용하여 하나 이상의 데이터베이스에 대한 인덱스 조각화 및 통계 업데이트를 자동으로 관리합니다. 이 절차는 다른 매개 변수 사이에서 조각화 수준에 따라 인덱스를 다시 작성하거나 다시 구성할지 여부를 자동으로 선택하고 통계를 선형 임계값으로 업데이트합니다.

rowstore 인덱스 다시 작성 및 다시 구성과 관련된 고려 사항

다음 시나리오의 경우 테이블의 모든 rowstore 비클러스터형 인덱스가 자동으로 다시 작성됩니다.

  • 테이블에 클러스터형 인덱스를 생성하는 경우(CREATE CLUSTERED INDEX ... WITH (DROP_EXISTING = ON)을 사용하여 다른 키로 클러스터형 인덱스를 다시 만드는 경우 포함)
  • 클러스터형 인덱스를 삭제하여 테이블이 힙으로 저장되는 경우

다음 시나리오의 경우 동일한 테이블에서 모든 rowstore 비클러스터형 인덱스가 자동으로 다시 작성되지 않습니다.

  • 클러스터형 인덱스를 다시 작성하는 경우
  • 클러스터형 인덱스 스토리지를 변경하는 경우(파티션 구성표를 적용하거나 클러스터형 인덱스를 다른 파일 그룹으로 옮기는 경우 등)

중요

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

인덱스를 다시 빌드하는 동안 물리적 미디어에는 인덱스의 두 복사본을 저장할 수 있는 충분한 공간이 있어야 합니다. 다시 빌드가 완료되면 데이터베이스 엔진이 원래 인덱스를 삭제합니다.

ALTER INDEX ... REORGANIZE 문에 ALL을 지정하면 테이블의 클러스터형, 비클러스터형 및 XML 인덱스가 다시 구성됩니다.

작은 rowstore 인덱스를 다시 작성하거나 다시 구성해도 조각화를 줄일 수 없습니다. SQL Server 2014(12.x)까지 SQL Server 데이터베이스 엔진은 혼합 익스텐트 사용 공간을 할당합니다. 따라서 작은 인덱스의 페이지가 혼합 익스텐트에 저장되어 인덱스가 암시적으로 조각화되는 경우가 있습니다. 혼합 익스텐트는 최대 8개의 개체가 공유할 수 있으므로 인덱스를 다시 작성하거나 다시 구성한 후에도 작은 인덱스의 조각화가 줄어들지 않을 수 있습니다.

Columnstore 인덱스 다시 빌드 관련 고려 사항

columnstore 인덱스 다시 작성 시 데이터베이스 엔진은 델타 저장소를 포함하여 원래 columnstore 인덱스에서 모든 데이터를 읽습니다. 데이터가 새 행 그룹으로 결합되고 모든 행 그룹이 columnstore로 압축됩니다. 데이터베이스 엔진은 삭제된 것으로 표시된 행을 물리적으로 삭제하여 columnstore를 조각 모음합니다.

참고

SQL Server 2019(15.x)부터 튜플 이동기는 내부 임계값에 따라 일정 시간 동안 존재했던 더 작은 열린 델타 저장소 행 그룹을 자동으로 압축하거나 많은 수의 행이 삭제된 압축된 행 그룹을 병합하는 백그라운드 병합 작업의 도움을 받습니다. 이렇게 하면 시간이 지나면서 columnstore 인덱스 품질이 향상됩니다. columnstore 용어 및 개념에 대한 자세한 내용은 columnstore 인덱스: 개요를 참조하세요.

전체 테이블 대신 파티션 다시 빌드

인덱스가 큰 경우 전체 테이블을 다시 작성하려면 많은 시간이 소요되고 다시 작성 중에 전체 인덱스의 추가 복사본을 저장할 수 있는 충분한 디스크 공간이 필요합니다.

분할된 테이블의 경우 조각화가 일부 파티션(예: UPDATE, DELETE 또는 MERGE 문이 다수의 행에 영향을 주는 파티션)에만 있으면 전체 columnstore 인덱스를 다시 작성할 필요가 없습니다.

데이터를 로드하거나 수정한 후 파티션을 다시 작성하면 모든 데이터가 columnstore의 압축된 행 그룹에 저장됩니다. 데이터 로드 프로세스에서 102,400개보다 작은 일괄 처리를 사용하여 파티션에 데이터를 삽입하면 파티션은 델타 저장소에 열려 있는 여러 행 그룹으로 끝날 수 있습니다. 다시 작성을 수행하면 모든 델타 저장소 행이 columnstore의 압축된 행 그룹으로 이동됩니다.

Columnstore 인덱스 다시 구성 관련 고려 사항

columnstore 인덱스를 다시 구성하면 데이터베이스 엔진은 델타 저장소의 닫힌 각 행 그룹을 압축된 행 그룹으로 columnstore로 압축합니다. SQL Server 2016(13.x)부터 Azure SQL Database REORGANIZE 에서 이 명령은 온라인에서 다음과 같은 추가 조각 모음 최적화를 수행합니다.

  • 행의 10% 이상이 논리적으로 삭제된 경우 행 그룹에서 행을 물리적으로 제거합니다. 예를 들어 100만 행의 압축된 행 그룹에 100,000개의 행이 삭제된 경우 데이터베이스 엔진은 삭제된 행을 제거하고 행 그룹을 900,000개 행으로 다시 압축하여 스토리지 공간을 줄입니다.
  • 하나 이상의 압축된 행 그룹을 결합하여 행 그룹당 행 수를 최대 1,048,576개로 증가시킵니다. 예를 들어, 각각 행이 102,400개인 배치 5개를 대량 삽입하면 압축된 행 그룹이 5개 생성됩니다. REORGANIZE를 실행하면 이들 행 그룹은 행이 512,000개인 하나의 압축된 행 그룹으로 병합됩니다. 이때 사전 크기 또는 메모리 제한이 없는 것으로 가정합니다.
  • 데이터베이스 엔진은 행의 10% 이상이 다른 행 그룹과 함께 삭제된 것으로 표시된 행 그룹을 결합하려고 시도합니다. 예를 들어, 행 그룹 1은 압축되어 있고 행이 500,000개 있는 반면, 행 그룹 21은 압축되어 있고 행이 1,048,576개 있습니다. 행 그룹 21은 행의 60%가 삭제된 것으로 표시되어 있으며 따라서 행이 409,830개 남습니다. 데이터베이스 엔진은 이러한 두 행 그룹을 결합하여 909,830개의 행이 있는 새 행 그룹을 압축하는 것을 선호합니다.

데이터 로드를 수행하면 델타 저장소에 여러 개의 작은 행 그룹을 포함할 수 있습니다. ALTER INDEX REORGANIZE를 사용하면 이러한 행 그룹을 columnstore에 강제로 넣은 다음, 작은 압축된 행 그룹을 큰 압축된 행 그룹으로 결합할 수 있습니다. 또한, 다시 구성 작업은 columnstore에서 삭제된 것으로 표시된 행을 제거합니다.

참고

Management Studio를 사용하여 columnstore 인덱스를 다시 구성하면 압축된 행 그룹이 결합되지만 모든 행 그룹을 columnstore로 압축하도록 강제하지는 않습니다. 닫힌 행 그룹은 압축되지만 열린 행 그룹은 columnstore로 압축되지 않습니다. 모든 행 그룹을 강제로 압축하려면 다음을 포함하는 COMPRESS_ALL_ROW_GROUPS = ONTransact-SQL 예제를 사용합니다.

인덱스 유지 관리를 수행하기 전에 고려해야 할 사항

인덱스를 다시 구성하거나 다시 작성하여 수행되는 인덱스 유지 관리에는 리소스가 많이 사용됩니다. 이로 인해 CPU 사용률, 사용된 메모리 및 스토리지 I/O가 크게 증가합니다. 하지만 이것이 제공하는 이점은 매우 중요한 것부터 아주 소소한 것까지 데이터베이스 워크로드 및 기타 요인에 따라 다양합니다.

쿼리 워크로드에 나쁜 영향을 줄 수 있는 불필요한 리소스 사용을 방지하려면 인덱스 유지 관리를 무계획적으로 수행하지 않는 것이 좋습니다. 대신, 인덱스 유지 관리로 인한 성능 이점은 권장 전략을 사용하고 각 워크로드에 대한 경험에 기반하여 결정해야 하고 이러한 이점을 달성하는 데 필요한 리소스 비용 및 워크로드에 미치는 영향과 비교하여 평가해야 합니다.

인덱스 다시 구성 또는 다시 작성을 통해 성능 이점을 볼 가능성은 인덱스가 심하게 조각화되었거나 페이지 밀도가 낮은 경우에 더 높습니다. 하지만 고려할 사항은 이 외에도 많습니다. 쿼리 패턴(트랜잭션 처리 대비 분석 및 보고), 스토리지 하위 시스템 동작, 사용 가능한 메모리, 시간 경과에 따른 데이터베이스 엔진 개선과 같은 요소를 모두 고려해야 합니다.

중요

인덱스 유지 관리 의사 결정은 유지 관리의 리소스 비용을 비롯한 각 워크로드의 특정 상황에서 여러 요소를 고려한 후에 내려져야 합니다. 고정된 조각화 또는 페이지 밀도 임계값만을 기반으로 해서는 안 됩니다.

인덱스 다시 작성의 긍정적인 부작용

인덱스를 다시 작성한 후 고객이 성능 향상을 알아채는 경우가 많습니다. 그러나 대부분의 경우 이러한 성능 향상은 조각화를 줄이거나 페이지 밀도를 높이는 것과는 관련이 없습니다.

인덱스 다시 작성에는 중요한 이점이 있습니다. 인덱스의 모든 행이 검사되어 인덱스의 키 열에 대한 통계가 업데이트됩니다. 이것은 UPDATE STATISTICS ... WITH FULLSCAN을 실행하는 것과 동일하여, 통계가 최신 상태가 되고 기본 샘플링된 통계 업데이트에 비해 품질이 향상되는 경우도 있습니다. 통계가 업데이트되면 이것을 참조하는 쿼리 계획이 다시 컴파일됩니다. 오래된 통계, 불충분한 통계 샘플링 비율 또는 기타 이유로 인해 이전 쿼리 계획이 최적의 상태가 아니면, 다시 컴파일된 계획이 더 나은 성능을 발휘하는 경우가 많습니다.

인덱스를 다시 작성했기 때문에 성능이 향상되었다고 잘못 인식하여, 조각화 감소 및 페이지 밀도 증가가 그 결과라고 생각하는 경우가 많습니다. 실제로는 인덱스를 다시 작성하는 대신 통계를 업데이트하면 훨씬 저렴한 리소스 비용으로 동일한 이점을 얻을 수 있습니다.

통계 업데이트에 드는 리소스 비용은 인덱스를 다시 작성하는 것보다 적으며 몇 분 내에 작업이 완료되는 경우가 많습니다. 인덱스 다시 작성에는 몇 시간이 필요할 수도 있습니다.

인덱스 유지 관리 전략

다음과 같은 인덱스 유지 관리 전략을 고려하고 채택하는 것이 좋습니다.

  • 인덱스 유지 관리로 인해 워크로드가 항상 눈에 띄게 향상될 것이라고 가정하지 않습니다.
  • 인덱스 다시 구성 또는 다시 작성이 워크로드의 쿼리 성능에 미치는 영향을 명확하게 측정합니다. 쿼리 저장소는 A/B 테스트 기법을 사용하여 "유지 관리 전"과 "유지 관리 후" 성능을 측정하기 좋은 방법입니다.
  • 인덱스를 다시 작성하여 성능이 향상된 것이 보이면 통계를 업데이트하여 대체해봅니다. 유사하게 성능이 향상될 수 있습니다. 그렇다면 인덱스를 자주(또는 전혀) 다시 작성할 필요가 없고, 대신 통계 업데이트를 정기적으로 수행하면 됩니다. 일부 통계의 경우 WITH SAMPLE ... PERCENT 또는 WITH FULLSCAN 절을 사용하여 샘플링 비율을 늘려야 할 수도 있습니다(일반적이지 않음).
  • 인덱스 조각화 및 페이지 밀도를 시간별로 모니터링하여 이러한 값의 상승 또는 하락 추세와 쿼리 성능 사이에 상관 관계가 있는지 확인합니다. 조각화 수준이 높아지거나 페이지 밀도가 낮아져서 허용할 수 없을 만큼 저하되는 경우에는 인덱스를 다시 구성하거나 다시 작성합니다. 성능이 저하된 쿼리에 사용되는 특정 인덱스만 다시 구성하거나 다시 작성하는 것으로 충분한 경우도 많습니다. 이렇게 하면 데이터베이스의 모든 인덱스를 유지 관리하여 리소스 비용이 높아지는 것을 피할 수 있습니다.
  • 조각화/페이지 밀도와 성능 사이의 상관 관계를 설정하여 인덱스 유지 관리 빈도를 결정할 수도 있습니다. 유지 관리가 정해진 일정에 따라 수행되어야 한다고 가정하지 마십시오. 조각화 및 페이지 밀도를 모니터링하면서 성능이 허용할 수 없을 정도로 저하되기 전에 필요에 따라 인덱스 유지 관리를 실행하는 것이 더 좋은 전략입니다.
  • 인덱스 유지 관리가 필요하고 리소스 비용이 허용된다고 판단이 서면, 리소스 사용 패턴이 시간별로 달라질 수 있다는 점을 염두에 두고 리소스 사용량이 적은 시간 동안 유지 관리를 수행합니다.

Azure SQL 데이터베이스 및 Azure SQL Managed Instance 인덱스 유지 관리

위의 고려 사항 및 전략 외에도 Azure SQL Database 및 Azure SQL Managed Instance 인덱스 유지 관리의 비용과 이점을 고려하는 것이 특히 중요합니다. 유지 관리는 명백한 요구 사항이 있는 경우에만 다음 사항을 고려하여 수행해야 합니다.

  • Azure SQL 데이터베이스 및 Azure SQL Managed Instance 리소스 거버넌스를 구현하여 프로비전된 가격 책정 계층에 따라 CPU, 메모리 및 I/O 소비에 대한 범위를 설정합니다. 이러한 경계는 인덱스 유지 관리를 포함하여 모든 사용자 워크로드에 적용됩니다. 모든 워크로드의 누적 리소스 소비가 리소스 경계에 도달한 경우, 다시 작성 또는 다시 구성 작업을 수행하면 리소스 경합으로 인해 다른 워크로드의 성능이 저하될 수 있습니다. 예를 들어 동시 인덱스 다시 작성으로 인해 트랜잭션 로그 I/O가 100%가 되어 대량 데이터 로드가 느려질 수 있습니다. Azure SQL Managed Instance 인덱스 유지 관리 기간을 연장하지 않고 리소스 할당이 제한된 별도의 Resource Governor 워크로드 그룹에서 인덱스 유지 관리를 실행하여 이러한 영향을 줄일 수 있습니다.
  • 비용 절감을 위해 최소한의 리소스 여유 공간으로 데이터베이스, 탄력적 풀 및 관리되는 인스턴스를 프로비전하는 경우가 있습니다. 가격 책정 계층은 애플리케이션 워크로드에 충분하도록 선택됩니다. 애플리케이션 성능을 저하시키지 않으면서 인덱스 유지 관리로 인해 리소스 사용량이 상당히 증가하는 상황을 수용하려면, 더 많은 리소스를 프로비전하고 비용을 늘려야 할 수도 있으며, 이 경우 애플리케이션 성능이 반드시 개선되는 것은 아닙니다.
  • 탄력적 풀에서는 리소스가 풀의 모든 데이터베이스에서 공유됩니다. 특정 데이터베이스가 유휴 상태인 경우에도 이 데이터베이스에 대한 인덱스 유지 관리를 수행하면 동일한 풀의 다른 데이터베이스에서 동시에 실행되는 애플리케이션 워크로드에 영향을 줄 수 있습니다. 자세한 내용은 조밀한 탄력적 풀의 리소스 관리를 참조하세요.
  • Azure SQL Database 및 Azure SQL Managed Instance 사용되는 대부분의 스토리지 유형에 대해 순차 I/O와 임의 I/O 간의 성능 차이는 없습니다. 따라서 인덱스 조각화가 쿼리 성능에 미치는 영향이 적습니다.
  • 읽기 확장 또는 지역에서 복제 복제본을 사용하는 경우에는 주 복제본에 대한 인덱스 유지 관리를 수행하는 동안 복제본의 데이터 대기 시간이 증가하는 경우가 많습니다. 지역에서 복제가 인덱스 유지 관리로 인한 트랜잭션 로그 생성 증가를 견디기에 부족한 리소스로 프로비전되면, 주 복제본보다 훨씬 뒤쳐져서 초기값 재설정이 수행될 수 있습니다. 그러면 초기값 재설정이 완료될 때까지 복제본을 사용할 수 없게 됩니다. 또한 프리미엄 및 중요 비즈니스용 서비스 계층에서 고가용성을 위해 사용되는 복제본이 마찬가지로 인덱스 유지 관리 중에 주 복제본보다 훨씬 더 뒤쳐질 수 있습니다. 인덱스 유지 관리 중에 또는 직후에 장애 조치(failover)가 필요한 경우 예상보다 오래 걸릴 수 있습니다.
  • 주 복제본에서 인덱스 다시 작성이 실행되고 읽을 수 있는 복제본에서 장기 실행 쿼리가 동시에 실행되면, 복제본에 대한 다시 실행 스레드가 차단되지 않도록 자동으로 쿼리가 종료될 수 있습니다.

데이터베이스 및 Azure SQL Managed Instance Azure SQL 일회성 또는 주기적 인덱스 유지 관리가 필요할 수 있는 특정하지만 드문 시나리오가 있습니다.

  • 페이지 밀도를 높이고 데이터베이스에 사용되는 공간을 줄여서 가격 책정 계층의 크기 제한 내에서 유지하기 위해 인덱스 유지 관리가 필요할 수 있습니다. 이렇게 하면 크기 제한이 더 높은 가격 책정 계층으로 스케일 업할 필요가 없습니다.
  • 데이터 파일을 축소해야 하는 경우 파일을 축소하기 전에 인덱스를 다시 작성하거나 다시 구성하면 페이지 밀도가 높아집니다. 이렇게 하면 축소 작업이 더 빨라집니다. 이동해야 하는 페이지가 줄어들기 때문입니다.

Azure SQL 데이터베이스 및 Azure SQL Managed Instance 워크로드에 인덱스 유지 관리가 필요하다고 판단한 경우 인덱스를 다시 구성하거나 온라인 인덱스를 다시 빌드해야 합니다. 그러면 인덱스가 다시 작성되는 동안 쿼리 워크로드가 테이블에 액세스할 수 있습니다.

또한 작업을 다시 시작이 가능하도록 만들면 계획하거나 계획하지 않은 데이터베이스 장애 조치(failover)로 인해 작업이 중단된 경우 처음부터 다시 시작하지 않아도 됩니다. 인덱스가 큰 경우에는 다시 시작 가능한 인덱스 작업을 사용하는 것이 특히 중요합니다.

오프라인 인덱스 작업은 일반적으로 온라인 작업보다 더 빨리 완료됩니다. 작업 중에 쿼리가 테이블에 액세스하지 않는 경우(예: 순차 ETL 프로세스의 일부로 준비 테이블에 데이터를 로드한 후) 사용해야 합니다.

제한 사항

128 익스텐트가 넘는 Rowstore 인덱스는 논리적 단계와 물리적 단계로 나누어 다시 빌드합니다. 논리적 단계에서는 인덱스에 의해 사용되는 기존 할당 단위가 할당 취소 상태로 표시되며 데이터 행이 복사되어 정렬된 후 다시 작성된 인덱스를 저장하기 위해 생성된 새 할당 단위로 옮겨집니다. 물리적 단계에서는 이전에 할당 취소 상태로 표시된 할당 단위가 백그라운드로 실행되는 짧은 트랜잭션을 통해 물리적으로 삭제됩니다. 이 단계는 잠금을 많이 필요로 하지 않습니다. 할당 단위에 대한 자세한 내용은 페이지 및 익스텐트 아키텍처 가이드를 참조하세요.

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.

ALLOW_PAGE_LOCKS가 OFF로 설정되면 인덱스를 다시 구성할 수 없습니다.

2017년 SQL Server(14.x)까지 클러스터형 columnstore 인덱스 다시 빌드는 오프라인 작업입니다. 데이터베이스 엔진은 다시 빌드가 발생하는 동안 테이블 또는 파티션에 대한 배타적 잠금을 획득해야 합니다. 데이터는 오프라인 상태이며 NOLOCK, RCSI(읽기 커밋된 스냅숏 격리) 또는 스냅숏 격리를 사용하는 경우에도 다시 빌드하는 동안에는 사용할 수 없습니다. SQL Server 2019(15.x)부터 이 옵션을 사용하여 ONLINE = ON 클러스터형 columnstore 인덱스를 다시 작성할 수 있습니다.

경고

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

통계 제한 사항

  • 인덱스를 만들거나 다시 작성하는 경우, 테이블의 모든 행을 검색하여 통계가 생성되거나 업데이트되며, 이것은 CREATE STATISTICS 또는 UPDATE STATISTICS에서 FULLSCAN 절을 사용하는 것과 같습니다. 그러나 SQL Server 2012(11.x)부터 분할된 인덱스를 만들거나 다시 작성하면 테이블의 모든 행을 검사하여 통계가 생성되거나 업데이트되지 않습니다. 대신 기본 샘플링 비율이 사용됩니다. 테이블의 모든 행을 검색하여 분할된 인덱스에 대한 통계를 생성하거나 업데이트하려면 FULLSCAN 절에서 CREATE STATISTICS 또는 UPDATE STATISTICS를 사용합니다.
  • 마찬가지로 인덱스 생성 또는 다시 작성 작업을 다시 시작할 수 있으면 기본 샘플링 비율로 통계가 생성되거나 업데이트됩니다. PERSIST_SAMPLE_PERCENT 절이 ON으로 설정된 상태에서 통계가 생성되었거나 마지막으로 업데이트된 경우, 다시 시작 가능한 인덱스 작업은 지속된 샘플링 비율을 사용하여 통계를 생성하거나 업데이트합니다.
  • 인덱스를 재구성하는 경우에는 통계가 업데이트되지 않습니다.

Transact-SQL을 사용하여 rowstore 인덱스의 조각화 및 페이지 밀도를 확인하려면

다음 예는 현재 데이터베이스의 모든 rowstore 인덱스에 대한 평균 조각화 및 페이지 밀도를 확인합니다. SAMPLED 모드를 사용하여 실행 가능한 결과를 신속하게 반환합니다. 더 정확한 결과를 얻으려면 DETAILED 모드를 사용합니다. 그러려면 모든 인덱스 페이지를 검색해야 해서 시간이 오래 걸릴 수 있습니다.

SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
       OBJECT_NAME(ips.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       ips.avg_fragmentation_in_percent,
       ips.avg_page_space_used_in_percent,
       ips.page_count,
       ips.alloc_unit_type_desc
FROM sys.dm_db_index_physical_stats(DB_ID(), default, default, default, 'SAMPLED') AS ips
INNER JOIN sys.indexes AS i 
ON ips.object_id = i.object_id
   AND
   ips.index_id = i.index_id
ORDER BY page_count DESC;

이전 명령문은 다음과 비슷한 결과 집합을 반환합니다.

schema_name  object_name           index_name                               index_type    avg_fragmentation_in_percent avg_page_space_used_in_percent page_count  alloc_unit_type_desc
------------ --------------------- ---------------------------------------- ------------- ---------------------------- ------------------------------ ----------- --------------------
dbo          FactProductInventory  PK_FactProductInventory                  CLUSTERED     0.390015600624025            99.7244625648629               3846        IN_ROW_DATA
dbo          DimProduct            PK_DimProduct_ProductKey                 CLUSTERED     0                            89.6839757845318               497         LOB_DATA
dbo          DimProduct            PK_DimProduct_ProductKey                 CLUSTERED     0                            80.7132814430442               251         IN_ROW_DATA
dbo          FactFinance           NULL                                     HEAP          0                            99.7982456140351               239         IN_ROW_DATA
dbo          ProspectiveBuyer      PK_ProspectiveBuyer_ProspectiveBuyerKey  CLUSTERED     0                            98.1086236718557               79          IN_ROW_DATA
dbo          DimCustomer           IX_DimCustomer_CustomerAlternateKey      NONCLUSTERED  0                            99.5197553743514               78          IN_ROW_DATA

자세한 내용은 sys.dm_db_index_physical_stats를 참조하세요.

Transact-SQL을 사용하여 columnstore 인덱스의 조각화 확인

다음 예는 현재 데이터베이스에서 압축된 행 그룹이 있는 모든 columnstore 인덱스의 평균 조각화를 확인합니다.

SELECT OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
       OBJECT_NAME(i.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       100.0 * (ISNULL(SUM(rgs.deleted_rows), 0)) / NULLIF(SUM(rgs.total_rows), 0) AS avg_fragmentation_in_percent
FROM sys.indexes AS i
INNER JOIN sys.dm_db_column_store_row_group_physical_stats AS rgs
ON i.object_id = rgs.object_id
   AND
   i.index_id = rgs.index_id
WHERE rgs.state_desc = 'COMPRESSED'
GROUP BY i.object_id, i.index_id, i.name, i.type_desc
ORDER BY schema_name, object_name, index_name, index_type;

이전 명령문은 다음과 비슷한 결과 집합을 반환합니다.

schema_name  object_name            index_name                           index_type                avg_fragmentation_in_percent
------------ ---------------------- ------------------------------------ ------------------------- ----------------------------
Sales        InvoiceLines           NCCX_Sales_InvoiceLines              NONCLUSTERED COLUMNSTORE  0.000000000000000
Sales        OrderLines             NCCX_Sales_OrderLines                NONCLUSTERED COLUMNSTORE  0.000000000000000
Warehouse    StockItemTransactions  CCX_Warehouse_StockItemTransactions  CLUSTERED COLUMNSTORE     4.225346161484279

SQL Server Management Studio 사용하여 인덱스 유지 관리

인덱스를 다시 구성하거나 다시 작성하려면

  1. 개체 탐색기에서 인덱스를 다시 구성할 테이블이 포함된 데이터베이스를 확장합니다.
  2. 테이블 폴더를 확장합니다.
  3. 인덱스를 다시 구성할 테이블을 확장합니다.
  4. 인덱스 폴더를 확장합니다.
  5. 다시 구성할 인덱스를 마우스 오른쪽 단추로 클릭하고 다시 구성을 선택합니다.
  6. 인덱스 다시 구성 대화 상자에서 다시 구성할 인덱스 표에 올바른 인덱스가 있는지 확인한 다음 확인을 클릭합니다.
  7. 큰 개체 열 데이터 압축 확인란을 선택하여 LOB(Large Object) 데이터가 포함된 모든 페이지도 압축되도록 지정합니다.
  8. 확인.

테이블의 모든 인덱스를 다시 구성하려면

  1. 개체 탐색기에서 인덱스를 다시 구성할 테이블이 포함된 데이터베이스를 확장합니다.
  2. 테이블 폴더를 확장합니다.
  3. 인덱스를 다시 구성할 테이블을 확장합니다.
  4. 인덱스 폴더를 마우스 오른쪽 단추로 클릭하고 모두 다시 구성을 선택합니다.
  5. 인덱스 다시 구성 대화 상자에서 다시 구성할 인덱스에 올바른 인덱스가 있는지 확인합니다. 다시 구성할 인덱스 표에서 인덱스를 제거하려면 인덱스를 선택한 다음 Delete 키를 누릅니다.
  6. 큰 개체 열 데이터 압축 확인란을 선택하여 LOB(Large Object) 데이터가 포함된 모든 페이지도 압축되도록 지정합니다.
  7. 확인.

Transact-SQL을 사용하여 인덱스 유지 관리

참고

Transact-SQL을 사용하여 인덱스를 다시 빌드하거나 재구성하는 방법에 대한 자세한 예제는 ALTER INDEX 예제 - Rowstore 인덱스ALTER INDEX 예제 - Columnstore 인덱스를 참조하세요.

특정 인덱스를 다시 구성하려면

다음 예에서는 AdventureWorks2016 데이터베이스에서 HumanResources.Employee 테이블의 IX_Employee_OrganizationalLevel_OrganizationalNode 인덱스를 다시 구성합니다.

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode
    ON HumanResources.Employee
    REORGANIZE;

다음 예에서는 AdventureWorksDW2016 데이터베이스의 dbo.FactResellerSalesXL_CCI 테이블에서 IndFactResellerSalesXL_CCI columnstore 인덱스를 다시 구성합니다.

-- This command will force all closed and open row groups into columnstore.
ALTER INDEX IndFactResellerSalesXL_CCI
    ON FactResellerSalesXL_CCI
    REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

테이블의 모든 인덱스를 다시 구성하려면

다음 예에서는 AdventureWorks2016 데이터베이스의 HumanResources.Employee 테이블에 있는 모든 인덱스를 다시 구성합니다.

ALTER INDEX ALL ON HumanResources.Employee
   REORGANIZE;

인덱스를 다시 작성하려면

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

ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD
;

테이블에서 모든 인덱스를 다시 작성하려면

다음 예에서는 ALL 키워드를 사용하여 AdventureWorks2016 데이터베이스의 테이블과 연결된 모든 인덱스를 다시 빌드합니다. 3개의 옵션이 지정됩니다.

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

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

참고 항목