성능 모니터링 및 문제 해결

  • 10분

모니터링 및 문제 해결은 일관된 성능을 제공하기 위한 핵심 요소입니다. Azure SQL은 SQL Server와 동일한 성능 모니터링 및 문제 해결 도구와 기능 및 추가 기능을 제공합니다. 여기에는 DMV(동적 관리 뷰), 확장 이벤트 및 Azure Monitor 같은 기능이 포함됩니다. Azure SQL에 대한 다양한 성능 시나리오에서 이러한 도구 및 기능을 사용하는 방법을 아는 것이 중요합니다. 이러한 시나리오에는 높은 CPU 사용률 또는 리소스 대기가 포함됩니다.

성능 모니터링 도구 및 기능

Azure SQL은 SQL Server와 함께 제공되는 친숙한 도구와 함께 Azure 에코시스템의 모니터링 및 문제 해결 기능을 제공합니다. 다음 섹션에서는 이를 간략하게 설명합니다.

Azure Monitor

Azure Monitor는 Azure 에코시스템의 일부로, Azure 메트릭, 경고 및 로그를 지원하기 위해 Azure SQL가 통합됩니다. Azure Portal에서 Azure Monitor 데이터를 시각화할 수 있으며 애플리케이션에서 Azure Event Hubs 또는 API를 통해 이 데이터에 액세스할 수 있습니다. Windows 성능 모니터와 마찬가지로 Azure Monitor는 SQL Server 도구를 사용하지 않고도 Azure SQL에 대한 리소스 사용 현황 메트릭에 액세스하는 데 도움이 됩니다.

DMV(동적 관리 뷰)

Azure SQL은 몇 가지 차이점을 제외하고 SQL Server와 거의 동일한 DMV 인프라를 제공합니다. DMV는 표준 T-SQL 쿼리를 사용하여 핵심 SQL Server 성능 데이터를 볼 수 있으므로 성능 모니터링에 중요합니다. 예를 들어 활성 쿼리, 리소스 사용량, 쿼리 계획, 리소스 대기 유형과 같은 정보를 볼 수 있습니다. 이 단원 뒷부분에서는 Azure SQL을 사용하는 DMV에 대해 자세히 알아봅니다.

확장 이벤트

Azure SQL은 몇 가지 차이점을 제외하고 SQL Server와 거의 동일한 확장 이벤트 인프라를 제공합니다. 확장 이벤트를 통해 Azure SQL을 구동하는 SQL Server 내의 핵심 실행 이벤트를 추적할 수 있습니다. 성능을 위해 확장 이벤트는 개별 쿼리의 실행을 추적할 수 있도록 지원합니다. 이 단원 뒷부분에서는 Azure SQL을 사용하는 확장 이벤트에 대해 자세히 알아봅니다.

간단한 쿼리 프로파일링

경량 프로파일링은 진행 중인 요청 및 고가치 쿼리에 대한 실제 실행 계획을 쿼리해야 하는 시나리오의 문제를 해결하는 고급 방법입니다. 낮은 오버헤드로 인해 아직 CPU에 바인딩되지 않은 모든 서버는 경량 프로파일링을 지속적으로 실행할 수 있으며 데이터베이스 전문가는 언제든지 실행 중인 실행을 활용할 수 있습니다. 예를 들어, SSMS(SQL Server Management Studio)에서 Activity Monitor를 사용하거나 sys.dm_exec_query_profiles 또는 sys.dm_exec_query_statistics_xml을 직접 쿼리하는 경우가 있습니다.

경량 쿼리 프로파일링을 사용하여 쿼리 계획 및 활성 쿼리의 실행 상태를 검사할 수 있습니다. 이 기능은 실행 중인 문의 쿼리 성능을 디버그하기 위한 주요 기능입니다. 이 기능은 확장 이벤트와 같은 도구를 사용하여 쿼리 성능을 추적하는 것에 비해 성능 문제를 해결하는 시간이 단축됩니다. DMV를 통해 경량 쿼리 프로파일링에 액세스할 수 있으며 SQL Server 2019 이상 버전과 마찬가지로 Azure SQL에서도 기본적으로 켜져 있습니다.

쿼리 계획 디버그 기능

경우에 따라 개별 T-SQL 문의 쿼리 성능에 대한 추가 정보가 필요할 수 있습니다. SHOWPLAN 및 STATISTICS와 같은 T-SQL SET 문은 이러한 세부 정보를 제공할 수 있으며 SQL Server의 경우처럼 Azure SQL에서도 완전하게 지원됩니다.

쿼리 저장소

쿼리 저장소는 사용자 데이터베이스에 저장된 쿼리 실행 성능의 이전 기록입니다. 쿼리 저장소는 기본적으로 Azure SQL에 대해 설정되어 있으며 자동 플랜 수정 및 자동 조정과 같은 기능을 제공하는 데 사용됩니다. 저장소에 대한 SSMS(SQL Server Management Studio) 보고서를 Azure SQL에서 사용할 수 있습니다. 이러한 보고서를 사용하여 쿼리 계획 차이 및 상위 대기 유형을 비롯한 상위 리소스 사용 쿼리를 찾아 리소스 대기 시나리오를 확인합니다.

성능 시각화

Azure SQL Database의 경우 시각화를 통해 Azure Portal에 쿼리 저장소 성능 정보를 통합했습니다. 그러면 SSMS와 같은 클라이언트 도구를 사용할 때와 같이 쿼리 저장소에 대해 동일한 정보 중 일부를 볼 수 있습니다. Azure Portal에서 성능 개요 및 Query Performance Insight 옵션을 사용합니다.

DMV 세부 정보

DMV는 오랫동안 SQL Server에서 성능을 모니터링하고 해결하는 주요 방법으로 사용되었습니다. 일반적인 SQL Server용 DMV는 Azure SQL에서 사용 가능하며 일부 추가 DMV는 Azure에서만 사용할 수 있습니다.

Azure SQL Managed Instance

SQL Server용 DMV는 모두 SQL Managed Instance에 사용할 수 있습니다. sys.dm_exec_requests 및 sys.dm_os_wait_stats와 같은 주요 DMV는 일반적으로 쿼리 성능을 검사하는 데 사용됩니다.

sys.server_resource_stats 시스템 뷰는 Azure SQL Managed Instance에만 해당되며 기록 리소스 사용량을 표시합니다. 성능 모니터와 같은 운영 체제 도구에 직접 액세스할 수 없기 때문에 이는 리소스 사용량을 확인하는 데 유용한 도구입니다.

Azure SQL Database

sys.dm_exec_requests 및 sys.dm_os_wait_stats를 포함하여 성능에 필요한 대부분의 일반적인 DMV를 사용할 수 있습니다. 이러한 DMV는 논리 서버에 대한 모든 데이터베이스에 걸친 정보가 아니라 해당 데이터베이스와 관련된 정보만 제공합니다.

sys.dm_db_resource_stats DMV는 Azure SQL Database에만 해당되며 이를 사용하여 데이터베이스의 리소스 사용 기록을 볼 수 있습니다. 관리되는 인스턴스에 대해 sys.server_resource_stats를 사용하는 방법과 비슷하게 이 DMV를 사용합니다.

sys.elastic_pool_resource_stats DMV는 sys.dm_db_resource_stats와 유사하지만 이를 사용하여 탄력적 풀 데이터베이스의 리소스 사용량을 볼 수 있습니다.

필요한 DMV

Azure SQL에 대한 특정 성능 시나리오를 해결하려면 다음 DMV가 필요합니다.

  • 파일별 I/O 성능에 대한 운영 체제 메트릭에 직접 액세스할 수 없기 때문에 sys.dm_io_virtual_file_stats는 중요합니다.
  • sys.dm_os_performance_counters는 Azure SQL Database와 SQL Managed Instance 모두에서 SQL Server의 일반적인 성능 메트릭을 보는 데 사용할 수 있습니다. 이 DMV는 성능 모니터에서 일반적으로 사용할 수 있는 SQL Server 성능 카운터 정보를 확인하는 데 사용합니다.
  • sys.dm_instance_resource_governance를 사용하여 관리되는 인스턴스에 대한 리소스 제한을 볼 수 있습니다. Azure Portal를 사용하지 않아도 이 정보를 확인하여 필요한 리소스 제한을 알 수 있습니다.
  • sys.dm_user_db_resource_governance를 사용하여 Azure SQL Database 배포의 배포 옵션, 서비스 계층 및 크기에 따라 일반적인 리소스 제한을 볼 수 있습니다. Azure Portal를 사용하지 않아도 이 정보를 확인하여 필요한 리소스 제한을 알 수 있습니다.

좀 더 심층적인 인사이트를 위한 DMV

이러한 DMV는 Azure SQL에 대한 리소스 제한 및 리소스 관리에 대해 심층적인 인사이트를 제공합니다. 일반적인 시나리오에서 사용하도록 고안되지는 않았으며 복잡한 성능 문제를 심층적으로 조사하는 경우에 유용할 수 있습니다. 이러한 DMV의 모든 세부 정보는 설명서를 참조하세요.

  • sys.dm_user_db_resource_governance_internal(SQL Managed Instance만 해당)
  • sys.dm_resource_governor_resource_pools_history_ex
  • sys.dm_resource_governor_workload_groups_history_ex

확장 이벤트 세부 정보

확장 이벤트 기능은 SQL Server에 대한 추적 메커니즘입니다. Azure SQL에 대한 확장 이벤트는 SQL Server 엔진을 기준으로 하므로 주목할 만한 몇 가지 차이점이 있으나 Azure SQL의 경우와 거의 동일합니다. 다음 섹션에서는 이러한 차이점을 설명합니다.

Azure SQL Database에 대한 확장 이벤트

세션을 만들고 이벤트, 작업 및 대상을 사용하여 SQL Server와 마찬가지로 Azure SQL Database에 대한 확장 이벤트를 사용할 수 있습니다. 확장 이벤트 세션을 만들 때 다음과 같은 중요한 사항을 염두에 두어야 합니다.

  • 가장 일반적으로 사용되는 이벤트 및 작업이 지원됩니다.
  • 파일, ring_buffer 및 카운터 대상이 지원됩니다.
  • 기본 운영 체제 디스크에 대한 액세스 권한이 없기 때문에 Azure Blob 스토리지에서 파일 대상이 지원됩니다.

SSMS 또는 T-SQL을 사용하여 세션을 만들고 시작할 수 있습니다. SSMS를 사용하여 확장 이벤트 세션 대상 데이터 또는 시스템 함수 sys.fn_xe_file_target_read_file을 볼 수 있습니다.

참고 항목

SSMS를 사용하여 Azure SQL Database의 활성 데이터를 볼 수는 없습니다.

세션에 대해 발생하는 모든 확장 이벤트가 논리 서버 전반이 아니라 데이터베이스에만 적용된다는 것을 알고 있어야 합니다.

Azure SQL Managed Instance에 대한 확장 이벤트

확장 이벤트는 세션을 만들고 이벤트, 작업 및 대상을 사용하여 SQL Server의 경우와 같은 방식으로 SQL Managed Instance에서도 사용할 수 있습니다. 확장 이벤트 세션을 만들 때 다음과 같은 중요한 사항을 염두에 두어야 합니다.

  • 모든 이벤트, 대상 및 작업이 지원됩니다.
  • 기본 운영 체제 디스크에 대한 액세스 권한이 없기 때문에 Azure Blob 스토리지에서 파일 대상이 지원됩니다.
  • 인스턴스의 관리 및 실행과 관련된 이벤트를 추적하기 위해 특정 이벤트가 SQL Managed Instance에 대해 추가됩니다.

SSMS 또는 T-SQL을 사용하여 세션을 만들고 시작할 수 있습니다. SSMS를 사용하여 확장 이벤트 세션 대상 데이터 또는 시스템 함수 sys.fn_xe_file_target_read_file을 볼 수 있습니다. 실시간 데이터를 보는 SSMS 기능은 SQL Server 및 Azure SQL Managed Instance에 대해 지원됩니다.

Azure SQL에 대한 성능 시나리오

성능 도구 및 기능을 모니터링하고 문제를 해결하는 방법을 결정하려면 시나리오를 통해 Azure SQL의 성능을 확인하는 것이 중요합니다.

일반적인 성능 시나리오

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

Diagram of running versus waiting.

다이어그램의 각 측면을 좀 더 자세히 살펴보겠습니다.

실행 중 및 대기 중

먼저 전체 리소스 사용량을 확인합니다. 표준 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 대기
  • Lock waits
  • 래치 대기
  • 버퍼 풀 제한
  • 메모리 부여
  • 계획 캐시 제거

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

  • 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 대기는 실행 중 문제와 동일하다는 것을 알고 있어야 합니다.

팁

실행 중이거나 대기 중인 시나리오에 대해 확장 이벤트를 사용할 수 있습니다. 이렇게 하려면 쿼리를 추적하는 확장 이벤트 세션을 설정해야 합니다. 성능 문제를 디버그하는 이 방법은 더 발전되었으며 DMV보다 더 많은 성능 오버헤드를 대가로 많은 정보를 반환할 수 있습니다.

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 및 가용성 기능을 구현하므로 이러한 대기 유형이 예상됩니다. 긴 대기 시간은 I/O 대기 시간 또는 복제본 숨김과 같은 병목 상태를 나타낼 수 있습니다.

하이퍼스케일

하이퍼스케일 아키텍처를 사용하면 RBIO가 접두사로 표시되는 고유한 대기 유형이 발생할 수 있습니다(로그 거버넌스를 나타낼 수 있음). 또한 DMV, 카탈로그 뷰 및 확장 이벤트는 페이지 서버 읽기에 대한 메트릭을 표시하도록 개선되었습니다.

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