SQL 하이퍼스케일 성능 문제 해결 진단

  • 아티클

적용 대상:Azure SQL Database

하이퍼스케일 데이터베이스의 성능 문제를 해결하기 위한 성능 조사의 시작점은 Azure SQL Database 컴퓨팅 노드의 일반적인 성능 튜닝 방법론입니다. 그러나 하이퍼스케일의 분산 아키텍처를 고려하여 지원을 위해 추가 진단이 추가되었습니다. 이 문서는 하이퍼스케일 전용 진단 데이터를 설명합니다.

로그 비율 제한 대기

모든 Azure SQL Database 서비스 목표에는 로그 속도 거버넌스를 통해 적용되는 로그 생성 속도 제한이 존재합니다. 하이퍼스케일에서 로그 거버넌스 제한은 서비스 수준과 관계없이 105MB/초로 설정합니다. 이 값은 sys.dm_user_db_resource_governance의 primary_max_log_rate 열에 노출됩니다.

그러나 복구 가능성 SLA를 유지하기 위해 기본 컴퓨팅 복제본의 로그 생성 속도를 제한해야 하는 경우가 있습니다. 이 제한은 페이지 서버 또는 다른 컴퓨팅 복제본이 로그 서비스의 새 로그 레코드를 적용하는 데 크게 뒤처진 경우에 발생합니다. 페이지 서버 또는 복제본이 뒤처지지 않은 경우 제한 메커니즘을 사용하면 로그 생성 속도가 100MB/초에 도달할 수 있습니다. 이는 모든 하이퍼스케일 서비스 목표에서 효과적인 최대 로그 생성 속도입니다.

다음 대기 유형(sys.dm_os_wait_stats에서)은 기본 컴퓨팅 복제본에서 로그 속도를 제한할 수 있는 이유를 설명합니다.

대기 유형 설명
RBIO_RG_STORAGE 하이퍼스케일 데이터베이스 기본 컴퓨팅 노드 로그 생성 속도가 페이지 서비스에서 지연된 로그 사용으로 인해 제한될 때 발생합니다.
RBIO_RG_DESTAGE 하이퍼스케일 데이터베이스 컴퓨팅 노드 로그 생성 속도가 장기 로그 스토리지의 지연된 로그 사용으로 인해 제한될 때 발생합니다.
RBIO_RG_REPLICA 하이퍼스케일 데이터베이스 컴퓨팅 노드 로그 생성 속도가 읽기 가능한 보조 복제본의 지연된 로그 사용으로 인해 제한될 때 발생합니다.
RBIO_RG_GEOREPLICA 하이퍼스케일 데이터베이스 컴퓨팅 노드 로그 생성 속도가 지역 보조 복제본의 지연된 로그 사용으로 인해 제한될 때 발생합니다.
RBIO_RG_LOCALDESTAGE 하이퍼스케일 데이터베이스 컴퓨팅 노드 로그 생성 속도가 로그 서비스의 지연된 로그 사용으로 인해 제한될 때 발생합니다.

페이지 서버 읽기

컴퓨팅 복제본은 데이터베이스의 전체 복사본을 로컬에서 캐시하지 않습니다. 컴퓨팅 복제본의 로컬 데이터는 버퍼 풀(메모리 내) 및 데이터 페이지의 일부(비포함) 캐시인 로컬 RBPEX(복원 버퍼 풀 확장) 캐시에 저장됩니다. 이 로컬 RBPEX 캐시는 컴퓨팅 크기에 비례해 크기를 조정하며 컴퓨팅 계층 메모리의 3배입니다. RBPEX는 가장 자주 액세스되는 데이터를 포함한다는 점에서 버퍼 풀과 비슷합니다. 반면 각 페이지 서버에는 유지 관리하는 데이터베이스의 일부에 대한 RBPEX 캐시가 포함됩니다.

컴퓨팅 복제본 읽기를 실행했을 때 버퍼 풀 또는 로컬 RBPEX 캐시에 데이터가 없으면 getPage(pageId, LSN) 함수 호출을 실행하고 페이지를 해당 페이지 서버에서 가져옵니다. 페이지 서버의 읽기는 원격 읽기이므로 로컬 RBPEX의 읽기보다 느립니다. IO 관련 성능 문제를 해결할 때 상대적으로 느린 원격 페이지 서버 읽기를 통해 얼마나 많은 IO가 수행되었는지 알 수 있어야 합니다.

여러 DMV(동적 관리 뷰) 및 확장 이벤트에는 페이지 서버에서 원격 읽기 수를 지정하는 열과 필드가 있으며 이는 총 읽기와 비교할 수 있습니다. 또한 쿼리 저장소는 쿼리 런타임 통계의 일부로 원격 읽기를 캡처합니다.

  • 보고서 페이지 서버 읽기에 대한 열은 다음과 같은 실행 DMV 및 카탈로그 뷰에서 사용할 수 있습니다.

  • 페이지 서버 읽기는 다음과 같은 확장 이벤트에 추가됩니다.

    • sql_statement_completed
    • sp_statement_completed
    • sql_batch_completed
    • rpc_completed
    • scan_stopped
    • query_store_begin_persist_runtime_stat
    • query-store_execution_runtime_info

  • ActualPageServerReads/ActualPageServerReadAheads는 실제 계획에 대한 쿼리 계획 XML에 추가합니다. 예시:

<RunTimeCountersPerThread Thread="8" ActualRows="90466461" ActualRowsRead="90466461" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="133645" ActualCPUms="85105" ActualScans="1" ActualLogicalReads="6032256" ActualPhysicalReads="0" ActualPageServerReads="0" ActualReadAheads="6027814" ActualPageServerReadAheads="5687297" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobPageServerReads="0" ActualLobReadAheads="0" ActualLobPageServerReadAheads="0" />

참고 항목

쿼리 계획 속성 창에서 이러한 특성을 보려면 SSMS 18.3 이상이 필요합니다.

가상 파일 통계 및 IO 계정

Azure SQL Database에서 sys.dm_io_virtual_file_stats() DMF는 SQL Database IO를 모니터링하는 기본 방법입니다. 하이퍼스케일의 IO 특성은 분산 아키텍처로 인해 다릅니다. 이 섹션은 이 DMF에서 볼 수 있듯이 데이터 파일에 대한 IO(읽기 및 쓰기)에 초점을 맞춥니다. 하이퍼스케일에서 이 DMF에 보이는 각 데이터 파일은 원격 페이지 서버에 해당합니다. 여기에 언급된 RBPEX 캐시는 로컬 SSD 기반 캐시이며 컴퓨팅 복제본의 비포함 캐시입니다.

로컬 RBPEX 캐시 사용량

로컬 RBPEX 캐시는 로컬 SSD 스토리지의 컴퓨팅 복제본에 있습니다. 따라서 이 캐시에 대한 IO는 원격 페이지 서버에 대한 IO보다 빠릅니다. 현재 하이퍼스케일 데이터베이스의 sys.dm_io_virtual_file_stats()에는 컴퓨팅 복제본의 로컬 RBPEX 캐시에 대한 IO를 보고하는 특수 행이 있습니다. 이 행에서 database_id 및 file_id 열의 값은 모두 0입니다. 예를 들어 아래 쿼리는 데이터베이스 시작 후 RBPEX 사용량 통계를 반환합니다.

select * from sys.dm_io_virtual_file_stats(0,NULL);

RBPEX에서 수행한 읽기와 다른 모든 데이터 파일에서 수행한 총 읽기의 비율은 RBPEX 캐시 적중률을 제공합니다. 카운터 RBPEX cache hit ratio는 성능 카운터 DMV sys.dm_os_performance_counters에도 노출됩니다.

데이터 읽기

  • 컴퓨팅 복제본의 SQL Server 데이터베이스 엔진에서 읽기를 실행하는 경우 로컬 RBPEX 캐시 또는 원격 페이지 서버에서 제공되거나 여러 페이지를 읽는 경우 둘의 조합에 의해 제공될 수 있습니다.
  • 컴퓨팅 복제본이 특정 파일(예: file_id 1)의 일부 페이지를 읽을 때 이 데이터가 로컬 RBPEX 캐시에만 있는 경우 이 읽기에 대한 모든 IO는 file_id 0(RBPEX)으로 간주합니다. 해당 데이터의 일부가 로컬 RBPEX 캐시에 있고 다른 일부가 원격 페이지 서버에 있는 경우 IO는 RBPEX에서 제공하는 부분을 위해 file_id 0으로 간주하고 원격 페이지 서버에서 제공하는 부분은 file_id 1로 간주합니다.
  • 컴퓨팅 복제본이 페이지 서버에서 특정 LSN의 페이지를 요청할 때 페이지 서버가 요청된 LSN을 따라잡지 못한 경우 컴퓨팅 복제본에 대한 읽기는 페이지가 컴퓨팅 복제본으로 반환되기 전에 페이지 서버가 따라잡을 때까지 대기합니다. 컴퓨팅 복제본의 페이지 서버에서 읽은 경우 해당 IO에서 대기하고 있다면 PAGEIOLATCH_* 대기 유형이 나타납니다. 하이퍼스케일에서 이 대기 시간은 페이지 서버에서 요청된 페이지를 필요한 LSN에 맞추는 시간과 페이지를 페이지 서버에서 컴퓨팅 복제본으로 전송하는 데 필요한 시간을 모두 포함합니다.
  • 미리 읽기와 같은 큰 읽기는 보통 "분산형 수집" 읽기를 사용하여 수행합니다. 이렇게 하면 SQL Server 데이터베이스 엔진에서 한 번에 최대 4MB의 페이지를 읽을 수 있습니다. 이는 단일 읽기로 간주합니다. 그러나 읽는 데이터가 RBPEX에 있는 경우 버퍼 풀과 RBPEX는 항상 8KB 페이지를 사용하므로 이러한 읽기는 여러 개의 개별 8KB 읽기로 간주합니다. 결과적으로 RBPEX에 대해 나타나는 읽기 IO 수는 엔진에서 수행하는 실제 IO 수보다 클 수 있습니다.

데이터 쓰기

  • 기본 컴퓨팅 복제본은 페이지 서버에 직접 쓰지 않습니다. 대신 로그 서비스의 로그 레코드는 해당 페이지 서버에서 재생합니다.
  • 컴퓨팅 복제본에서 발생하는 쓰기는 주로 로컬 RBPEX(file_id 0)에 기록됩니다. 8KB보다 큰 논리 파일에 대한 쓰기의 경우(즉, 수집 쓰기를 사용하여 수행하는 쓰기) 버퍼 풀과 RBPEX가 항상 8KB 페이지를 사용하므로 각 쓰기 작업은 RBPEX에 대한 여러 개의 개별 8KB 쓰기로 변환합니다. 결과적으로 RBPEX에 대해 나타나는 쓰기 IO 수는 엔진에서 수행하는 실제 IO 수보다 클 수 있습니다.
  • RBPEX 파일이 아니거나 페이지 서버에 해당하는 file_id 0이 아닌 데이터 파일 또한 쓰기를 표시합니다. 하이퍼스케일 서비스 계층에서는 컴퓨팅 복제본을 페이지 서버에 직접 쓰지 않으므로 이러한 쓰기를 시뮬레이션합니다. 쓰기 IOPS 및 처리량은 컴퓨팅 복제본에서 발생할 때 고려하지만 file_id 0이 아닌 데이터 파일의 대기 시간은 페이지 서버 쓰기의 실제 대기 시간을 반영하지 않습니다.

로그 쓰기

  • 기본 컴퓨팅에서 로그 쓰기는 sys.dm_io_virtual_file_stats의 file_id 2에 해당합니다. 기본 컴퓨팅에 대한 로그 쓰기는 로그 랜딩 존에 대한 쓰기입니다.
  • 로그 레코드는 커밋의 보조 복제본에서 강화되지 않습니다. 하이퍼스케일에서 로그는 로그 서비스가 보조 복제본에 비동기적으로 적용합니다. 로그 쓰기를 실질적으로 보조 복제본에서 진행하지 않으므로 보조 복제본에서 로그 IO의 모든 계정은 추적 목적으로만 사용합니다.

리소스 사용률 통계의 데이터 IO

하이퍼스케일이 아닌 데이터베이스에서 리소스 거버넌스 데이터 IOPS 제한과 관련된 데이터 파일에 대한 읽기 및 쓰기 IOPS가 결합되어 sys.dm_db_resource_stats 및 sys.resource_stats 뷰, avg_data_io_percent 열에 보고됩니다. 동일한 값을 Azure Portal에서 데이터 IO 백분율로 보고합니다.

하이퍼스케일 데이터베이스에서 이 열은 컴퓨팅 복제본의 로컬 스토리지 제한, 특히 RBPEX 및 tempdb에 대한 IO와 관련된 데이터 IOPS 사용률을 보고합니다. 이 열의 100% 값은 리소스 거버넌스가 로컬 스토리지 IOPS를 제한하고 있음을 나타냅니다. 이것이 성능 문제와 관련이 있는 경우 더 적은 IO를 생성하도록 워크로드를 조정하거나 리소스 거버넌스 최대 데이터 IOPS한도를 늘리기 위해 데이터베이스 서비스 목표를 늘립니다. RBPEX 읽기 및 쓰기 리소스 거버넌스의 경우 시스템은 SQL Server 데이터베이스 엔진에서 발급할 수 있는 더 큰 IO가 아닌 개별 8KB IO를 계산합니다.

원격 페이지 서버에 대한 데이터 IO는 리소스 사용률 보기 또는 포털에서 보고되지 않지만 앞에서 언급한 것처럼 sys.dm_io_virtual_file_stats() DMF에서 보고됩니다.

추가 리소스