Analyse des performances E/S (aperçu) – SQL Server sur des machines virtuelles Azure

S’applique à : SQL Server sur la machine virtuelle Azure

Cet article vous apprend à analyser les performances E/S pour SQL Server sur Azure Machines Virtuelles (VM) afin de trouver des problèmes résultant du dépassement des limites des machines virtuelles et des disques de données.

Vue d’ensemble

Bien que divers outils vous aident à résoudre les problèmes de performance de SQL Server, pour le faire efficacement sur une machine virtuelle Azure, il est important de comprendre ce qui se passe au niveau de l’hôte et de votre instance SQL Server où, souvent, la corrélation des métriques d’hôte avec les charges de travail SQL Server peut être un défi. SQL Server sur des machines virtuelles Azure facilite l’identification des problèmes de performances liés aux E/S par seconde (entrée/sortie par seconde) et à la limitation du débit causée par le dépassement des limites des disques de données et des machines virtuelles.

Les indicateurs de performance qui démontrent le problème et les étapes potentielles pour le résoudre sont disponibles dans le portail Azure et consultables avec Azure CLI.

Le volet Stockage de votre ressource de machines virtuelles SQL dans le portail Azure vous aide à :

• Gérer votre configuration de stockage
• Identifier la limitation E/S
• Évaluer votre système pour les meilleures pratiques liées aux E/S

Compréhension des métriques

L’onglet Analyse E/S s’appuie sur les métriques Azure pour identifier la latence du disque et la limitation des E/S de machine virtuelle ou disque. Les métriques Azure sont échantillonnées toutes les 30 secondes et agrégées à une minute.

Le système surveille la limitation et la latence du disque. Certaines limitations sont attendues et sont ignorées, sauf s’il existe également une latence de disque. Si plus de 500 millisecondes de latence de disque sont observées pendant une période consécutive de 5 minutes, le système :

• Explore plus en détail les métriques de performances
• Identifie la ressource limitée
• Fournit des causes racines potentielles et des étapes d’atténuation

Le tableau suivant explique les métriques Azure utilisées pour identifier les problèmes de limitation problématiques :

Résultats de l’analyse des E/S

En fonction de son analyse des métriques de performances au cours des dernières 24 heures, l’analyse des E/S détermine qu’il existe :

• Aucune limitation
• Limitation des E/S au niveau de la machine virtuelle
• Limitation E/S au niveau du disk

Aucun problème de limitation d’E/S

Si vous rencontrez un problème de performances mais qu’il n’y a pas de latence de disque, le problème de performances n’est pas dû à un problème de limitation d’E/S. Vous devez examiner d’autres domaines. Vous pouvez utiliser la Liste de vérification des meilleures pratiques pour SQL Server sur les machines virtuelles Azure pour vous assurer que votre système est configuré efficacement ou trouver des liens utiles pour dépanner les performances de SQL Server. Si vous activez la fonctionnalité d’évaluation des meilleures pratiques SQL, vous pouvez voir la liste complète des recommandations pour votre machine virtuelle SQL Server.

Problème de limitation des E/S au niveau de la machine virtuelle

Les machines virtuelles Azure sont des ressources informatiques basées sur le cloud qui sont disponibles en différentes séries et tailles pour différentes charges de travail, chacune avec des capacités et des caractéristiques de performance différentes. Pour les charges de travail SQL Server, les séries recommandées sont généralement les séries à mémoire optimisée, telles que les séries Ebdsv5, M et Mv2.

La taille de la machine virtuelle détermine le nombre de processeurs virtuels, de mémoire et de stockage disponibles pour l’instance SQL Server. Par rapport au stockage, il est relativement facile pour les clients de redimensionner leurs machines virtuelles et de mettre à l’échelle leur machine virtuelle en fonction des besoins des ressources d’application. Étant donné qu’il est possible que les E/S par seconde et le débit soient limités au niveau de la machine virtuelle, choisissez une taille de machine virtuelle appropriée en fonction des besoins en performances et du coût de la charge de travail.

Si vous migrez vers Azure, vous pouvez utiliser des outils tels que l’Assistant Migration de données et les Recommandations SKU, pour analyser la configuration et l’utilisation actuelles de SQL Server et suggérer la meilleure taille de machine virtuelle pour votre charge de travail dans Azure.

Les métriques Azure suivantes permettent de déterminer que la charge de travail est limitée à des limites supérieures imposées par la machine virtuelle :

• Pourcentage d’IOPS en cache de machine virtuelle consommées
• Pourcentage de bande passante en cache consommée par la machine virtuelle
• Pourcentage d’IOPS non mises en cache de machine virtuelle consommées
• Pourcentage de bande passante non mise en cache consommée par la machine virtuelle

Tenez compte des points clés suivants concernant la limitation des machines virtuelles :

• Vous pouvez augmenter la mémoire, les vCores, le débit et les E/S par seconde en redimensionnant votre machine virtuelle au sein d’une série de machines virtuelles.
• Vous ne pouvez pas réduire la taille de la machine virtuelle à un niveau où le nombre de disques de données dépasse la limite maximale des disques de données pour la taille de machine virtuelle cible.
• Il est important de déterminer le modèle de limitation. Par exemple, des pics peu fréquents de limitation peuvent probablement être résolus en réglant la charge de travail, tandis que les pics soutenus peuvent indiquer que le stockage sous-jacent est incapable de gérer la charge de travail.

Problème de limitation des E/S au niveau du disque

Pour les clients de machines virtuelles SQL, le stockage est l’aspect le plus essentiel à configurer de manière appropriée pour optimiser les performances, car la modification du stockage est plus difficile que le redimensionnement d’une machine virtuelle. Par exemple, l’augmentation des E/S par seconde ou du débit pour les disques SSD Premium nécessite la création d’un pool de stockage. Par conséquent, il est essentiel d’optimiser la configuration du stockage pour les prix et les performances pendant la phase de planification afin d’éviter les problèmes de performances après le déploiement.

Les métriques Azure suivantes permettent de déterminer que la charge de travail est limitée à des limites supérieures imposées par le disque :

• Pourcentage d’IOPS du disque de données consommées

• Pourcentage de bande passante du disque de données consommée Tenez compte des points clés suivants concernant la limitation des E/S au niveau du disque :

• Le disque de données est essentiel pour les performances de SQL Server. Le placement de fichiers de données SQL Server (.mdf) et de journal (.df) sur le disque de données est recommandé.

• Pour la limitation au niveau du disque de données, activez la mise en cache en lecture, si elle est disponible.

Pourcentage d’IOPS du disque de données consommées

La métrique Pourcentage d’IOPS du disque de données consommées mesure la consommation d’IOPS au niveau du disque. En règle générale, les besoins en E/S par seconde élevés sont associés à des applications et charges de travail basées sur OLTP hautement transactionnelles.   Les scénarios ou conditions suivants peuvent potentiellement dépasser les limites d’E/S par seconde du disque de données :

• Charge de travail de transaction élevée (IOPS) : si l’application traite un volume élevé de transactions de base de données qui impliquent des opérations de lecture et d’écriture fréquentes, elle peut rapidement consommer les IOPS allouées. 
• Requêtes inefficaces : les requêtes SQL mal optimisées ou les opérations de récupération de données peuvent entraîner une activité d’E/S excessive, consommant plus d’E/S par seconde que prévu. 
• Utilisateurs simultanés : si plusieurs utilisateurs ou sessions accèdent simultanément à la base de données et génèrent des demandes d’E/S, l’effet cumulatif peut entraîner l’atteinte de la limite d’E/S par seconde. 
• Ressources en cours : si l’infrastructure physique sous-jacente est fortement partagée avec d’autres locataires ou charges de travail, elle peut avoir un impact sur les IOPS disponibles pour votre machine virtuelle. 
• Pics temporaires : les pics temporaires de charge de travail, tels que le traitement par lots ou les migrations de données, peuvent entraîner des augmentations soudaines de la demande d’E/S qui dépassent les E/S alloués. 
• Petite taille du disque : si la taille du disque de données approvisionnée est relativement petite, la capacité d’E/S par seconde peut être limitée. Les disques plus petits ont des limites d’E/S par seconde inférieures et, si les exigences de l’application dépassent cette limite, le « Pourcentage d’IOPS de disque de données consommées » atteint 100 %. 
• Type de disque insuffisant : le choix d’un type de disque moins performant (tel qu’un HDD Standard) pour une application nécessitant beaucoup d’E/S peut entraîner des limitations d’E/S par seconde. 
• Taille de bande de disque non optimisée : si la configuration de stockage n’est pas optimisée pour la charge de travail, cela peut entraîner des performances d’E/S par seconde non optimales. 

Tenez compte des étapes suivantes pour éviter de dépasser la limite d’E/S par seconde du disque de données :

• Optimisez les requêtes SQL et la conception de base de données pour réduire les opérations d’E/S inutiles. 
• Sélectionnez un type de disque approprié (SSD Standard ou SSD Premium) qui correspond aux exigences d’IOPS de votre application. 
• Utilisez des tailles de disque plus grandes pour augmenter la capacité d’E/S par seconde disponible. 
• Distribuer des E/S sur plusieurs disques de données à l’aide de configurations RAID. 

Pourcentage de bande passante du disque de données consommée

La métrique Pourcentage de bande passante du disque de données consommée Azure mesure l’utilisation de la bande passante au niveau du disque. En règle générale, les besoins en débit élevé sont associés à l’entreposage de données, à l’entrepôt de données, à la création de rapports, à l’ETL et à d’autres charges de travail analytiques de données.

Les scénarios ou conditions suivants peuvent potentiellement dépasser les limites de bande passante du disque de données :

• Transferts de données volumineux : les transferts fréquents de données d’application à grande échelle entre le disque et la base de données SQL peuvent rapidement consommer la bande passante disponible du disque de données. 
• Chargement de données en bloc : les activités de transfert de disque associées aux insertions, mises à jour ou importations de données en bloc peuvent entraîner une consommation élevée de bande passante. 
• Entreposage ou analytique des données : les applications qui impliquent un entreposage de données lourd, un traitement analytique ou un rapport peuvent générer un déplacement important des données, ce qui peut entraîner un dépassement des limites de bande passante.
• Technologie/réplication de redondance des données élevée : la copie de données associée à l’utilisation de la réplication basée sur disque, la miroir de données ou d’autres mécanismes de redondance peuvent contribuer à la saturation de la bande passante. 
• Sauvegarde et restauration des données : les sauvegardes fréquentes de données, les instantané ou les processus de restauration peuvent consommer une bande passante importante du disque de données. 
• Exécution de requêtes parallèles : les requêtes parallèles qui impliquent des analyses de données volumineuses ou des jointures peuvent entraîner un déplacement important des données qui entraîne une utilisation de la bande passante. 
• Trafic réseau élevé : une activité réseau élevée, telle que les transferts de données entre la machine virtuelle et d’autres ressources, peut avoir un impact indirect sur la disponibilité de la bande passante du disque de données. 
• Type de disque insuffisant : le choix d’un type de disque moins performant pour une application avec des exigences élevées en matière de transfert de données peut entraîner un dépassement de la limite de bande passante. 
• Opérations simultanées nécessitant beaucoup de données : l’effet combiné de plusieurs processus ou sessions simultanés accédant aux données sur le même disque peut entraîner l’atteinte de la limite de bande passante. 
• Requêtes non optimisées ou processus ETL : les requêtes SQL mal optimisées ou les processus Extract, Transform, Load (ETL) peuvent entraîner un déplacement excessif des données qui entraîne une consommation excessive de bande passante. 

Tenez compte des étapes suivantes pour éviter de dépasser la limite de bande passante par seconde du disque de données :

• Optimisez les opérations de transfert de données pour réduire le déplacement inutile des données. 
• Envisagez d’utiliser des types de disques plus performants qui offrent une plus grande capacité de bande passante, comme Premium SSD ou Premium SSD v2.
• Distribuez des données sur plusieurs disques à l’aide de techniques telles que le partitionnement ou le partitionnement.
• Optimisez et parallélisez les requêtes et le traitement des données pour réduire le déplacement des données.
• Utilisez des mécanismes de compression et de stockage de données efficaces pour réduire le volume de données transférées.
• Surveillez les métriques de performances et augmentez vos configurations de stockage en fonction des besoins. Premium SSD v2 permet aux clients de mettre à l’échelle leurs E/S par seconde et leur débit en fonction des besoins à la demande.
• Il est important de surveiller et d’analyser régulièrement les métriques de performances pour identifier la cause des limitations d’E/S par seconde et de prendre les mesures appropriées pour optimiser les performances de stockage pour votre machine virtuelle SQL.

Bonnes pratiques relatives aux E/S

Étant donné que les systèmes de stockage mal configurés peuvent entraîner des problèmes de performances, vous pouvez utiliser le volet Stockage dans l’Portail Azure pour exécuter un sous-ensemble spécifique au disque de règles d’évaluation des meilleures pratiques SQL pour identifier les problèmes de configuration de stockage avec vos machines virtuelles SQL Server sur Azure. La fonctionnalité de bonnes pratiques SQL est basée sur l’API SQL Assessment.

Vous pouvez afficher une liste complète des recommandations sur GitHub. En filtrant la colonne ID sur les règles sur GitHub, vous pouvez voir les règles de configuration de disque de machine virtuelle SQL validées sous l’onglet configuration E/S des bonnes pratiques de configuration d’E/S du volet Stockage de vos machines virtuelle SQL dans le Portail Azure.

Sous l’onglet Meilleures pratiques relatives aux E/S, utilisez l’évaluation d’exécution pour démarrer une évaluation de votre configuration, ce qui doit prendre quelques minutes (sauf s’il existe un grand nombre de bases de données et d’objets). Sinon, si vous voyez un horodatage pour les résultats disponibles les plus récents, vous pouvez utiliser Fetch les résultats les plus récents pour passer en revue les résultats des évaluations précédentes.

Analyser les E/S avec PowerShell

Vous pouvez également utiliser le script PowerShell d’analyse des E/S pour analyser les performances d’E/S de votre machine virtuelle SQL Server :

# 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."
}

Étapes suivantes

• Exécutez une évaluation des meilleures pratiques SQL pour identifier les configurations incorrectes potentielles susceptibles d’entraîner des problèmes de performances.