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

적용 대상:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAnalytics 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 및 Azure SQL 인덱스 아키텍처 및 디자인 가이드를 참조 하세요.

  • 데이터베이스 엔진 기본 데이터에 대한 삽입, 업데이트 또는 삭제 작업이 수행 될 때마다 인덱스를 자동으로 수정합니다. 예를 들어 테이블에 행을 추가하면 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 이외의 값으로 설정하지 않는 것이 좋습니다.

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

조각화 및 페이지 밀도는 인덱스 기본 테넌트 수행 여부와 사용할 기본 테넌트 메서드를 결정할 때 고려해야 할 요소 중 하나입니다.

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

반환된 sys.dm_db_index_physical_stats 결과 집합에는 다음 열이 포함됩니다.

설명
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 결과 집합에는 다음 열이 포함됩니다.

설명
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 Database 및 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(10.0.x)부터 비클러스터형 인덱스 오프라인을 다시 빌드하여 비클러스터형 인덱스에서 이러한 불일치를 복구할 수 있습니다. 그러나 온라인 다시 빌드 메커니즘은 기존 비클러스터형 인덱스를 다시 빌드의 기준으로 사용하므로 비클러스터형 인덱스를 온라인으로 다시 작성하여 비클러스터형 인덱스 불일치를 복구할 수 없습니다. 인덱스를 오프라인으로 다시 빌드하면 클러스터형 인덱스(또는 힙)를 강제로 검사하여 비클러스터형 인덱스의 일관되지 않은 데이터를 클러스터형 인덱스 또는 힙의 데이터로 바꿀 수 있습니다.

클러스터형 인덱스 또는 힙이 데이터의 원본으로 사용되는지 확인하려면 비클러스터형 인덱스는 다시 빌드하는 대신 삭제하고 다시 만듭니다. 이전 버전과 마찬가지로 백업에서 영향을 받는 데이터를 복원하여 불일치에서 복구하는 것이 좋습니다. 그러나 오프라인으로 다시 빌드하거나 다시 만들어 비클러스터형 인덱스 불일치를 복구할 수 있습니다. 자세한 내용은 DBCC CHECKDB(Transact-SQL)를 참조하세요

자동 인덱스 및 통계 관리

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

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

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

  • 를 사용하여 다른 키를 사용하여 클러스터형 인덱스 다시 만들기를 포함하여 테이블에 클러스터형 인덱스 만들기 CREATE CLUSTERED INDEX ... WITH (DROP_EXISTING = ON)
  • 클러스터형 인덱스 삭제로 인해 테이블이 힙으로 저장됩니다.

다음 시나리오에서는 동일한 테이블에 있는 모든 rowstore 비클러스터형 인덱스를 자동으로 다시 빌드하지 않습니다.

  • 클러스터형 인덱스 다시 작성
  • 분할 구성표 적용 또는 클러스터형 인덱스 다른 파일 그룹으로 이동 등 클러스터형 인덱스 스토리지 변경

Important

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

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

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

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

columnstore 인덱스 다시 작성과 관련된 고려 사항

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

참고 항목

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

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

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

분할된 테이블의 경우 조각화가 일부 파티션에만 있는 경우(예: 많은 수의 행에 영향을 받은 UPDATEDELETEMERGE 파티션) 전체 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개의 행이 있는 새 행 그룹을 압축하는 것이 좋습니다.

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

참고 항목

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

인덱스 기본 테넌스를 수행하기 전에 고려해야 할 사항

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

쿼리 워크로드에 해로울 수 있는 불필요한 리소스 사용률을 방지하기 위해 Microsoft는 인덱스 기본 무차별적으로 수행하지 않는 것이 좋습니다. 대신 인덱스 기본 테넌트의 성능 이점은 권장 전략을 사용하여 각 워크로드에 대해 경험적으로 결정되어야 하며, 이러한 이점을 달성하는 데 필요한 리소스 비용 및 워크로드 영향과 비교할 수 있습니다.

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

Important

기본 테넌스의 리소스 비용을 포함하여 각 워크로드의 특정 컨텍스트에서 여러 요인을 고려한 후 인덱스 기본 테넌트 결정을 내려야 합니다. 고정된 조각화 또는 페이지 밀도 임계값만을 기반으로 해서는 안 됩니다.

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

고객은 종종 인덱스를 다시 빌드한 후 성능 향상을 관찰합니다. 그러나 대부분의 경우 이러한 개선 사항은 조각화를 줄이거나 페이지 밀도를 높이는 방법과 관련이 없습니다.

인덱스 재구축에는 인덱스의 모든 행을 검사하여 인덱스의 키 열에 대한 통계를 업데이트하는 중요한 이점이 있습니다. 이는 통계를 UPDATE STATISTICS ... WITH FULLSCAN최신 상태로 만들고 경우에 따라 기본 샘플링된 통계 업데이트에 비해 품질이 향상되는 실행과 동일합니다. 통계가 업데이트되면 통계를 참조하는 쿼리 계획이 다시 컴파일됩니다. 부실 통계, 통계 샘플링 비율이 부족하거나 다른 이유로 인해 쿼리에 대한 이전 계획이 최적이 아닌 경우 다시 컴파일된 계획이 더 잘 수행되는 경우가 많습니다.

고객은 종종 이러한 개선 사항을 인덱스 다시 작성 자체에 잘못 기인하여 조각화 감소 및 페이지 밀도 증가의 결과로 간주합니다. 실제로 인덱스를 다시 작성하는 대신 통계를 업데이트하여 훨씬 저렴한 리소스 비용으로 동일한 이점을 얻을 수 있습니다.

통계 업데이트의 리소스 비용은 인덱스 다시 작성에 비해 미소하며, 인덱스 다시 작성에 필요할 수 있는 시간 대신 몇 분 안에 작업이 완료되는 경우가 많습니다.

인덱스 유지 관리 전략

Microsoft는 고객이 다음 인덱스 기본 테넌트 전략을 고려하고 채택할 것을 권장합니다.

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

Azure SQL Database 및 Azure SQL Managed Instance의 인덱스 기본 테넌트

위의 고려 사항 및 전략 외에도 Azure SQL Database 및 Azure SQL Managed Instance에서는 인덱스 기본 테넌스의 비용과 이점을 고려하는 것이 특히 중요합니다. 고객은 입증된 필요성이 있는 경우에만 이를 수행해야 하며 다음 사항을 고려해야 합니다.

  • Azure SQL Database 및 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 간에 성능에 차이가 없습니다. 이렇게 하면 인덱스 조각화가 쿼리 성능에 미치는 영향을 줄일 수 있습니다.
  • 읽기 확장 또는 지역에서 복제 복제본을 사용하는 경우에는 주 복제본에 대한 인덱스 유지 관리를 수행하는 동안 복제본의 데이터 대기 시간이 증가하는 경우가 많습니다. 지역 복제본(replica) 인덱스 기본 테넌스로 인한 트랜잭션 로그 생성 증가를 유지하기 위해 리소스가 부족하여 프로비전되는 경우 주 데이터베이스보다 훨씬 뒤쳐져 시스템이 다시 시동을 일으킬 수 있습니다. 그러면 초기값 재설정이 완료될 때까지 복제본을 사용할 수 없게 됩니다. 또한 프리미엄 및 중요 비즈니스용 서비스 계층에서 고가용성을 위해 사용되는 복제본(replica) 마찬가지로 인덱스 기본 테넌트 동안 기본 계층보다 훨씬 뒤처질 수 있습니다. 인덱스 기본 테넌트 도중 또는 그 직후에 장애 조치(failover)가 필요한 경우 예상보다 오래 걸릴 수 있습니다.
  • 주 복제본에서 인덱스 다시 작성이 실행되고 읽을 수 있는 복제본에서 장기 실행 쿼리가 동시에 실행되면, 복제본에 대한 다시 실행 스레드가 차단되지 않도록 자동으로 쿼리가 종료될 수 있습니다.

Azure SQL Database 및 Azure SQL Managed Instance에서 일회성 또는 주기적 인덱스 기본 테넌스가 필요할 수 있는 특정하지만 드문 시나리오가 있습니다.

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

Azure SQL Database 및 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 가 발생할 수 있습니다.

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

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

Warning

파티션 수가 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을 사용하여 인덱스 유지 관리

인덱스 다시 구성

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

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode
    ON HumanResources.Employee
    REORGANIZE;

다음 예제에서는 데이터베이스의 IndFactResellerSalesXL_CCI 테이블에 AdventureWorksDW2022 있는 dbo.FactResellerSalesXL_CCI columnstore 인덱스를 다시 구성합니다. 이 명령은 모든 닫힌 행 및 열린 행 그룹을 columnstore로 강제 적용합니다.

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

테이블의 모든 인덱스 다시 구성

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

ALTER INDEX ALL ON HumanResources.Employee
   REORGANIZE;

인덱스 다시 작성

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

ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD
;

테이블의 모든 인덱스 다시 작성

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

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

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

다음 단계