Упражнение — мониторинг и устранение проблем с производительностью

  • 10 мин

В этом упражнении вы узнаете, как отслеживать и устранять проблемы с производительностью в SQL Azure с помощью знакомых и новых средств и возможностей.

Настройка. Использование скриптов для развертывания База данных SQL Azure

Сеанс терминала Azure Cloud Shell в правой части позволяет взаимодействовать с Azure с помощью браузера. В этом упражнении вы запустите сценарий для создания среды, экземпляр База данных SQL Azure с базой AdventureWorks данных. (Используется более простая, упрощенная база AdventureWorksLT данных, но мы будем вызывать ее AdventureWorks , чтобы предотвратить путаницу.) В скрипте вам будет предложено ввести пароль и локальный IP-адрес, чтобы разрешить устройству подключаться к базе данных.

Выполнение сценария занимает 3–5 минут. Не забудьте записать свой пароль, уникальный идентификатор и регион. Они не будут отображаться снова.

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

    (Invoke-WebRequest -Uri "https://ipinfo.io/ip").Content

  2. В Azure Cloud Shell в правой части введите приведенный ниже код и при появлении запроса введите сложный пароль и локальный общедоступный IP-адрес, полученный на предыдущем шаге. Нажмите клавишу ВВОД, чтобы выполнить последнюю строку сценария.

    $adminSqlLogin = "cloudadmin"
$password = Read-Host "Your username is 'cloudadmin'. Please enter a password for your Azure SQL Database server that meets the password requirements"
# Prompt for local ip address
$ipAddress = Read-Host "Disconnect your VPN, open PowerShell on your machine and run '(Invoke-WebRequest -Uri "https://ipinfo.io/ip").Content'. Please enter the value (include periods) next to 'Address':"
# Get resource group and location and random string
$resourceGroup = Get-AzResourceGroup | Where ResourceGroupName -like "<rgn>[sandbox resource group name]</rgn>"
$resourceGroupName = "<rgn>[sandbox resource group name]</rgn>"
$uniqueID = Get-Random -Minimum 100000 -Maximum 1000000
$storageAccountName = "mslearnsa"+$uniqueID
$location = $resourceGroup.Location
$serverName = "aw-server$($uniqueID)"

  3. В Azure Cloud Shell выполните приведенный ниже сценарий. Сохраните выходные данные. Эти сведения понадобятся на протяжении модуля. После вставки кода нажмите клавишу ВВОД, чтобы последняя строка кода вывела нужные выходные данные.

    Write-Host "Please note your unique ID for future exercises in this module:"  
Write-Host $uniqueID
Write-Host "Your resource group name is:"
Write-Host $resourceGroupName
Write-Host "Your resources were deployed in the following region:"
Write-Host $location
Write-Host "Your server name is:"
Write-Host $serverName

    Совет

    Сохраните выходные данные и запишите пароль, уникальный идентификатор и сервер. Эти элементы потребуются во всем модуле.

  4. Выполните следующий сценарий, чтобы развернуть экземпляр базы данных SQL Azure и логический сервер с примером AdventureWorks. Этот сценарий добавляет IP-адрес в качестве правила брандмауэра, включает расширенную защиту данных и создает учетную запись хранения для использования в остальных упражнениях этого модуля. Выполнение сценария может занять несколько минут и будет приостановлено несколько раз. Дождитесь появления командной строки.

    # The logical server name has to be unique in the system
$serverName = "aw-server$($uniqueID)"
# The sample database name
$databaseName = "AdventureWorks"
# The storage account name has to be unique in the system
$storageAccountName = $("sql$($uniqueID)")
# Create a new server with a system wide unique server name
$server = New-AzSqlServer -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -Location $location `
    -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminSqlLogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))
# Create a server firewall rule that allows access from the specified IP range and all Azure services
$serverFirewallRule = New-AzSqlServerFirewallRule `
    -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -FirewallRuleName "AllowedIPs" `
    -StartIpAddress $ipAddress -EndIpAddress $ipAddress 
$allowAzureIpsRule = New-AzSqlServerFirewallRule `
    -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -AllowAllAzureIPs
# Create a database
$database = New-AzSqlDatabase  -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -DatabaseName $databaseName `
    -SampleName "AdventureWorksLT" `
    -Edition "GeneralPurpose" -Vcore 2 -ComputeGeneration "Gen5"
# Enable Advanced Data Security
$advancedDataSecurity = Enable-AzSqlServerAdvancedDataSecurity `
    -ResourceGroupName $resourceGroupName `
    -ServerName $serverName
# Create a Storage Account
$storageAccount = New-AzStorageAccount -ResourceGroupName $resourceGroupName `
    -AccountName $storageAccountName `
    -Location $location `
    -Type "Standard_LRS"

  5. На своем локальном устройстве откройте SQL Server Management Studio (SSMS), чтобы создать подключение к логическому серверу.

  6. В диалоговом окне "Подключение к серверу" укажите следующие сведения:

    Поле значение
    Тип сервера Ядро СУБД (по умолчанию).
    Имя сервера Значение $serverName, возвращенное в Cloud Shell, а также остальную часть URI. Например, aw-server<unique ID>.database.windows.net.
    Проверка подлинности Проверка подлинности SQL Server (по умолчанию).
    Имя входа cloudadmin Значение adminSqlLogin, назначенное на шаге 1 этого упражнения.
    Пароль Пароль, указанный на шаге 1 этого упражнения.
    Запомнить пароль включен

  7. Нажмите Подключиться.

    Снимок экрана: диалоговое окно подключения для Базы данных SQL в SSMS.

    Примечание.

    В зависимости от локальной конфигурации (например, VPN), ваш IP-адрес клиента может отличаться от IP-адреса, использованного порталом Azure во время развертывания. Если это так, вы получите следующее сообщение: "Ip-адрес клиента не имеет доступа к серверу. Войдите в учетную запись Azure и создайте новое правило брандмауэра для включения доступа". Если вы получите это сообщение, войдите с помощью учетной записи, которую вы используете для песочницы, и добавьте правило брандмауэра для IP-адреса клиента. Все эти действия можно выполнить с помощью мастера в SSMS.

Подготовка упражнения путем загрузки и редактирования сценариев

Все скрипты этого упражнения можно найти в папке 04-Performance\monitor_and_scale в репозитории GitHub, который вы клонировали или скачанный ZIP-файл. Подготовим упражнение путем загрузки и редактирования сценариев.

  1. В SSMS в обозреватель объектов разверните папку "Базы данных" и выберите базу данных AdventureWorks.

  2. Выберите "Открыть>файл" и откройте скрипт dmexecrequests.sql. > Окно редактора запросов должно выглядеть как в следующем тексте:

    SELECT er.session_id, er.status, er.command, er.wait_type, er.last_wait_type, er.wait_resource, er.wait_time
FROM sys.dm_exec_requests er
INNER JOIN sys.dm_exec_sessions es
ON er.session_id = es.session_id
AND es.is_user_process = 1;

  3. Используйте тот же метод в SSMS для загрузки сценария dmdbresourcestats.sql. Новое окно редактора запросов должно выглядеть как в следующем тексте:

    SELECT * FROM sys.dm_db_resource_stats;

    Это динамическое административное представление будет отслеживать общее использование ресурсов рабочей нагрузкой в базе данных SQL Azure. Например, оно отслеживает ресурсы ЦП, ввод-вывод и память.

  4. Откройте и измените скрипт sqlworkload.cmd (который будет использовать программу ostress.exe).

    • Замените свой unique_id, сохраненный в сценарии развертывания, в имени сервера.
    • Замените пароль, использовавшийся для входа на сервер Базы данных SQL Azure, на -P parameter.
    • Сохраните изменения в скрипте.

Выполнение рабочей нагрузки

В этой задаче вы запустите рабочую нагрузку в запросе T-SQL, чтобы наблюдать за производительностью одновременных пользователей.

  1. Используйте SSMS, чтобы открыть файл скрипта topcustomersales.sql для наблюдения за запросом. Не запускайте запрос из SSMS. Окно редактора запросов должно выглядеть как в следующем тексте:

    DECLARE @x int
DECLARE @y float
SET @x = 0;
WHILE (@x < 10000)
BEGIN
SELECT @y = sum(cast((soh.SubTotal*soh.TaxAmt*soh.TotalDue) as float))
FROM SalesLT.Customer c
INNER JOIN SalesLT.SalesOrderHeader soh
ON c.CustomerID = soh.CustomerID
INNER JOIN SalesLT.SalesOrderDetail sod
ON soh.SalesOrderID = sod.SalesOrderID
INNER JOIN SalesLT.Product p
ON p.ProductID = sod.ProductID
GROUP BY c.CompanyName
ORDER BY c.CompanyName;
SET @x = @x + 1;
END
GO

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

  2. В командной строке PowerShell введите следующую команду, чтобы перейти к нужному каталогу для этого упражнения. Замените <base directory> идентификатором пользователя и путем для этого модуля.

    cd <base directory>\04-Performance\monitor_and_scale

  3. Выполните следующую команду, чтобы запустить рабочую нагрузку:

    .\sqlworkload.cmd

    Этот сценарий будет использовать 10 параллельных пользователей, выполняющих запрос рабочей нагрузки два раза. Обратите внимание, что сам сценарий запускает один пакет, но повторяется 10 000 раз. Он также присваивает полученный результат переменной, таким образом устраняя почти весь трафик результирующего набора к клиенту. Это не обязательно, но помогает показать "чистую" рабочую нагрузку ЦП, выполняемую полностью на сервере.

    Совет

    Если вы не видите характер использования ЦП с этой рабочей нагрузкой в своей среде, можно настроить параметр -n parameter для числа пользователей и параметр -r parameter для итераций.

    Выходные данные в командной строке должны выглядеть примерно так, как показано ниже:

    [datetime] [ostress PID] Max threads setting: 10000
[datetime] [ostress PID] Arguments:
[datetime] [ostress PID] -S[server].database.windows.net
[datetime] [ostress PID] -isqlquery.sql
[datetime] [ostress PID] -U[user]
[datetime] [ostress PID] -dAdventureWorks
[datetime] [ostress PID] -P********
[datetime] [ostress PID] -n10
[datetime] [ostress PID] -r2
[datetime] [ostress PID] -q
[datetime] [ostress PID] Using language id (LCID): 1024 [English_United States.1252] for character formatting with NLS: 0x0006020F and Defined: 0x0006020F
[datetime] [ostress PID] Default driver: SQL Server Native Client 11.0
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_1.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_2.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_3.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_4.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_5.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_6.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_7.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_8.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_9.out]
[datetime] [ostress PID] Starting query execution...
[datetime] [ostress PID]  BETA: Custom CLR Expression support enabled.
[datetime] [ostress PID] Creating 10 thread(s) to process queries
[datetime] [ostress PID] Worker threads created, beginning execution...

Наблюдение за производительностью рабочей нагрузки

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

  1. Выполните запрос в среде SSMS, загруженной ранее для отслеживания dm_exec_requests (dmexecrequests.sql), чтобы понаблюдать за активными запросами. Запустите этот запрос пять или шесть раз и просмотрите некоторые из результатов:

    SELECT er.session_id, er.status, er.command, er.wait_type, er.last_wait_type, er.wait_resource, er.wait_time
FROM sys.dm_exec_requests er
INNER JOIN sys.dm_exec_sessions es
ON er.session_id = es.session_id
AND es.is_user_process = 1;

    Вы должны увидеть, что многие запросы имеют состояние RUNNABLEи last_wait_type имеет значение SOS_SCHEDULER_YIELD. Один из индикаторов многих RUNNABLE запросов и многих SOS_SCHEDULER_YIELD ожиданий — это возможное отсутствие ресурсов ЦП для активных запросов.

    Примечание.

    Вы можете увидеть один или несколько активных запросов с помощью команды SELECT и одного wait_type из XE_LIVE_TARGET_TVFних. Это запросы, выполняемые службами, управляемыми Майкрософт. Они помогают использовать такие возможности, как аналитика производительности, с помощью расширенных событий. Майкрософт не публикует сведения об этих сеансах.

    Оставьте окно редактора запросов открытым. Вы снова запустите его в следующем упражнении.

  2. Выполните запрос в среде SSMS, загруженной ранее для отслеживания sys.dm_db_resource_stats (dmdbresourcestats.sql). Выполните запрос, чтобы просмотреть результаты этого динамического административного представления три или четыре раза.

    SELECT * FROM sys.dm_db_resource_stats;

    Это динамическое административное представление делает моментальный снимок использования ресурсов для базы данных каждые 15 секунд (хранится в течение 1 часа). Вы увидите, что столбец avg_cpu_percent близок к 100 % для нескольких моментальных снимков. Это признак того, что рабочая нагрузка подходит к ограничениям на использование ресурсов ЦП для базы данных.

    Для локальной среды SQL Server обычно используется средство, конкретное для операционной системы, для отслеживания общего использования ресурсов такого ЦП. Например, для этой цели можно использовать Монитор производительности Windows. Если вы выполнили этот пример на локальном сервере SQL Server или SQL Server на виртуальной машине с двумя ЦП, вы увидите почти 100 процентов использования ЦП на сервере.

    Примечание.

    Вы можете запустить другое динамическое административное представление sys.resource_statsв контексте базы данных сервера master База данных SQL Azure, чтобы просмотреть использование ресурсов для всех баз данных База данных SQL Azure, связанных с сервером. Это представление менее детализировано и отображает использование ресурсов каждые пять минут (хранится в течение 14 дней).

    Оставьте окно редактора запросов открытым. Вы снова запустите его в следующем упражнении.

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

    [datetime] [ostress PID] Total IO waits: 0, Total IO wait time: 0 (ms)
[datetime] [ostress PID] OSTRESS exiting normally, elapsed time: 00:01:22.637

    Длительность может отличаться, но обычно она составляет не менее 1–3 минут. Обязательно дождитесь завершения выполнения. По завершении рабочей нагрузки вы вернетесь в командную строку.

Использование хранилища запросов для дальнейшего анализа

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

Хранилище запросов поставляется с серией системных представлений каталога для просмотра данных о производительности. Среда SSMS предоставляет отчеты с помощью этих представлений.

  1. С помощью обозревателя объектов в SSMS откройте папку хранилища запросов, чтобы найти отчет Самые ресурсоемкие запросы.

    Снимок экрана хранилища запросов.

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

    Снимок экрана отчета о топе ресурсоемких запросов.

    Показанный запрос представляет собой SQL-запрос по продажам клиентов из рабочей нагрузки. Этот отчет содержит три компонента: запросы с высокой общей длительностью (можно изменить метрику), связанный план запроса и статистика времени выполнения, а также связанный план запроса в визуальной карте.

  3. Выберите линейчатую диаграмму для запроса (query_id может иметь другое значение в вашей системе). Полученные результаты должны иметь следующий вид:

    Снимок экрана идентификатора запроса.

    Можно будет увидеть общую длительность запроса и текст запроса.

  4. Справа от этой линейчатой диаграммы находится диаграмма статистики по плану этого запроса. Наведите указатель мыши на точку, связанную с планом. Полученные результаты должны иметь следующий вид:

    Снимок экрана статистики медленного запроса.

    Обратите внимание на среднюю длительность запроса. Время может отличаться, но сравните среднюю длительность со средним временем ожидания для этого запроса. Далее мы предоставим улучшение производительности, и вы сделаете это сравнение еще раз, чтобы увидеть разницу.

  5. Последний компонент является визуальным планом запроса. План запроса для этого запроса выглядит, как на следующем рисунке:

    Снимок экрана плана запроса рабочей нагрузки.

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

  6. После отчета о наиболее потребляемых ресурсах в SSMS отчет называется статистикой ожидания запросов. Благодаря более ранней диагностике мы знаем, что большое количество запросов постоянно находилось в состоянии RUNNABLE и что использовалось почти 100 % ресурсов ЦП. Хранилище запросов предоставляется с отчетами, чтобы узнать о возможных узких местах производительности из-за ожидания ресурсов. Щелкните этот отчет и наведите указатель мыши на линейчатую диаграмму. Полученные результаты должны иметь следующий вид:

    Снимок экрана топа статистики ожидания.

    Можно увидеть, что чаще всего выполняется ожидание ЦП (это эквивалентно wait_type SOS_SCHEDULER_YIELD, который можно увидеть в sys.dm_os_wait_stats). Также здесь отображается среднее время ожидания.

  7. Щелкните линейчатую диаграмму ЦП в отчете. Запрос, чаще всего ожидающий ЦП, — это запрос из используемой вами рабочей нагрузки.

    Снимок экрана: статистика по самым частым ожидающим запросам.

    Обратите внимание, что среднее время ожидания ЦП в этом запросе составляет значительный процент от общей средней длительности запроса.

    Учитывая собранную информацию, без настройки запроса для нашей рабочей нагрузки требуется больше ресурсов ЦП, чем мы развернули для экземпляра Базы данных SQL Azure.

  8. Закройте оба отчета хранилища запросов. Эти же отчеты будут использоваться в следующем упражнении.

Наблюдение за производительностью с помощью Azure Monitor

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

  1. Откройте портал Azure, а затем найдите экземпляр базы данных SQL AdventureWorks. На панели "Обзор" для базы данных выберите вкладку "Мониторинг". Представление по умолчанию в области мониторинга — использование вычислений:

    Снимок экрана: портал Azure с медленным запросом.

    В этом примере процент ЦП составляет около 100 процентов для недавнего диапазона времени. На этой диаграмме показано использование ресурсов (по умолчанию — ЦП и операции ввода-вывода) за последний час. Она постоянно обновляется. Выберите диаграмму, чтобы настроить ее и просмотреть сведения об использовании других ресурсов.

  2. В меню базы данных SQL выберите " Добавить метрики". Другим способом просмотра метрик Использование вычислений и других метрик, автоматически собираемых Azure Monitor для Базы данных SQL Azure, является использование обозревателя метрик.

    Примечание.

    Использование вычислений — это предварительно определенное представление обозревателя метрик. Если выбрать раскрывающийся список метрик в окне "Добавление метрик", вы увидите следующие результаты:

    Снимок экрана метрик Azure Monitor.

    Как видно на снимке экрана, с помощью обозревателя метрик можно просматривать несколько метрик. Представление обозревателя метрик по умолчанию составляет 24-часовой период с пятью минутами детализации. Представление использования вычислений — это последний час с детализацией за одну минуту (которую можно изменить). Чтобы просмотреть то же представление, выберите процент ЦП и измените запись в течение одного часа. Степень детализации изменится на одну минуту. Это должно выглядеть, как на следующем рисунке:

    Снимок экрана: метрики Azure Monitor, включая использование ЦП с детализацией по минутам.

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

Журналы Azure Monitor

В этом упражнении вы не настроили журнал Azure Monitor, но стоит обсудить, как может выглядеть журнал в сценарии использования ресурсов ЦП. Журналы Azure Monitor могут предоставлять записи за гораздо более долгий период, чем метрики Azure.

Если вы настроили журналы Azure Monitor с рабочей областью Log Analytics, можно использовать следующий запрос Kusto, чтобы просмотреть те же результаты использования ЦП для базы данных:

AzureMetrics
| where MetricName == 'cpu_percent'
| where Resource == "ADVENTUREWORKS"
| project TimeGenerated, Average
| render columnchart

Полученные результаты будут иметь следующий вид:

Снимок экрана запроса на измерение использования ЦП.

Журналы Azure Monitor дают задержку при первой настройке диагностики журналов для базы данных, поэтому для появления этих результатов может потребоваться некоторое время.

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