Análise de Desempenho de E/S (Versão prévia) - SQL Server em VMs do Azure

Aplica-se a: SQL Server na VM do Azure

Este artigo ensina você a analisar o desempenho de E/S do SQL Server em VMs (Máquinas Virtuais) do Azure para localizar problemas que ocorrem por exceder os limites de máquinas virtuais e discos de dados.

Visão geral

Embora várias ferramentas o ajudem a solucionar problemas de desempenho do SQL Server, para fazer isso com eficiência em uma VM do Azure, é importante entender o que está acontecendo no nível do host e na instância do SQL Server, em que, muitas vezes, pode ser um desafio correlacionar métricas de host com cargas de trabalho do SQL Server. O SQL Server em VMs do Azure facilita a identificação de problemas de desempenho decorrentes de IOPS (Entrada/Saída por segundo) e limitação de produtividade causada por exceder os limites de máquinas virtuais e discos de dados.

As métricas de desempenho que demonstram o problema e as etapas possíveis para resolvê-lo estão disponíveis no portal do Azure e podem ser consultadas com a CLI do Azure.

O painel Armazenamento do recurso máquinas virtuais do SQL no portal do Azure ajuda você a:

• Gerenciar suas configurações de armazenamento
• Identificar a limitação de E/S
• Avaliar seu sistema quanto às práticas recomendadas relacionadas à E/S

Noções básicas sobre as métricas

A guia Análise de E/S depende das Métricas do Azure para identificar a latência de disco e a limitação de E/S de VM ou disco. As Métricas do Azure são amostradas a cada 30 segundos e agregadas a um minuto.

O sistema monitora a limitação e a latência do disco. Alguma limitação é esperada e é ignorada, a menos que também haja latência de disco. Se mais de 500 milissegundos de latência de disco forem observados em um período consecutivo de 5 minutos, o sistema:

• Aprofunda-se nas métricas de desempenho
• Identifica o recurso limitado
• Fornece possíveis causas básicas e etapas de mitigação

A tabela a seguir explica as Métricas do Azure usadas para identificar problemas de limitação:

Conclusões da análise de E/S

Com base em sua análise de métricas de desempenho nas últimas 24 horas, a análise de E/S determina que:

• Não há limitação
• Há limitação de E/S no nível da VM
• Há limitação de E/S no nível do disco

Não há problema de limitação de E/S

Se você estiver enfrentando um problema de desempenho, mas não houver latência de disco, o problema de desempenho não é devido a um problema de limitação de E/S. Você precisará investigar outras áreas. Você pode usar a Lista de verificação de práticas recomendadas para o SQL Server em VMs do Azure para garantir que seu sistema esteja configurado de forma eficiente ou encontrar links úteis para solucionar problemas de desempenho do SQL Server. Se você habilitar o recurso de práticas recomendadas de avaliação do SQL, poderá ver a lista completa de recomendações para sua VM do SQL Server.

Problema de limitação de E/S no nível da VM

As Máquinas Virtuais do Azure são recursos de computação baseados em nuvem que vêm em diferentes séries e tamanhos para várias cargas de trabalho, cada uma com recursos e características de desempenho diferentes. Para cargas de trabalho do SQL Server, geralmente, as séries recomendadas para cargas de trabalho do SQL Server são as com otimização de memória, como as séries Ebdsv5, M e Mv2.

O tamanho da VM determina o número de vCPUs, memória e armazenamento disponíveis para a instância do SQL Server. Em comparação com o armazenamento, é relativamente fácil para os clientes redimensionar suas máquinas virtuais e aumentar e reduzir verticalmente sua VM com base nas necessidades de recursos do aplicativo. Como é possível que IOPS e produtividade sejam limitadas no nível da VM, escolha um tamanho de VM apropriado com base nas necessidades de desempenho e no custo da carga de trabalho.

Se você estiver migrando para o Azure, poderá usar ferramentas como o Assistente de Migração de Dados e as Recomendações de SKU para analisar sua configuração e uso atuais do SQL Server e sugerir o melhor tamanho de VM para sua carga de trabalho no Azure.

As seguintes métricas do Azure são usadas para determinar se a carga de trabalho é limitada por exceder os limites impostos pela VM:

• Percentual Consumido de IOPS Armazenada em Cache da VM
• Percentual Consumido de Largura de Banda Armazenada em Cache da VM
• Percentual Consumido de IOPS Não Armazenada em Cache da VM
• Percentual Consumido de Largura de Banda Não Armazenada em Cache da VM

Considere os seguintes pontos-chave sobre a limitação de VM:

• Você pode aumentar a memória, vCores, produtividade e IOPS redimensionando sua máquina virtual em uma série de VMs.
• Não é possível reduzir o tamanho da VM a um nível em que o número de discos de dados exceda o limite máximo de discos de dados para o tamanho da VM de destino.
• É importante determinar o padrão de limitação. Por exemplo, picos de limitação pouco frequentes provavelmente podem ser resolvidos ajustando a carga de trabalho, enquanto picos prolongados podem indicar que o armazenamento subjacente não é capaz de lidar com a carga de trabalho.

Problema de limitação de E/S no nível do disco

Para clientes de máquina virtual do SQL, o armazenamento é o aspecto mais crítico a ser configurado adequadamente para desempenho otimizado, já que modificar o armazenamento é mais complexo do que redimensionar uma máquina virtual. Por exemplo, fazer alterações para aumentar IOPS ou produtividade para discos SSD Premium requer a criação de um novo pool de armazenamento. Como tal, é crucial otimizar a configuração de armazenamento para preço e desempenho durante a fase de planejamento para evitar problemas de desempenho após a implantação.

As seguintes métricas do Azure são usadas para determinar se a carga de trabalho é limitada por exceder os limites impostos pelo disco:

• Percentual Consumido de IOPS do Disco de Dados

• Percentual Consumido de Largura de Banda do Disco de Dados Considere os seguintes pontos-chave sobre a limitação de E/S no nível do disco:

• O disco de dados é essencial para o desempenho do SQL Server. É recomendável colocar arquivos de dados (.mdf) e de log (.df) do SQL Server no disco de dados.

• Para limitação no nível do disco de dados, habilite o cache de leitura, se estiver disponível.

Percentual Consumido de IOPS do Disco de Dados

A métrica Percentual Consumido de IOPS do Disco de Dados mede o consumo de IOPS no nível do disco. Geralmente, a alta necessidade de IOPS está associada a aplicativos e cargas de trabalho altamente transacionais baseados em OLTP.   Os seguintes cenários ou condições podem potencialmente exceder os limites de IOPS do disco de dados:

• Alta carga de trabalho de transação (IOPS): se o aplicativo estiver processando um alto volume de transações de banco de dados que envolvem operações de leitura e gravação frequentes, ele poderá consumir rapidamente as IOPS alocadas. 
• Consultas ineficientes: consultas SQL ou operações de recuperação de dados mal otimizadas podem levar a uma atividade excessiva de E/S, consumindo mais IOPS do que o previsto. 
• Usuários simultâneos: se vários usuários ou sessões estiverem acessando simultaneamente o banco de dados e gerando solicitações de E/S, o efeito cumulativo poderá fazer com que o limite de IOPS seja atingido. 
• Recursos concorrentes: se a infraestrutura física subjacente for fortemente compartilhada com outros locatários ou cargas de trabalho, isso poderá afetar as IOPS disponíveis para sua máquina virtual. 
• Picos temporários: picos temporários na carga de trabalho, como processamento em lote ou migrações de dados, podem levar a aumentos repentinos na demanda de E/S que superam as IOPS alocadas. 
• Tamanho de disco pequeno: se o tamanho do disco de dados provisionados for relativamente pequeno, a capacidade de IOPS poderá ser limitada. Discos individuais menores têm limites de IOPS mais baixos e, se as demandas do aplicativo excederem esse limite, o "Percentual consumido de IOPS de disco de dados" atingirá 100%. 
• Tipo de disco insuficiente: escolher um tipo de disco de baixo desempenho (como um HDD Standard) para um aplicativo com uso intensivo de E/S pode levar a limitações de IOPS. 
• Tamanho de distribuição de disco não otimizado: se a configuração de armazenamento não for otimizada para a carga de trabalho, isso pode levar a um desempenho de IOPS abaixo do ideal. 

Considere as seguintes etapas para evitar exceder o limite de IOPS do disco de dados:

• Otimize as consultas SQL e o design do banco de dados para minimizar operações de E/S desnecessárias. 
• Escolha um tipo de disco apropriado (SSD Standard ou SSD Premium) que corresponda aos requisitos de IOPS do seu aplicativo. 
• Use tamanhos de disco maiores para aumentar a capacidade de IOPS disponível. 
• Distribua E/S em vários discos de dados usando configurações RAID. 

Percentual Consumido de Largura de Banda do Disco de Dados

A métrica Percentual Consumido de Largura de Banda do Disco de Dados do Azure mede a utilização da largura de banda no nível do disco. Geralmente, as necessidades de alta produtividade estão associadas a data warehousing, data mart, relatórios, ETL e outras cargas de trabalho de análise de dados.

Os seguintes cenários ou condições podem potencialmente exceder os limites de largura de banda do disco de dados:

• Grandes transferências de dados: transferências de dados de aplicativos frequentes e em grande escala entre o disco e o banco de dados SQL podem consumir rapidamente a largura de banda do disco de dados disponível. 
• Carregamento de dados em massa: as atividades de transferência de disco associadas a inserções, atualizações ou importações de dados em massa podem levar a um alto consumo de largura de banda. 
• Data warehousing ou análise: aplicativos que envolvem armazenamento de dados intenso, processamento de análise ou relatórios podem gerar movimentação substancial de dados, potencialmente causando limites de largura de banda excedentes.
• Alta tecnologia de redundância de dados/replicação: a cópia de dados associada ao uso de replicação baseada em disco, espelhamento de dados ou outros mecanismos de redundância pode contribuir para a saturação da largura de banda. 
• Backup e restauração de dados: backups de dados, instantâneos ou processos de restauração frequentes podem consumir uma largura de banda de disco de dados significativa. 
• Execução de consulta paralela: consultas paralelas que envolvem grandes verificações ou junções de dados podem levar a uma movimentação substancial de dados que resulta na utilização da largura de banda. 
• Tráfego de rede elevado: a alta atividade de rede, como transferências de dados entre a máquina virtual e outros recursos, pode afetar indiretamente a disponibilidade da largura de banda do disco de dados. 
• Tipo de disco insuficiente: escolher um tipo de disco de baixo desempenho para um aplicativo com altos requisitos de transferência de dados pode levar a exceder o limite de largura de banda. 
• Operações simultâneas com uso intensivo de dados: o efeito combinado de vários processos ou sessões simultâneas acessando e transferindo dados no mesmo disco pode fazer com que o limite de largura de banda seja excedido. 
• Consultas ou processos ETL não otimizados: consultas SQL ou processos de Extração, Transformação, Carga (ETL) mal otimizados podem levar à movimentação excessiva de dados que resulta em consumo excessivo de largura de banda. 

Considere as seguintes etapas para evitar exceder o limite de largura de banda do disco de dados:

• Otimize as operações de transferência de dados para minimizar a movimentação de dados desnecessária. 
• Considere o uso de tipos de disco de alto desempenho que ofereçam maior capacidade de largura de banda, como SSD Premium ou SSD Premium v2.
• Distribua dados em vários discos usando técnicas como particionamento ou fragmentação.
• Otimize e paralelize consultas e processamento de dados para reduzir a movimentação de dados.
• Use mecanismos de compactação e armazenamento de dados eficientes para reduzir o volume de dados que estão sendo transferidos.
• Monitore as métricas de desempenho e aumente a escala de suas configurações de armazenamento conforme necessário. O SSD v2 Premium permite que os clientes escalem suas IOPS e produtividade conforme necessário sob demanda.
• É importante monitorar e analisar métricas de desempenho regularmente para identificar a causa das limitações de IOPS e tomar as ações apropriadas para otimizar o desempenho de armazenamento para sua máquina virtual SQL.

Práticas recomendadas relacionadas à E/S

Como sistemas de armazenamento mal configurados podem levar a problemas de desempenho, você pode usar o painel Armazenamento no portal do Azure para executar um subconjunto específico de disco de regras de práticas recomendadas de avaliação do SQL para identificar problemas de configuração de armazenamento com seu SQL Server em VMs do Azure. O recurso de práticas recomendadas do SQL é baseado na API de Avaliação do SQL.

Você pode ver uma lista completa de recomendações no GitHub. Ao filtrar na coluna id nas regras no GitHub, você pode ver as regras de configuração de disco da VM do SQL que são validadas na guia Práticas recomendadas de configuração de E/S do painel Armazenamento em seu recurso máquinas virtuais de SQL no portal do Azure.

Na guia Práticas recomendadas relacionadas à E/S, use Executar avaliação para iniciar uma avaliação de sua configuração, que deve levar alguns minutos para ser concluída (a menos que haja um número grande de bancos de dados e objetos). Como alternativa, se você vir um carimbo de data/hora para os resultados mais recentes disponíveis, poderá usar Buscar resultados mais recentes para revisar os resultados de avaliações anteriores.

Analisar E/S com o PowerShell

Você também pode usar o script do PowerShell de Análise de E/S para analisar o desempenho de E/S da VM do 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."
}

Próximas etapas

• Execute práticas recomendadas de avaliação do SQL para identificar possíveis configurações incorretas que possam levar a problemas de desempenho.