I/O 성능 분석(미리 보기) - Azure VM의 SQL Server

적용 대상: Azure VM 기반 SQL Server

이 문서에서는 Azure VM(Virtual Machines)의 SQL Server에 대한 I/O 성능을 분석하여 가상 머신 및 데이터 디스크 제한을 초과하여 발생하는 문제를 찾는 방법을 설명합니다.

개요

다양한 도구를 통해 SQL Server 성능 문제를 해결하는 데 도움이 되지만, Azure VM에서 효과적으로 수행하려면 호스트 수준과 SQL Server 인스턴스 모두에서 발생하는 일을 이해하는 것이 중요합니다. 호스트 메트릭과 SQL Server 워크로드의 상관 관계를 지정하는 것이 어려울 수 있습니다. Azure VM의 SQL Server를 사용하면 가상 머신 및 데이터 디스크 제한을 초과하여 발생하는 IOPS(초당 입출력) 및 처리량 제한에서 비롯되는 성능 문제를 쉽게 식별할 수 있습니다.

문제를 보여 주는 성능 메트릭과 이를 해결하기 위한 잠재적인 단계는 Azure Portal에서 사용할 수 있으며 Azure CLI를 사용하여 쿼리할 수 있습니다.

Azure Portal에서 SQL 가상 머신 리소스의 스토리지 창을 통해 다음을 수행할 수 있습니다.

• 스토리지 구성 관리
• I/O 제한 식별
• I/O 관련 모범 사례에 대한 시스템 평가

메트릭 이해

I/O 분석 탭은 Azure 메트릭을 사용하여 디스크 대기 시간 및 VM 또는 디스크 I/O 제한을 식별합니다. Azure 메트릭은 30초마다 샘플링되고 1분으로 집계됩니다.

시스템은 제한 및 디스크 대기 시간을 모니터링합니다. 일부 제한은 예상되며 디스크 대기 시간도 없는 한 무시됩니다. 연속 5분 동안 500밀리초 이상의 디스크 대기 시간이 관찰되면 시스템은 다음을 수행합니다.

• 성능 메트릭에 대한 심층적 분석
• 제한된 리소스 식별
• 잠재적인 근본 원인 및 완화 단계 제공

다음 테이블에서는 문제가 있는 제한 문제를 식별하는 데 사용되는 Azure 메트릭에 대해 설명합니다.

I/O 분석 결과

I/O 분석은 지난 24시간의 성능 메트릭 분석을 기반으로 다음이 있는지 확인합니다.

• 제한 없음
• VM 수준 I/O 제한
• 디스크 수준 I/O 제한

I/O 제한 문제 없음

성능 문제가 발생하지만 디스크 대기 시간이 없는 경우 성능 문제는 I/O 제한으로 인한 문제가 아닙니다. 다른 영역을 조사해야 합니다. Azure VM의 SQL Server에 대한 모범 사례 체크리스트를 사용하여 시스템이 효율적으로 구성되었는지 확인하거나 SQL Server 성능 문제 해결을 위한 유용한 링크를 찾을 수 있습니다. SQL 모범 사례 평가 기능을 사용하도록 설정하면 SQL Server VM에 대한 권장 사항의 전체 목록을 볼 수 있습니다.

VM 수준 I/O 제한 문제

Azure Virtual Machines는 다양한 워크로드에 대해 서로 다른 시리즈와 크기로 제공되는 클라우드 기반 컴퓨팅 리소스로, 각각 다른 기능과 성능 특성을 가지고 있습니다. 일반적으로 SQL Server 워크로드의 경우 SQL Server 워크로드에 권장되는 시리즈는 Ebdsv5, M 및 Mv2 시리즈와 같은 메모리 최적화 시리즈입니다.

VM의 크기에 따라 SQL Server 인스턴스에 사용할 수 있는 vCPU, 메모리 및 스토리지 수가 결정됩니다. 스토리지에 비해 고객은 애플리케이션 리소스 요구 사항에 따라 가상 머신의 크기를 조정하고 VM을 확장 및 축소하는 것이 비교적 쉽습니다. IOPS 및 처리량이 VM 수준에서 제한될 수 있으므로 워크로드의 성능 요구 사항 및 비용에 따라 적절한 VM 크기를 선택합니다.

Azure로 마이그레이션하는 경우 Data Migration Assistant 및 SKU 권장 사항과 같은 도구를 사용하여 현재 SQL Server 구성 및 사용량을 분석하고 Azure에서 워크로드에 가장 적합한 VM 크기를 제안할 수 있습니다.

다음 Azure 메트릭은 워크로드가 VM에 의해 부과된 제한을 초과하지 못하도록 제한되는 것을 확인하는 데 사용됩니다.

• 소비한 VM 캐시 IOPS 백분율
• VM 캐시된 대역폭 사용률
• VM 캐시되지 않은 IOPS 사용량 백분율
• VM 캐시되지 않은 대역폭 소비한 백분율

VM 제한에 대한 다음 주요 사항을 고려합니다.

• VM 시리즈 내에서 가상 머신의 크기를 조정하여 메모리, vCore, 처리량 및 IOPS를 늘릴 수 있습니다.
• VM 크기를 데이터 디스크 수가 대상 VM 크기에 대한 최대 데이터 디스크 제한을 초과하는 수준으로 줄일 수 없습니다.
• 제한 패턴을 결정하는 것이 중요합니다. 예를 들어 워크로드를 튜닝하여 자주 제한 급증을 해결할 수 있는 반면, 지속적인 급증은 기본 스토리지가 워크로드를 처리할 수 없음을 나타낼 수 있습니다.

디스크 수준 I/O 제한 문제

SQL 가상 머신 고객의 경우 스토리지는 가상 머신 크기를 조정하는 것보다 스토리지 수정이 더 어렵기 때문에 최적화된 성능을 위해 적절하게 구성하는 가장 중요한 측면입니다. 예를 들어 프리미엄 SSD 디스크에 대한 IOPS 또는 처리량을 늘리기 위해 변경하려면 새 스토리지 풀을 만들어야 합니다. 따라서 배포 후 성능 문제를 방지하기 위해 계획 단계에서 가격과 성능 모두에 대한 스토리지 구성을 최적화하는 것이 중요합니다.

다음 Azure 메트릭은 워크로드가 디스크에 의해 부과된 제한을 초과하지 못하도록 제한되는 것을 확인하는 데 사용됩니다.

• 소비한 데이터 디스크 IOPS 백분율

• 데이터 디스크 대역폭 사용 백분율 디스크 수준 I/O 제한에 대한 다음 주요 사항을 고려합니다.

• 데이터 디스크는 SQL Server 성능에 매우 중요합니다. 데이터 디스크에 SQL Server 데이터(.mdf) 및 로그(.df) 파일을 배치하는 것이 좋습니다.

• 데이터 디스크 수준에서 제한하려면 사용 가능한 경우 읽기 캐싱을 사용하도록 설정합니다.

소비한 데이터 디스크 IOPS 백분율

데이터 디스크 IOPS 사용 백분율 메트릭은 디스크 수준에서 IOPS 사용을 측정합니다. 일반적으로 높은 IOPS 요구 사항은 높은 트랜잭션, OLTP 기반 애플리케이션 및 워크로드와 연결됩니다.   다음 시나리오 또는 조건은 잠재적으로 데이터 디스크 IOPS 제한을 초과할 수 있습니다.

• IOPS(높은 트랜잭션 워크로드): 애플리케이션이 자주 읽고 쓰는 작업을 포함하는 대용량 데이터베이스 트랜잭션을 처리하는 경우 할당된 IOPS를 빠르게 사용할 수 있습니다. 
• 비효율적인 쿼리: 제대로 최적화되지 않은 SQL 쿼리 또는 데이터 검색 작업으로 인해 과도한 I/O 작업이 발생할 수 있으므로 예상보다 많은 IOPS를 사용할 수 있습니다. 
• 동시 사용자: 여러 사용자 또는 세션이 동시에 데이터베이스에 액세스하고 I/O 요청을 생성하는 경우 누적 효과로 인해 IOPS 제한에 도달할 수 있습니다. 
• 리소스 경합: 기본 물리적 인프라가 다른 테넌트 또는 워크로드와 과도하게 공유되는 경우 가상 머신에 사용 가능한 IOPS에 영향을 미칠 수 있습니다. 
• 일시적인 급증: 일괄 처리 또는 데이터 마이그레이션과 같은 워크로드의 일시적인 급증은 할당된 IOPS를 능가하는 I/O 수요의 급격한 증가로 이어질 수 있습니다. 
• 작은 디스크 크기: 프로비전된 데이터 디스크 크기가 상대적으로 작은 경우 IOPS 용량이 제한될 수 있습니다. 작은 개별 디스크에는 더 낮은 IOPS 제한이 있으며 애플리케이션의 요구가 이 제한을 초과하면 "데이터 디스크 IOPS 사용 백분율"이 100%에 도달합니다. 
• 디스크 유형 부족: I/O 집약적 애플리케이션에 대해 성능이 낮은 디스크 유형(예: 표준 HDD)을 선택하면 IOPS 제한 사항이 발생할 수 있습니다. 
• 최적화되지 않은 디스크 스트라이프 크기: 스토리지 구성이 워크로드에 최적화되지 않은 경우 최적이 아닌 IOPS 성능이 발생할 수 있습니다. 

데이터 디스크 IOPS 제한을 초과하지 않도록 하려면 다음 단계를 고려합니다.

• 불필요한 I/O 작업을 최소화하도록 SQL 쿼리 및 데이터베이스 디자인을 최적화합니다. 
• 애플리케이션의 IOPS 요구 사항과 일치하는 적절한 디스크 유형(표준 SSD 또는 프리미엄 SSD)을 선택합니다. 
• 더 큰 디스크 크기를 사용하여 사용 가능한 IOPS 용량을 늘입니다. 
• RAID 구성을 사용하여 여러 데이터 디스크에 I/O를 배포합니다. 

데이터 디스크 대역폭 사용 백분율

데이터 디스크 대역폭 사용 백분율 Azure 메트릭은 디스크 수준에서 대역폭 사용률을 측정합니다. 일반적으로 높은 처리량 요구 사항은 데이터 웨어하우징, 데이터 마트, 보고, ETL 및 기타 데이터 분석 워크로드와 관련이 있습니다.

다음 시나리오 또는 조건은 잠재적으로 데이터 디스크 대역폭 제한을 초과할 수 있습니다.

• 대규모 데이터 전송: 디스크와 SQL 데이터베이스 간에 자주 대규모 애플리케이션 데이터를 전송하면 사용 가능한 데이터 디스크 대역폭을 빠르게 사용할 수 있습니다. 
• 대량 데이터 로드: 대량 데이터 삽입, 업데이트 또는 가져오기와 관련된 디스크 전송 활동으로 인해 대역폭 사용량이 높아질 수 있습니다. 
• 데이터 웨어하우징 또는 분석: 데이터 웨어하우징, 분석 처리 또는 보고가 많은 애플리케이션은 상당한 데이터 이동을 생성하여 대역폭 제한을 초과할 수 있습니다.
• 높은 데이터 중복 기술/복제: 연결된 데이터 복사는 디스크 기반 복제, 데이터 미러링 또는 기타 중복 메커니즘을 사용하여 대역폭 포화에 기여할 수 있습니다. 
• 데이터 백업 및 복원: 빈번한 데이터 백업, 스냅샷 또는 복원 프로세스는 상당한 데이터 디스크 대역폭을 사용할 수 있습니다. 
• 병렬 쿼리 실행: 대용량 데이터 검색 또는 조인을 포함하는 병렬 쿼리는 대역폭 사용률을 초래하는 상당한 데이터 이동으로 이어질 수 있습니다. 
• 상승된 네트워크 트래픽: 가상 머신과 다른 리소스 간의 데이터 전송과 같은 높은 네트워크 활동은 데이터 디스크 대역폭 가용성에 간접적으로 영향을 미칠 수 있습니다. 
• 디스크 유형 부족: 데이터 전송 요구 사항이 높은 애플리케이션에 대해 성능이 낮은 디스크 유형을 선택하면 대역폭 제한을 초과할 수 있습니다. 
• 동시 데이터 집약적 작업: 동일한 디스크에서 데이터에 액세스하고 전송하는 여러 동시 프로세스 또는 세션의 결합된 효과로 인해 대역폭 제한에 도달할 수 있습니다. 
• 최적화되지 않은 쿼리 또는 ETL 프로세스: 제대로 최적화되지 않은 SQL 쿼리 또는 ETL(추출, 변환, 로드) 프로세스로 인해 과도한 데이터 이동이 발생하여 과도한 대역폭 사용으로 이어질 수 있습니다. 

데이터 디스크 대역폭 제한을 초과하지 않도록 하려면 다음 단계를 고려합니다.

• 불필요한 데이터 이동을 최소화하도록 데이터 전송 작업을 최적화합니다. 
• 프리미엄 SSD 또는 프리미엄 SSD v2와 같이 더 큰 대역폭 용량을 제공하는 고성능 디스크 유형을 사용하는 것이 좋습니다.
• 분할 또는 분배와 같은 기술을 사용하여 여러 디스크에 데이터를 분산합니다.
• 쿼리 및 데이터 처리를 최적화하고 병렬화하여 데이터 이동을 줄입니다.
• 압축 및 효율적인 데이터 스토리지 메커니즘을 사용하여 전송되는 데이터의 양을 줄입니다.
• 성능 메트릭을 모니터링하고 필요에 따라 스토리지 구성을 강화합니다. 프리미엄 SSD v2를 사용하면 고객이 필요에 따라 IOPS 및 처리량을 확장할 수 있습니다.
• 정기적으로 성능 메트릭을 모니터링하고 분석하여 IOPS 제한의 원인을 식별하고 적절한 조치를 취하여 SQL 가상 머신에 대한 스토리지 성능을 최적화하는 것이 중요합니다.

I/O 관련 모범 사례

제대로 구성되지 않은 스토리지 시스템은 성능 문제로 이어질 수 있으므로 Azure Portal의 스토리지 창을 통해 SQL 모범 사례 평가 규칙의 디스크별 하위 집합을 실행하여 Azure VM의 SQL Server에서 스토리지 구성 문제를 식별할 수 있습니다. SQL 모범 사례 기능은 SQL 평가 API를 기반으로 합니다.

GitHub에서 권장 사항의 전체 목록을 볼 수 있습니다. GitHub의 규칙에서 ID 열을 필터링하면 Azure Portal의 SQL 가상 머신 리소스에 대한 스토리지 창의 I/O 구성 모범 사례 탭에서 유효성을 검사하는 SQL VM 디스크 구성 규칙을 볼 수 있습니다.

I/O 관련 모범 사례 탭에서 실행 평가를 사용하여 구성 평가를 시작합니다. 이 평가는 완료하는 데 몇 분 정도 걸립니다(데이터베이스 및 개체 수가 많지 않은 경우). 또는 사용 가능한 최신 결과에 대한 타임스탬프가 표시되는 경우 최신 결과 가져오기를 사용하여 이전 평가의 결과를 검토할 수 있습니다.

PowerShell을 사용한 I/O 분석

I/O 분석 PowerShell 스크립트를 사용하여 SQL Server VM의 I/O 성능을 분석할 수도 있습니다.

# Enter parameters
$subscriptionId = Read-Host "<Subscription ID>"
$resourceGroup = Read-Host "<Resource Group>"
$vmName = Read-Host "<Virtual machine name>"

# Set resource details
$resourceType = "Microsoft.Compute/virtualMachines"
$resourceId = "/subscriptions/$subscriptionId/resourceGroups/$resourceGroup/providers/$resourceType/$vmName"

# Get Azure access token
$accessToken = az account get-access-token --query accessToken -o tsv

# Invoke Azure Monitor Metrics API
function Get-Metrics {
    [CmdletBinding()]
    param (
        [string]$accessToken,
        [string]$resourceId,
        [string]$metricNames,
        [string]$apiVersion = "2023-10-01"
    )
    try {
        $startTime = (Get-Date).AddHours(-24).ToUniversalTime().ToString('yyyy-MM-ddTHH:mm:ssZ')
        $endTime = (Get-Date).ToUniversalTime().ToString('yyyy-MM-ddTHH:mm:ssZ')
        $timespan = "$startTime/$endTime"
        Write-Verbose "Evaluating timespan: $timespan"
        $uri = "https://management.azure.com$resourceId/providers/Microsoft.Insights/metrics?api-version=$apiVersion&metricnames=$metricNames&aggregation=maximum&interval=PT1M&timespan=$timespan"
        $headers = @{ "Authorization" = "Bearer $accessToken"; "Content-Type" = "application/json" }
        
        $response = Invoke-RestMethod -Uri $uri -Headers $headers -Method Get
        if ($response) {
            Write-Verbose "API response successfully retrieved."
            return $response
        } else {
            Write-Error "No response from API."
        }
    } catch {
        Write-Error "Error retrieving metrics: $_"
    }
}

# Check if data disk latency violates thresholds
function Check-Latency {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $true)]
        [Object]$metrics,

        [Parameter()]
        [int]$latencyThreshold = 500,

        [Parameter()]
        [int]$consecutiveCount = 5
    )
    $violationTimes = @()
    foreach ($metric in $metrics.value) {
        if ($metric.name.value -eq "Data Disk Latency") {
            $count = 0
            foreach ($dataPoint in $metric.timeseries[0].data) {
                if ($dataPoint.maximum -gt $latencyThreshold) {
                    $count++
                    if ($count -ge $consecutiveCount) {
                        $violationTimes += $dataPoint.timeStamp
                        $count = 0  # Reset count after recording a violation
                    }
                } else {
                    $count = 0  # Reset count if the sequence is broken
                }
            }
        }
    }
    if ($violationTimes.Count -gt 0) {
        Write-Verbose "Latency violations detected."
        return @{ "Flag" = $true; "Times" = $violationTimes }
    } else {
        Write-Verbose "No latency violations detected."
        return @{ "Flag" = $false }
    }
}

# Check metrics other than latency to evaluate for throttling
function Check-OtherMetricsThrottled {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $true)]
        [Object]$metrics,

        [Parameter()]
        [int]$PercentageThreshold = 90,

        [Parameter()]
        [int]$consecutiveCount = 5
    )
    $violatedMetrics = @()
    foreach ($metric in $metrics.value) {
        $count = 0
        foreach ($dataPoint in $metric.timeseries[0].data) {
            if ($dataPoint.maximum -gt $PercentageThreshold) {
                $count++
                if ($count -ge $consecutiveCount) {
                    $violatedMetrics += @{ "Metric" = $metric.name.localizedValue; "Time" = $dataPoint.timeStamp; "Value" = $dataPoint.maximum }
                    break
                }
            } else {
                $count = 0
            }
        }
    }
    if ($violatedMetrics.Count -gt 0) {
        Write-Verbose "Other metrics violations detected."
    } else {
        Write-Verbose "No other metrics violations detected."
    }
    return $violatedMetrics
}

# Compare times for latency & other throttled metrics. Logs the volations with values & timestamps
function CompareTimes {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $true)]
        [Hashtable]$latencyResult,
        
        [Parameter(Mandatory = $true)]
        [Array]$otherMetrics
    )
    foreach ($metric in $otherMetrics) {
        $otherDateTime = [DateTime]$metric["Time"]
        $isWithinFiveMinutes = $false
        $closestLatencyTime = $null
        $closestTimeDifference = [int]::MaxValue

        foreach ($latencyTime in $latencyResult.Times) {
            $latencyDateTime = [DateTime]$latencyTime
            $timeDifference = [Math]::Abs(($otherDateTime - $latencyDateTime).TotalMinutes)
            
            if ($timeDifference -le 5) {
                $isWithinFiveMinutes = $true
                if ($timeDifference -lt $closestTimeDifference) {
                    $closestTimeDifference = $timeDifference
                    $closestLatencyTime = $latencyTime
                }
            }
        }

        if ($isWithinFiveMinutes) {
            if ($otherDateTime -lt $closestLatencyTime) {
                Write-Host "`n $($metric["Metric"]) limit was hit before latency spiked at $closestLatencyTime with value $($metric["Value"]). `n"
            } else {
                Write-Host "`n $($metric["Metric"]) hit its limit with value $($metric["Value"]) at $($metric["Time"])."
                Write-Host "Latency spiked at $closestLatencyTime before $($metric["Metric"]) hit its limit `n"
            }
        } else {
            Write-Host "`n Metric: $($metric["Metric"]) exceeded its threshold with a value of $($metric["Value"]) at $($metric["Time"]), but this was not within 5 minutes of any latency spikes."
        }
    }
}

# Prompt user for latency threshold
$latencyThreshold = Read-Host "Enter Latency Threshold (default is 500)"
if (-not [int]::TryParse($latencyThreshold, [ref]0)) {
    $latencyThreshold = 500 # Use default if invalid input
    Write-Host "No valid input provided. Using Default 500ms for disk latency threshold"
}

# Execute main logic
$latencyMetrics = Get-Metrics -accessToken $accessToken -resourceId $resourceId -metricNames "Data Disk Latency"
$latencyResult = Check-Latency -metrics $latencyMetrics -latencyThreshold $latencyThreshold

if ($latencyResult.Flag) {
    
    # If latency is flagged, check for other metrics. If there is no disk latency, machine is likely not throttled but only at high consumption
    Write-Verbose "Checking the following metrics: Data Disk Bandwidth Consumed Percentage,Data Disk IOPS Consumed Percentage,VM Cached Bandwidth Consumed Percentage,VM Cached IOPS Consumed Percentage,VM Uncached Bandwidth Consumed Percentage,VM Uncached IOPS Consumed Percentage"
    
    $DiskVMMetrics = Get-Metrics -accessToken $accessToken -resourceId $resourceId -metricNames "Data Disk Bandwidth Consumed Percentage,Data Disk IOPS Consumed Percentage,VM Cached Bandwidth Consumed Percentage,VM Cached IOPS Consumed Percentage,VM Uncached Bandwidth Consumed Percentage,VM Uncached IOPS Consumed Percentage"
    
    $additionalMetrics = Check-OtherMetricsThrottled -metrics $DiskVMMetrics
    
    if ($additionalMetrics.Count -gt 0) {
        CompareTimes $latencyResult $additionalMetrics
    } else {
        Write-Host "No metrics violations detected besides latency."
    }
} else {
    Write-Host "No latency issues detected."
}

다음 단계

• SQL 모범 사례 평가를 실행하여 성능 문제로 이어질 수 있는 잠재적인 잘못된 구성을 식별합니다.