Анализ производительности ввода-вывода (предварительная версия) — SQL Server на виртуальных машинах Azure

Область применения: SQL Server на виртуальной машине Azure

В этой статье описано, как проанализировать производительность ввода-вывода для SQL Server в Azure Виртуальные машины (виртуальных машинах), чтобы найти проблемы, возникающие из-за превышения ограничений на виртуальные машины и диски данных.

Обзор

Несмотря на то, что различные средства помогают устранять проблемы с производительностью SQL Server, для эффективного работы на виртуальной машине Azure важно понимать, что происходит как на уровне узла, так и в экземпляре SQL Server, где часто корреляция метрик узла с рабочими нагрузками SQL Server может быть проблемой. SQL Server на виртуальных машинах Azure упрощает определение проблем с производительностью, связанных с операцией ввода-вывода (входные и выходные данные в секунду) и регулирование пропускной способности, вызванное превышением ограничений на виртуальные машины и диски данных.

Метрики производительности, демонстрирующие проблему, и потенциальные шаги по ее устранению, доступны в портал Azure и запрашиваемые с помощью Azure CLI.

Область хранилища ресурса виртуальных машин SQL в портал Azure поможет вам:

• Управление конфигурацией хранилища
• Определение регулирования ввода-вывода
• Оценка системы для связанных с вводом-выводом рекомендаций

Общие сведения о метриках

Вкладка "Анализ ввода-вывода" использует метрики Azure для определения задержки диска и регулирования операций ввода-вывода виртуальной машины или диска. Метрики Azure выборки выполняются каждые 30 секунд и объединяются в минуту.

Системные мониторы для регулирования и задержки дисков. Ожидается регулирование и игнорируется, если не существует задержки на диске. Если более 500 миллисекунда задержки диска наблюдается в течение 5-минутного периода, система:

• Углубленный анализ метрик производительности
• Определяет регулируемый ресурс
• Предоставляет потенциальные первопричины и шаги по устранению рисков

В следующей таблице описаны метрики Azure, используемые для выявления проблем с регулированием:

Результаты анализа операций ввода-вывода

На основе анализа метрик производительности за последние 24 часа анализ ввода-вывода определяет, что есть:

• Регулирование не выполняется
• Регулирование уровня ввода-вывода виртуальной машины
• Регулирование уровня дискового ввода-вывода

Нет проблемы с регулированием ввода-вывода

Если у вас возникла проблема с производительностью, но задержка на диске отсутствует, проблема с производительностью не возникает из-за проблемы регулирования ввода-вывода. Вам потребуется изучить другие области. Вы можете использовать контрольный список рекомендаций для SQL Server на виртуальных машинах Azure, чтобы убедиться, что система настроена эффективно или найти полезные ссылки для устранения неполадок с производительностью SQL Server. Если включить функцию оценки рекомендаций SQL, вы увидите полный список рекомендаций для виртуальной машины SQL Server.

Проблема регулирования уровня ввода-вывода виртуальной машины

Виртуальные машины Azure — это облачные вычислительные ресурсы, которые входят в разные серии и размеры различных рабочих нагрузок, каждый из которых имеет различные возможности и характеристики производительности. Как правило, для рабочих нагрузок SQL Server рекомендуется использовать ряды рабочих нагрузок SQL Server, оптимизированные для памяти, такие как серия Ebdsv5, M и Mv2.

Размер виртуальной машины определяет количество виртуальных ЦП, памяти и хранилища, доступных для экземпляра SQL Server. По сравнению с хранилищем, пользователи могут изменять размер виртуальных машин и масштабировать виртуальную машину вверх и вниз в зависимости от потребностей в ресурсах приложения. Так как для регулирования операций ввода-вывода в секунду и пропускной способности на уровне виртуальной машины можно выбрать соответствующий размер виртуальной машины в зависимости от потребностей в производительности и стоимости рабочей нагрузки.

При миграции в Azure можно использовать такие средства, как Помощник по миграции данных и рекомендации SKU, для анализа текущей конфигурации и использования SQL Server и предложения оптимального размера виртуальной машины для рабочей нагрузки в Azure.

Следующие метрики Azure используются для определения регулирования рабочей нагрузки из превышения ограничений, введенных виртуальной машиной:

• Процент использования кэшированных операций ввода/вывода в секунду виртуальной машиной
• Процент использования кэшированной пропускной способности виртуальной машиной
• Процент использования некэшированных операций ввода/вывода в секунду виртуальной машиной
• Процент использования некэшированной пропускной способности виртуальной машиной

Рассмотрим следующие ключевые моменты регулирования виртуальных машин:

• Вы можете увеличить память, виртуальные ядра, пропускную способность и операции ввода-вывода в секунду, изменив размер виртуальной машины в серии виртуальных машин.
• Нельзя уменьшить размер виртуальной машины до уровня, в котором максимальное количество дисков данных превышает максимальное ограничение на размер целевой виртуальной машины.
• Важно определить шаблон регулирования. Например, редкие пики регулирования могут быть устранены путем настройки рабочей нагрузки, в то время как устойчивые пики могут указывать на то, что базовое хранилище не может обрабатывать рабочую нагрузку.

Проблема регулирования уровня диска

Для клиентов виртуальных машин SQL хранилище является наиболее важным аспектом настройки оптимальной производительности, так как изменение хранилища является более сложным, чем изменение размера виртуальной машины. Например, внесение изменений в увеличение операций ввода-вывода в секунду или пропускную способность дисков SSD уровня "Премиум" требует создания нового пула носителей. Таким образом, важно оптимизировать конфигурацию хранилища для цен и производительности во время этапа планирования, чтобы избежать проблем с производительностью после развертывания.

Следующие метрики Azure используются для определения регулирования рабочей нагрузки из превышения ограничений, введенных диском:

• Процент использования операций ввода/вывода в секунду для диска данных

• Процент использования пропускной способности диска данных Рассмотрим следующие ключевые моменты регулирования уровня дискового ввода-вывода:

• Диск данных критически важен для производительности SQL Server. Рекомендуется размещать файлы данных SQL Server (.mdf) и журналов (df) на диске данных.

• Для регулирования на уровне диска данных включите кэширование чтения, если оно доступно.

Процент использования операций ввода/вывода в секунду для диска данных

Метрика "Количество операций ввода-вывода в секунду" на диске измеряет потребление операций ввода-вывода в секунду на уровне диска. Как правило, потребности с высоким уровнем операций ввода-вывода в секунду связаны с высоко транзакциальными приложениями и рабочими нагрузками на основе OLTP.   Следующие сценарии или условия могут превышать ограничения операций ввода-вывода в секунду на диск данных.

• Высокая рабочая нагрузка транзакций (IOPS): если приложение обрабатывает большой объем транзакций базы данных, включающих частые операции чтения и записи, он может быстро использовать выделенные операции ввода-вывода в секунду. 
• Неэффективные запросы: плохо оптимизированные запросы SQL или операции извлечения данных могут привести к чрезмерному количеству операций ввода-вывода, потребляя больше операций ввода-вывода, чем ожидалось. 
• Одновременные пользователи: если несколько пользователей или сеансов одновременно обращаются к базе данных и создают запросы ввода-вывода, совокупный эффект может привести к достижению ограничения операций ввода-вывода. 
• Претенденты на ресурсы. Если базовая физическая инфраструктура сильно используется для других клиентов или рабочих нагрузок, это может повлиять на доступные операции ввода-вывода в секунду для виртуальной машины. 
• Временные пики: временные пики рабочей нагрузки, такие как пакетная обработка или миграция данных, могут привести к внезапному увеличению спроса на операции ввода-вывода, которые превышают выделенные операции ввода-вывода. 
• Небольшой размер диска: если размер подготовленного диска данных относительно мал, емкость операций ввода-вывода в секунду может быть ограничена. Отдельные небольшие диски имеют более низкие ограничения операций ввода-вывода в секунду, и если требования приложения превышают это ограничение, "Процент использования дисков данных в секунду" достигает 100 %. 
• Недостаточно типа диска. Выбор типа диска с низкой производительностью (например, hdD уровня "Стандартный") для приложения с интенсивным вводом-выводом может привести к ограничениям операций ввода-вывода. 
• Неоптимизованный размер полосы диска: если конфигурация хранилища не оптимизирована для рабочей нагрузки, это может привести к неоптимальной производительности операций ввода-вывода в секунду. 

Чтобы избежать превышения ограничения операций ввода-вывода в секунду на диск данных, выполните следующие действия.

• Оптимизируйте запросы SQL и структуру базы данных, чтобы свести к минимуму ненужные операции ввода-вывода. 
• Выберите подходящий тип диска (SSD уровня "Стандартный" или SSD уровня "Премиум"), соответствующий требованиям к операций ввода-вывода в секунду приложения. 
• Используйте более крупные размеры дисков, чтобы увеличить доступную емкость операций ввода-вывода в секунду. 
• Распределяйте операции ввода-вывода на нескольких дисках данных с помощью конфигураций RAID. 

Процент использования пропускной способности диска данных

Процент использования метрики пропускной способности диска данных Azure измеряет использование пропускной способности на уровне диска. Как правило, требования к высокой пропускной способности связаны с хранилищем данных, киоском данных, отчетом, ETL и другими рабочими нагрузками аналитики данных.

Следующие сценарии или условия могут превышать ограничения пропускной способности диска данных:

• Передача больших данных: часто масштабируемые передачи данных приложения между диском и базой данных SQL могут быстро использовать доступную пропускную способность диска данных. 
• Массовая загрузка данных: операции передачи дисков, связанные с массовыми вставками, обновлениями или импортами, могут привести к высокой пропускной способности. 
• Хранение данных или аналитика: приложения, включающие большие объемы хранения данных, обработку аналитики или отчеты, могут создавать существенное перемещение данных, что может привести к превышению ограничений пропускной способности.
• Технология избыточности и репликация с высоким уровнем избыточности данных: копирование данных, связанное с использованием репликации на основе дисков, зеркального отображения данных или других механизмов избыточности, может способствовать насыщенности пропускной способности. 
• Резервное копирование и восстановление данных: частые резервные копии данных, моментальные снимки или процессы восстановления могут использовать значительную пропускную способность диска данных. 
• Параллельное выполнение запросов: параллельные запросы, включающие сканирование больших данных или соединения, могут привести к существенному перемещению данных, что приводит к использованию пропускной способности. 
• Повышенный сетевой трафик: высокая сетевая активность, например передача данных между виртуальной машиной и другими ресурсами, может косвенно повлиять на доступность пропускной способности диска данных. 
• Недостаточного типа диска. Выбор типа диска с более низкой производительностью для приложения с высокими требованиями к передаче данных может привести к превышению ограничения пропускной способности. 
• Одновременные операции с интенсивными данными. Объединенный эффект нескольких параллельных процессов или сеансов, обращаюющихся к данным на одном диске, может привести к достижению ограничения пропускной способности. 
• Неоптимизированные запросы или процессы ETL: плохо оптимизированные запросы SQL или извлечение, преобразование, загрузка (ETL) могут привести к чрезмерному перемещению данных, что приводит к чрезмерному потреблению пропускной способности. 

Рассмотрите следующие действия, чтобы избежать превышения ограничения пропускной способности диска данных.

• Оптимизируйте операции передачи данных, чтобы свести к минимуму ненужные перемещения данных. 
• Рекомендуется использовать типы дисков с более высокой производительностью, которые обеспечивают большую пропускную способность, например SSD уровня "Премиум" или SSD уровня "Премиум" версии 2.
• Распределяйте данные по нескольким дискам с помощью таких методов, как секционирование или сегментирование.
• Оптимизация и параллелизация запросов и обработки данных для уменьшения перемещения данных.
• Используйте механизмы сжатия и эффективного хранилища данных, чтобы уменьшить объем передаваемых данных.
• Отслеживайте метрики производительности и масштабируйте конфигурации хранилища по мере необходимости. SSD уровня "Премиум" версии 2 позволяет клиентам масштабировать операции ввода-вывода в секунду и пропускную способность по требованию.
• Важно регулярно отслеживать и анализировать метрики производительности, чтобы определить причину ограничений операций ввода-вывода в секунду и предпринять соответствующие действия для оптимизации производительности хранилища для виртуальной машины SQL.

Рекомендации, связанные с вводом-выводом

Так как плохо настроенные системы хранения могут привести к проблемам с производительностью, вы можете использовать область хранилища в портал Azure для запуска определенного диска подмножества правил оценки рекомендаций SQL для выявления проблем конфигурации хранилища с SQL Server на виртуальных машинах Azure. Функция рекомендаций SQL основана на API оценки SQL.

Полный список рекомендаций можно просмотреть на сайте GitHub. Отфильтровав столбец идентификаторов в правилах на сайте GitHub, можно просмотреть правила конфигурации диска виртуальной машины SQL, которые проверяются на вкладке рекомендаций по настройке ввода-вывода на панели хранилища для ресурса виртуальных машин SQL в портал Azure.

На вкладке рекомендаций, связанных с вводом-выводом, используйте оценку запуска для запуска оценки конфигурации, которая должна занять несколько минут (если не существует большого количества баз данных и объектов). Кроме того, если отображается метка времени для последних доступных результатов, можно использовать получение последних результатов для просмотра результатов из предыдущих оценок.

Анализ ввода-вывода с помощью PowerShell

Вы также можете использовать скрипт PowerShell для анализа производительности ввода-вывода виртуальной машины 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."
}

Следующие шаги

• Выполните оценку рекомендаций SQL, чтобы определить потенциальные неправильные конфигурации, которые могут привести к проблемам с производительностью.