Упражнение — оптимизация производительности приложения

Завершено

В этом упражнении вы увидите новый сценарий производительности и устраните его, оптимизируя приложение и запросы.

Оптимизация производительности приложений с помощью SQL Azure

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

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

При перемещении теста в базу данных SQL Azure с использованием уровня "Общего назначения" (8 виртуальных ядер) рабочая нагрузка INSERT выполняется медленнее. Нужно ли изменить цель или уровень служб для поддержки новой рабочей нагрузки или обратить внимание на приложение?

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

Создание новой таблицы для приложения

В обозревателе объектов выберите базу данных AdventureWorks. >>Откройте файл, чтобы открыть скрипт order_rating_ddl.sql, чтобы создать таблицу в AdventureWorks базе данных. Окно редактора запросов должно выглядеть как в следующем тексте:

DROP TABLE IF EXISTS SalesLT.OrderRating;
GO
CREATE TABLE SalesLT.OrderRating
(OrderRatingID int identity not null,
SalesOrderID int not null,
OrderRatingDT datetime not null,
OrderRating int not null,
OrderRatingComments char(500) not null);
GO

Нажмите кнопку "Выполнить" , чтобы запустить скрипт.

Загрузка запросов для отслеживания выполнения запросов

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

1. В обозревателе объектов выберите базу данных AdventureWorks. Откройте файл с>помощью файла, чтобы открыть скрипт sqlrequests.sql для просмотра активных запросов 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;
   

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

   SELECT * FROM sys.dm_os_wait_stats
   ORDER BY waiting_tasks_count DESC;
   

3. В обозревателе объектов выберите базу данных AdventureWorks. Откройте файл с>помощью файла, чтобы открыть скрипт tlog_io.sql, чтобы наблюдать за задержкой записи журнала транзакций.> Окно редактора запросов должно выглядеть как в следующем тексте:

   SELECT io_stall_write_ms/num_of_writes as avg_tlog_io_write_ms, * 
   FROM sys.dm_io_virtual_file_stats
   (db_id('AdventureWorks'), 2);
   

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

Откройте и измените скрипт рабочей нагрузки order_rating_insert_single.cmd .

• Замените unique_id, указанным в первом упражнении, имя сервера для -S parameter.
• Замените паролем, указанным в развертывании базы данных из первого упражнения, параметр -P parameter.
• Сохраните изменения в файле.

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

1. В командной строке PowerShell перейдите в каталог для этого действия модуля:

   cd c:<base directory>\04-Performance\tuning_applications
   

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

   .\order_rating_insert_single.cmd
   

   Этот сценарий использует программу ostress.exe для запуска 25 параллельных пользователей, выполняющих следующую инструкцию T-SQL (в сценарии order_rating_insert_single.sql):

   DECLARE @x int;
   SET @x = 0;
   WHILE (@x < 500)
   BEGIN
   SET @x = @x + 1;
   INSERT INTO SalesLT.OrderRating
   (SalesOrderID, OrderRatingDT, OrderRating, OrderRatingComments)
   VALUES (@x, getdate(), 5, 'This was a great order');
   END
   

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

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

Теперь выполните в среде SQL Server Management Studio (SSMS) запросы, загруженные ранее для наблюдения за производительностью. Выполните запросы для sqlrequests.sql, top_waits.sql и tlog_io.sql.

Используйте эти запросы, чтобы просмотреть следующие факты:

• У многих запросов wait_type — WRITELOG со значением > 0.
• Тип WRITELOG ожидания является одним из самых высоких показателей для типов ожидания.
• Среднее время записи в журнал транзакций ( avg_tlog_io_write_ms столбец в результирующем наборе tlog_io.sql ) составляет около 2 мс.

Длительность этой рабочей нагрузки в экземпляре SQL Server 2022 с ssd-накопителем составляет примерно 10–12 секунд. Общая продолжительность в базе данных SQL Azure с использованием 8 виртуальных ядер 5-го поколения составляет около 25 секунд.

WRITELOG Типы ожидания с более высоким временем ожидания свидетельствуют о задержке очистки в журнале транзакций. Время ожидания 2 мс на запись — вроде бы не так уж много, но на локальном накопителе SSD оно может составлять менее 1 мс.

Принятие решения

Проблема состоит не в большом проценте операций записи в журнал. Портал Azure и sys.dm_db_resource_stats не отображают каких-либо чисел выше 20–25 процентов (вам не нужно запрашивать их). Эта проблема также заключается не в ограничении операций ввода-вывода в секунду. Проблема заключается в том, что эта рабочая нагрузка приложения чувствительна к низкой задержке при записи в журнал транзакций, а уровень "Общего назначения" не предназначен для этого типа требований к задержке. Ожидаемая задержка ввода-вывода для База данных SQL Azure составляет 5–7 мс.

Примечание.

Для документов общего назначения базы данных SQL Azure приблизительные средние значения задержки ввода-вывода составляют 5–7 мс (операции записи) и 5–10 мс (операции чтения). Но могут возникать более длительные задержки. Задержки для Управляемого экземпляра SQL Azure на уровне "Общего назначения" имеют схожие значения. Если приложение очень чувствительно к задержкам ввода-вывода, можно рассмотреть переход на уровни "Критически важный для бизнеса".

Изучите скрипт T-SQL order_rating_insert_single.sql рабочей нагрузки. Каждая INSERT из них является одной фиксацией транзакции, для которой требуется очистка журнала транзакций.

Одна фиксация для каждой операции вставки неэффективна, но приложение на локальном SSD тут не пострадало, так как каждая фиксация выполнялась очень быстро. Ценовая категория (цель обслуживания или номер SKU) "Критически важный для бизнеса" предоставляет локальные диски SSD с более низкой задержкой. Возможно, что существует оптимизация приложения, поэтому рабочая нагрузка не учитывает задержку ввода-вывода для журнала транзакций.

Пакет T-SQL можно изменить для рабочей нагрузки, чтобы обтекать BEGIN TRAN/COMMIT TRAN INSERT итерации.

Выполнение измененной более эффективной рабочей нагрузки

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

1. Подготовьте скрипт рабочей нагрузки, изменив order_rating_insert.cmd , чтобы использовать правильное имя сервера и пароль.

2. Запустите измененную рабочую нагрузку с помощью скрипта order_rating_insert.cmd , аналогичного выполнению предыдущего скрипта рабочей нагрузки.

Просмотр новых результатов

1. Просмотрите результаты выполнения сценария T-SQL для sqlrequests.sql в SSMS. Обратите внимание, что ожиданий WRITELOG гораздо меньше и в целом они короче.

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

   Примечание.

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

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

   Понятие "пакетной обработки" может помочь большинству приложений, включая те, которые подключены к SQL Azure.

Совет

Управление ресурсами в Azure может повлиять на очень большие транзакции, и симптомы будут.LOG_RATE_GOVERNOR В этом примере столбец char(500), не равный NULL, дополняет пробелы и приводит к большим записям в журнале транзакций. Производительность можно оптимизировать еще больше, если сделать его столбцом переменной длины.

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