你当前正在访问 Microsoft Azure Global Edition 技术文档网站。如果需要访问由世纪互联运营的 Microsoft Azure 中国技术文档网站，请访问 https://docs.azure.cn。

I/O 性能分析（预览版）- Azure VM 上的 SQL Server

项目
05/23/2024

本文介绍如何分析 Azure 虚拟机 (VM) 上的 SQL Server 的 I/O 性能，以查找因超出虚拟机和数据磁盘限制而导致的问题。

注意

Azure 门户中的 Azure VM 上的 SQL Server 的 I/O 分析目前为预览版。

概述

尽管各种工具可帮助你排查 SQL Server 性能问题，但要在 Azure VM 上有效地解决问题，重要的是要了解主机级别和 SQL Server 实例发生的情况，通常，将主机指标与 SQL Server 工作负载关联起来可能是一个挑战。通过 Azure VM 上的 SQL Server，可以轻松识别因超出虚拟机和数据磁盘限制而导致的 IOPS（每秒输入/输出）和吞吐量限制所导致的性能问题。

显示问题的性能指标以及解决该问题的潜在步骤在 Azure 门户中提供，并且可使用 Azure CLI 进行查询。

Azure 门户中“SQL 虚拟机”资源的“存储”窗格可帮助你：

管理你的存储配置
识别 I/O 限制
评估系统以获得与 I/O 相关的最佳做法

了解指标

“I/O 分析”选项卡依赖于 Azure 指标来识别磁盘延迟，以及 VM 或磁盘 I/O 限制。 Azure 指标每 30 秒采样一次，并且会合并到一分钟。

系统将监视限制和磁盘延迟。有些限制是预期的，除非还存在磁盘延迟，否则将被忽略。如果在连续 5 分钟的时间内观察到超过 500 毫秒的磁盘延迟，则系统会：

更深入地了解性能指标
标识受限制的资源
提供潜在的根本原因和缓解步骤

下表介绍用于识别有问题的限制问题的 Azure 指标：

Azure 指标	度量值说明	有问题的条件	I/O 限制结论
磁盘延迟（预览）	在监视期间完成数据磁盘 IO 的平均时间。值以毫秒为单位。	> 连续 5 分钟时间段内为 500 毫秒	系统存在延迟问题，可以进一步调查潜在限制。
VM 使用的缓存 IOPS 的百分比	通过将完成的总 IOPS 与最大缓存虚拟机 IOPS 限制相比计算得出的百分比。	> 连续 5 分钟时间段内 = 95%	存在 VM 限制。在 SQL 虚拟机上运行的应用程序充分利用可用于虚拟机的最大缓存 IOPS 容量 - 应用程序的存储需求超出了虚拟机的基础存储配置提供的缓存 IOPS。
已使用的 VM 缓存带宽百分比	通过将完成的总磁盘吞吐量与最大缓存虚拟机吞吐量相比计算得出的百分比。	> 连续 5 分钟时间段内 = 95%	存在 VM 限制。在 SQL 虚拟机上运行的应用程序正在利用最大可用的缓存磁盘带宽进行数据传输 - 应用程序的数据传输需求超出了虚拟机的基础存储配置提供的缓存带宽资源。
VM 使用的未缓存 IOPS 的百分比	通过将虚拟机上完成的总 IOPS 与最大非缓存虚拟机 IOPS 限制相比计算得出的百分比。	> 连续 5 分钟时间段内 = 95%	存在 VM 限制。在 SQL 虚拟机上运行的应用程序利用可用于虚拟机的最大可允许非缓存 IOPS 容量 - 应用程序的存储需求超出了虚拟机的基础存储配置提供的非缓存 IOPS。
已使用的 VM 未缓存带宽百分比	通过将虚拟机上完成的总磁盘吞吐量与最大预配虚拟机吞吐量相比计算得出的百分比。	> 连续 5 分钟时间段内 = 95%	存在 VM 限制。在 SQL 虚拟机上运行的应用程序正在利用最大可允许非缓存磁盘带宽进行数据传输 - 应用程序的数据传输需求超出了虚拟机的基础存储配置提供的非缓存带宽资源。
已使用的数据磁盘 IOPS 的百分比	通过将完成的数据磁盘 IOPS 与预配的数据磁盘 IOPS 相比计算得出的百分比。	> 连续 5 分钟时间段内 = 95%	存在数据磁盘限制。在 SQL 虚拟机上运行的应用程序达到了预配数据磁盘的 IOPS 限制 - 应用程序的存储需求超出了所选磁盘配置的性能功能。
已使用的数据磁盘带宽的百分比	通过将完成的数据磁盘吞吐量与预配的数据磁盘吞吐量相比计算得出的百分比。	> 连续 5 分钟时间段内 = 95%	存在数据磁盘限制。在 SQL 虚拟机上运行的应用程序达到了预配数据磁盘的 IOPS 限制 - 应用程序的存储需求超出了所选磁盘配置的性能功能。

I/O 分析发现结果

基于对过去 24 小时内性能指标的分析，I/O 分析确定存在以下情况：

无限制
VM 级 I/O 限制
磁盘级 I/O 限制

无 I/O 限制问题

如果遇到性能问题，但没有磁盘延迟，则性能问题不是因 I/O 限制问题造成的。你需要调查其他区域。可以使用 Azure VM 上的 SQL Server 的最佳做法检查列表来确保系统配置高效，或查找用于排查 SQL Server 性能问题的有用链接。如果启用 SQL 最佳做法评估功能，可以查看适用于 SQL Server VM 的建议的完整列表。

VM 级 I/O 限制问题

Azure 虚拟机是适用于各种工作负载的不同系列和大小的基于云的计算资源，每个虚拟机都具有不同的功能和性能特征。对于 SQL Server 工作负载，通常，适用于 SQL Server 工作负载的建议系列是内存优化系列，例如 Ebdsv5、M 和 Mv2 系列。

VM 的大小决定了可用于 SQL Server 实例的 vCPU 数量、内存和存储量。与存储相比，客户相对容易调整其虚拟机大小，并根据应用程序资源需求纵向扩展和缩减 VM。由于可以在 VM 级别限制 IOPS 和吞吐量，因此可以根据工作负载的性能需求和成本选择适当的 VM 大小。

如果要迁移到 Azure，可以使用数据迁移助手和 SKU 推荐等工具分析当前的 SQL Server 配置和使用情况，并为 Azure 中的工作负载建议最佳 VM 大小。

以下 Azure 指标用于确定工作负载不超过虚拟机施加的限制：

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。 
临时峰值：工作负载（如批处理或数据迁移）的临时峰值可能导致 I/O 需求突然增加，超出分配的 IOPS。 
小型磁盘大小：如果预配的数据磁盘大小相对较小，则 IOPS 容量可能会受限制。单个较小的磁盘具的 IOPS 限制较低，如果应用程序的需求超过此限制，则“已使用的数据磁盘 IOPS 的百分比”达到 100%。 
磁盘类型不足：为 I/O 密集型应用程序选择性能较低的磁盘类型（例如标准 HDD）可能会导致 IOPS 限制。 
未优化的磁盘条带大小：如果存储配置未针对工作负载进行优化，可能会导致 IOPS 性能欠佳。

请考虑以下步骤以避免超出数据磁盘的 IOPS 限制：

请优化 SQL 查询和数据库设计，以最大程度减少不必要的 I/O 操作。 
请选择与应用程序的 IOPS 要求相匹配的适当磁盘类型（标准 SSD 或高级 SSD）。 
请使用更大的磁盘大小来增加可用的 IOPS 容量。 
请使用 RAID 配置跨多个数据磁盘分配 I/O。

已使用的数据磁盘带宽的百分比

已使用的数据磁盘带宽的百分比 Azure 指标度量磁盘级别的带宽利用率。高吞吐量需求通常与数据仓库、数据市场、报告、ETL 和其他数据分析工作负载相关联。

以下应用场景或条件可能会超过数据磁盘带宽限制：

大型数据传输：磁盘与 SQL 数据库之间频繁的大规模应用程序数据传输可能会快速消耗可用的数据磁盘带宽。 
批量数据加载：与批量数据插入、更新或导入关联的磁盘传输活动可能会导致高带宽消耗。 
数据仓库或分析：涉及大量数据仓库、分析处理或报告的应用程序可能会产生大量数据移动，从而可能会导致超出带宽限制。
高数据冗余技术/复制：与使用基于磁盘的复制、数据镜像或其他冗余机制关联的数据复制可能会导致带宽饱和。 
数据备份和还原：频繁的数据备份、快照或还原过程可能会消耗大量的数据磁盘带宽。 
并行查询执行：涉及大量数据扫描或联接的并行查询可能会导致大量数据移动，从而导致带宽占用。 
提升的网络流量：高网络活动（例如虚拟机和其他资源之间的数据传输）可能会间接影响数据磁盘带宽可用性。 
磁盘类型不足：为具有高数据传输要求的应用程序选择性能较低的磁盘类型可能会导致超出带宽限制。 
并发的数据密集型操作：访问和传输同一磁盘上的数据的多个并发进程或会话的组合效果可能会导致达到带宽限制。 
未优化的查询或 ETL 进程：优化不佳的 SQL 查询或提取、转换、加载 (ETL) 进程可能会导致过多的数据移动，从而导致带宽消耗过多。

请考虑以下步骤以避免超出数据磁盘的带宽限制：

请优化数据传输操作，以最大程度减少不必要的数据移动。 
请考虑使用提供更大带宽容量的较高性能磁盘类型，例如高级 SSD 或高级 SSD v2。
请使用分区或分片等技术跨多个磁盘分配数据。
请优化和并行化查询和数据处理，以减少数据移动。
请使用压缩和高效的数据存储机制来减少传输的数据量。
请监视性能指标并根据需要纵向扩展存储配置。 高级 SSD v2 使客户能够按需缩放其 IOPS 和吞吐量。
请务必定期监视和分析性能指标，以确定 IOPS 限制的原因，并采取适当措施来优化 SQL 虚拟机的存储性能。

提示

定期监视性能指标、优化数据传输操作和优化磁盘配置，以确保 SQL 虚拟机的数据磁盘的性能保持最佳状态，且不超过限制。

由于配置不佳的存储系统可能会导致性能问题，因此可以使用 Azure 门户中的“存储”窗格运行特定于磁盘的 SQL 最佳做法评估规则子集，以确定 Azure VM 上的 SQL Server 存在的存储配置问题。 SQL 最佳做法功能基于 SQL 评估 API。

你可以查看 GitHub 上的建议完整列表。通过对 GitHub 上的规则筛选 id 列，可以看到在 Azure 门户中“SQL 虚拟机”资源的“存储”窗格的“I/O 配置最佳做法”选项卡上验证的 SQL VM 磁盘配置规则。

AzSqlVmSize
AzDataDiskCache
AzDataDiskStriping
AzDataOnDataDisks
AzDbDefaultLocation
AzDiskColumnCount

AzErrorLogLocation
AzPremSsdDataFiles
AzTempDbFileLocation
AzTranLogDiskCache
NtfsBlockSizeNotFormatted
LockedPagesInMemory

在“与 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 最佳做法评估，以确定可能导致性能问题的潜在配置错误。

通过