데이터베이스 유지 관리 검사 탐색

  • 7분

쿼리 최적화 프로그램은 인덱스의 통계 정보를 활용하여 최적의 실행 계획을 작성합니다.

백업 및 무결성 검사와 같은 Azure SQL 유지 관리 작업이 자동으로 처리되며 통계를 최신 상태로 유지하는 자동 업데이트를 사용할 수 있지만 충분하지 않은 경우도 있습니다.

정상 인덱스와 통계가 있으면 최적의 효율성으로 지정된 계획을 수행할 수 있습니다. 인덱스 유지 관리는 시간이 지남에 따라 데이터베이스의 데이터가 변경되므로 정기적으로 수행해야 합니다. 데이터 수정 빈도에 따라 인덱스 유지 관리 전략을 변경할 수 있습니다.

다시 빌드 및 재구성

인덱스 페이지 내의 논리적 순서가 물리적 순서와 일치하지 않으면 인덱스 조각화가 발생합니다. UPDATE, DELETE, INSERT와 같은 일상적인 데이터 수정 명령문 중에 페이지 순서가 잘못될 수 있습니다. 조각화를 수행하면 인덱스 페이지에서 포인터가 참조하는 데이터를 찾는 데 추가 I/O가 필요하므로 성능 문제가 발생할 수 있습니다.

인덱스에서 데이터를 삽입, 업데이트 및 삭제하면 인덱스의 논리 순서가 더 이상 페이지 내 및 페이지 사이의 물리적 순서와 일치하지 않으므로 인덱스를 구성하게 됩니다. 또한 시간이 지남에 따라 데이터 수정으로 인해 데이터가 데이터베이스에서 분산되거나 조각화될 수 있습니다. 이 조각화는 필요한 데이터를 찾기 위해 데이터베이스 엔진이 추가 페이지를 읽어야 할 때 쿼리 성능을 저하시킬 수 있습니다.

인덱스 재구성은 인덱스의 리프 수준(클러스터형과 비클러스터형 모두)을 조각 모음하는 온라인 작업입니다. 이 조각 모음 프로세스는 왼쪽에서 오른쪽으로 노드의 논리적 순서와 일치하도록 리프 수준 페이지를 물리적으로 다시 정렬합니다. 이 프로세스 중에는 구성된 fillfactor 값을 기준으로 인덱스 페이지도 압축합니다.

실행되는 명령 또는 사용 중인 SQL Server의 버전에 따라 온라인 또는 오프라인으로 다시 빌드할 수 있습니다. 오프라인 다시 빌드 프로세스에서는 인덱스 자체를 삭제한 후 다시 생성합니다. 온라인으로 다시 빌드할 수 있으면 기존 인덱스와 병렬로 새 인덱스를 빌드합니다. 새 인덱스를 빌드하고 나면 기존 인덱스를 삭제한 다음 기존 인덱스 이름과 일치하도록 새 인덱스의 이름을 변경합니다. 새 인덱스는 기존 인덱스와 병렬로 빌드되기 때문에 온라인 버전에는 추가 공간이 필요합니다.

인덱스 유지 관리에 관한 일반적인 지침은 다음과 같습니다.

  • > 5%, < 30% - 인덱스를 재구성합니다.

  • > 30% - 인덱스를 다시 빌드합니다.

이러한 숫자를 일반적인 권장 사항으로 사용합니다. 워크로드와 데이터에 따라 더 적극적으로 유지 관리해야 할 수도 있습니다. 또는 주로 특정 페이지를 검색하는 쿼리를 수행하는 데이터베이스의 경우에는 인덱스 유지 관리를 지연시킬 수도 있습니다.

SQL Server 및 Azure SQL 플랫폼은 개체의 조각화를 검색할 수 있는 DMV를 제공합니다. 이러한 용도로 가장 일반적으로 사용되는 DMV는 b-트리 인덱스의 경우 sys.dm_db_index_physical_stats이며, columnstore 인덱스의 경우 sys.dm_db_column_store_row_group_physical_stats입니다.

한 가지 기억할 점은 인덱스를 다시 빌드하면 인덱스에 대한 통계가 업데이트되어 성능이 향상될 수 있다는 것입니다. 인덱스 재구성은 통계를 업데이트하지 않습니다.

Microsoft는 SQL Server 2017을 사용하여 다시 시작 가능한 인덱스 다시 빌드 작업을 도입했습니다. 다시 시작 가능한 인덱스 다시 빌드 옵션은 다시 빌드 작업이 지정된 인스턴스에 부과할 수 있는 시간을 더 유연하게 제어할 수 있습니다. SQL Server 2019를 사용하면 연결된 최대 병렬 처리 수준을 제어하는 기능을 통해 데이터베이스 관리자가 더 세분화하여 제어할 수 있습니다.

통계

Azure SQL에서 성능 튜닝을 수행할 때 통계의 중요도를 이해하는 것이 중요합니다.

통계는 BLOB(Binary Large Object)으로 사용자 데이터베이스에 저장됩니다. 이 BLOB에는 테이블이나 인덱싱된 뷰에서 하나 이상의 열에 있는 데이터 값의 분포에 관한 통계 정보가 포함되어 있습니다.

통계는 열에 있는 데이터 값의 분포에 관한 정보를 포함합니다. 쿼리 최적화 프로그램은 열과 인덱스 통계를 사용하여 쿼리가 반환할 행 수인 카디널리티를 결정합니다.

그런 다음 쿼리 최적화 프로그램에서 카디널리티 예상치를 사용하여 실행 계획을 생성합니다. 카디널리티 예상치를 사용하면 최적화 프로그램에서 요청된 데이터를 검색하는 데 사용할 작업 유형(예: 인덱스 검색 또는 스캔)도 결정할 수 있습니다.

마지막으로 업데이트된 날짜의 사용자 정의 통계 목록을 보려면 아래 쿼리를 실행합니다.

SELECT sp.stats_id, 
       name, 
       last_updated, 
       rows, 
       rows_sampled
FROM sys.stats
     CROSS APPLY sys.dm_db_stats_properties(object_id, stats_id) AS sp
WHERE user_created = 1

통계 만들기

AUTO_CREATE_STATISTICS 옵션이 ON으로 설정된 경우 쿼리 최적화 프로그램은 기본적으로 인덱싱된 열에 대한 통계를 만듭니다. 또한 쿼리 최적화 프로그램은 쿼리 조건자의 단일 열에 대한 통계를 작성합니다.

이러한 메서드는 대부분의 쿼리에 대해 고품질 쿼리 계획을 제공합니다. 특정 쿼리 계획을 개선하기 위해 CREATE STATISTICS 문을 사용하여 더 많은 통계를 만들어야 할 수도 있습니다.

쿼리 최적화 프로그램에서 쿼리 조건자 열에 대한 통계를 자동으로 만들 수 있도록 AUTO_CREATE_STATISTICS 옵션을 사용하도록 설정하는 것이 좋습니다.

다음과 같은 상황이 발생할 때마다 통계를 만드는 것이 좋습니다.

  • 데이터베이스 엔진 튜닝 관리자가 통계 작성을 제안하는 경우
  • 쿼리 조건자에 동일한 인덱스에 없는 관련된 여러 열이 포함된 경우
  • 쿼리가 데이터 하위 집합에서 선택하는 경우
  • 쿼리에 통계가 누락된 경우

유지 관리 작업 자동화

Azure SQL은 자동화를 위해 데이터베이스 유지 관리 작업을 수행하는 네이티브 도구를 제공합니다. 데이터베이스가 실행되는 플랫폼에 따라 다양한 도구를 사용할 수 있습니다.

Azure 가상 머신의 SQL Server

SQL 에이전트 또는 Windows 작업 스케줄러와 같은 예약 서비스에 액세스할 수 있습니다. 이러한 자동화 도구를 사용하면 인덱스 내의 조각화 크기를 최소로 유지할 수 있습니다. 데이터베이스가 큰 경우 최적의 성능을 보장하려면 인덱스 다시 빌드와 재구성 사이에 균형을 맞춰야 합니다. SQL 에이전트 또는 작업 스케줄러에서 제공하는 유연성을 사용하여 사용자 지정 작업을 실행할 수 있습니다.

Azure SQL Database

Azure SQL Database의 특성상 SQL Server 에이전트 또는 Windows 작업 스케줄러에 액세스할 수 없습니다. 이러한 서비스가 없으면 다른 방법을 사용하여 인덱스 유지 관리를 만들어야 합니다. SQL Database에 대한 유지 관리 작업을 관리하는 방법은 다음 세 가지가 있습니다.

  • Azure Automation Runbook

  • Azure 가상 머신의 SQL Server에서 SQL 에이전트 작업(원격 호출)

  • Azure SQL 탄력적 작업

Azure SQL Managed Instance

Azure 가상 머신의 SQL Server와 마찬가지로 SQL Server 에이전트를 통해 Azure SQL Managed Instance에서 작업을 예약할 수 있습니다. SQL Server 에이전트를 사용하면 데이터베이스의 인덱스에서 조각화를 줄이기 위해 설계된 코드를 유연하게 실행할 수 있습니다.