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、記憶體和儲存體的數量。相較於儲存體，客戶可相對輕鬆地根據應用程式資源需求調整虛擬機器大小並增加或減少虛擬機器。由於 IOPS 和輸送量可能會在 VM 層級節流，因此請根據效能需求和工作負載成本選擇適當的 VM 大小。

如果要移轉至 Azure，您可以使用 Data Migration Assistant 和 SKU 建議等工具，來分析您目前的 SQL Server 組態和使用情況，並建議 Azure 中工作負載的最佳 VM 大小。

下列 Azure 計量用於判斷工作負載是否因超過 VM 所施加的限制而節流：

已使用的 VM 快取 IOPS 百分比
已使用的 VM 快取頻寬百分比
已使用的 VM 未快取 IOPS 百分比
已使用的 VM 未快取頻寬百分比

請考慮下列有關 VM 節流的重點：

您可以透過調整 VM 系列內的虛擬機器大小，來增加記憶體、虛擬核心、輸送量和 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 最佳做法評量，以識別可能導致效能問題的潛在組態錯誤。

共用方式為