성능 시나리오 살펴보기

  • 5분

성능 도구 및 기능을 사용하는 방법을 결정하려면 시나리오를 통해 Azure SQL의 성능을 살펴보는 것이 중요합니다.

일반적인 성능 시나리오 이해

SQL Server 성능 문제 해결의 일반적인 기술은 성능 문제가 실행 중(높은 CPU) 또는 대기 중인지(리소스 대기 중) 여부를 검사하는 것입니다. 다음 다이어그램에서는 SQL Server 성능 문제가 실행 중인지 또는 대기 중인지와 성능 도구를 사용하여 원인과 해결 방법을 결정하는 의사 결정 트리를 보여 줍니다.

실행 및 대기 다이어그램

먼저 전체 리소스 사용량을 확인합니다. 표준 SQL Server 배포의 경우 Windows 성능 모니터 또는 Linux의 top과 같은 도구를 사용할 수 있습니다. Azure SQL의 경우 다음과 같은 방법을 사용할 수 있습니다.

  • Azure Portal/Powershell/경고

    Azure Monitor에는 Azure SQL에 대한 리소스 사용량을 볼 수 있는 통합 메트릭이 있습니다. 또한 경고를 설정하여 리소스 사용 조건을 찾을 수도 있습니다.

  • sys.dm_db_resource_stats

    Azure SQL Database의 경우 이 DMV를 확인하여 데이터베이스 배포에 대한 CPU, 메모리 및 I/O 리소스 사용량을 검토할 수 있습니다. 이 DMV는 15초마다 이 데이터의 스냅샷을 만듭니다.

  • sys.server_resource_stats

    이 DMV는 sys.dm_db_resource_stats와 동일하게 작동하지만 SQL Managed Instance에 대한 리소스 사용량인 CPU, 메모리 및 I/O를 확인하는 데 사용됩니다. 또한 이 DMV는 15초마다 스냅샷을 생성합니다.

  • sys.dm_user_db_resource_governance

    Azure SQL Database의 경우 이 DMV는 현재 데이터베이스 또는 탄력적 풀의 리소스 거버넌스 메커니즘에서 사용되는 실제 구성 및 용량 설정을 반환합니다.

  • sys.dm_instance_resource_governance

    Azure SQL Managed Instance의 경우 이 DMV는 sys.dm_user_db_resource_governance와 유사하지만 현재 SQL Managed Instance에 대한 정보를 반환합니다.

실행 중

높은 CPU 사용률에 문제가 있다고 확인한 경우 실행 중 시나리오라고 합니다. 실행 중 시나리오에는 컴파일 또는 실행을 통해 리소스를 사용하는 쿼리가 포함될 수 있습니다. 추가 분석을 위해 다음 도구를 사용합니다.

  • 쿼리 저장소

    SSMS의 가장 많이 사용되는 리소스 보고서, 쿼리 저장소 카탈로그 뷰 또는 Azure Portal의 Query Performance Insight(Azure SQL Database에만 해당)를 사용하여 가장 많은 CPU 리소스를 사용하고 있는 쿼리를 찾습니다.

  • sys.dm_exec_requests

    Azure SQL에서 이 DMV를 사용하여 활성 쿼리 상태의 스냅샷을 가져옵니다. CPU 용량이 충분한지 확인하려면 상태가 RUNNABLE이고 대기 형식이 SOS_SCHEDULER_YIELD인 쿼리를 찾습니다.

  • sys.dm_exec_query_stats

    이 DMV를 쿼리 저장소와 매우 유사하게 사용하여 리소스를 많이 사용하는 쿼리를 찾을 수 있습니다. 캐시된 쿼리 계획에만 사용할 수 있는 반면 쿼리 저장소는 지속적인 성능 기록 레코드를 제공합니다. 이 DMV를 사용하여 캐시된 쿼리에 대한 쿼리 플랜을 찾을 수도 있습니다.

  • sys.dm_exec_procedure_stats

    이 DMV는 저장 프로시저 수준에서 성능 정보를 볼 수 있다는 점을 제외하고 sys.dm_exec_query_stats만큼 많은 정보를 제공합니다.

    가장 많은 리소스를 사용하는 쿼리를 확인한 후에는 워크로드에 충분한 CPU 리소스가 있는지 점검해야 할 수 있습니다. 간단한 쿼리 프로파일링, SET 문, 쿼리 저장소 또는 확장 이벤트 추적과 같은 도구를 사용하여 쿼리 계획을 디버그할 수 있습니다.

대기 중

높은 CPU 리소스 사용량이 문제가 아닌 것으로 나타날 경우 리소스 대기와 관련된 성능 문제일 수 있습니다. 리소스 대기와 관련된 시나리오는 다음과 같습니다.

  • I/O 대기
  • 잠금 대기
  • 래치 대기
  • 버퍼 풀 제한
  • 메모리 부여
  • 계획 캐시 제거

대기 중 시나리오를 분석하려면 일반적으로 다음 도구를 확인합니다.

  • sys.dm_os_wait_stats

    이 DMV를 사용하여 데이터베이스 또는 인스턴스에 대한 상위 대기 유형을 확인합니다. 이 DMV는 상위 대기 유형에 따라 다음에 수행할 작업을 안내할 수 있습니다.

  • sys.dm_exec_requests

    이 DMV를 사용하여 활성 쿼리에 대한 특정 대기 유형을 찾아서 대기 중인 리소스를 확인할 수 있습니다. 다른 사용자의 잠금을 기다리는 표준 차단 시나리오일 수 있습니다.

  • sys.dm_os_waiting_tasks

    이 DMV를 사용하여 현재 실행 중인 특정 쿼리에 대한 특정 작업에 대한 대기 유형을 찾아 정상보다 오래 걸리는 이유를 확인할 수 있습니다. sys.dm_os_waiting_tasks에는 sys.dm_os_wait_stats가 시간 경과에 따라 집계하는 실시간 대기 통계가 포함되어 있습니다.

  • 쿼리 저장소

    쿼리 저장소는 쿼리 플랜 실행에 대한 상위 대기의 집계를 표시하는 보고서 및 카탈로그 뷰를 제공합니다. CPU 대기 시간은 실행 문제와 동일하다는 것을 알아야 합니다.

Azure SQL 관련 시나리오

실행 중 및 대기 중과 같이 Azure SQL과 관련된 몇 가지 성능 시나리오가 있습니다. 여기에는 로그 거버넌스, 작업자 제한, 중요 비즈니스용 서비스 계층에 대해 발생한 대기 및 하이퍼스케일 배포와 관련된 대기가 포함됩니다.

로그 거버넌스

Azure SQL은 로그 속도 거버넌스를 사용하여 리소스 제한을 트랜잭션 로그 사용에 적용할 수 있습니다. 이 적용은 종종 리소스 제한을 보장하고 약속된 SLA를 충족하는 데 필요할 수 있습니다. 로그 거버넌스는 다음과 같은 대기 유형에서 볼 수 있습니다.

  • LOG_RATE_GOVERNOR: Azure SQL Database 대기
  • POOL_LOG_RATE_GOVERNOR: 탄력적 풀 대기
  • INSTANCE_LOG_GOVERNOR: Azure SQL Managed Instance 대기
  • HADR_THROTTLE_LOG_RATE*: 중요 비즈니스용 및 지역 복제 대기 시간 대기

작업자 제한

SQL Server는 스레드의 작업자 풀을 사용하지만 최대 작업자 수를 제한합니다. 동시 사용자 수가 많은 애플리케이션은 Azure SQL Database 및 SQL Managed Instance에 적용되는 작업자 제한에 접근할 수 있습니다.

  • Azure SQL Database는 서비스 계층 및 크기에 따른 제한이 있습니다. 이 제한을 초과하면 새 쿼리에 오류가 발생합니다.
  • 현재 SQL Managed Instance는 max worker threads를 사용하므로 이 제한을 초과하는 작업자에게는 THREADPOOL 대기가 표시될 수 있습니다.

중요 비즈니스용 HADR 대기

중요 비즈니스용 서비스 계층을 사용하는 경우 예기치 않게 다음 대기 유형이 표시될 수 있습니다.

  • HADR_SYNC_COMMIT
  • HADR_DATABASE_FLOW_CONTROL
  • HADR_THROTTLE_LOG_RATE_SEND_RECV

이러한 대기로 인해 애플리케이션의 속도가 느려질 수 있지만 이를 확인하지 못할 수 있습니다. 일반적으로 Always On 가용성 그룹을 사용하는 것과 관련이 있습니다. 중요 비즈니스용 계층에서는 가용성 그룹 기술을 사용하여 중요 비즈니스용 서비스 계층의 SLA 및 가용성 기능을 구현하므로 이러한 대기 유형이 예상됩니다. 긴 대기 시간은 입출력 지연이나 복제본의 동기화 지연과 같은 병목 현상을 나타낼 수 있습니다.

하이퍼스케일

하이퍼스케일 아키텍처는 RBIO (로그 거버넌스의 가능한 표시)를 접두사로 하는 몇 가지 고유한 대기 유형을 생성할 수 있습니다. 또한 DMV, 카탈로그 뷰 및 확장 이벤트는 페이지 서버 읽기에 대한 메트릭을 표시하도록 향상됩니다.

다음 연습에서는 이 단원에서 얻은 도구와 지식을 사용하여 Azure SQL의 성능 문제를 모니터링하고 해결하는 방법을 알아봅니다.