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

Для тех, кто не знаком с данной технологией, поясню, что экземпляр EC2 — это выделенная виртуальная машина, размещенная в «облаке» AWS компании Amazon. Существуют экземпляры EC2 с различными наборами вычислительных ресурсов, и плата взимается в зависимости от предоставляемых ресурсов процессора и оперативной памяти. Большинство поставщиков «облачных» служб используют схожие структуры затрат, где вычислительные ресурсы и память — факторы, определяющие плату за ресурсы в их среде.

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

Существует целый ряд решений для диагностики запросов, приводящих к высокой загрузке процессора, и хранимых процедур в Microsoft SQL Server (https://www. microsoft. com/en-us/sql-server/sql-server-2017), а также способов пошагового улучшения процесса настройки. К ним относятся:

  • динамические административные представления;
  • статистика по времени;
  • статистика клиента SQL Server Management Studio.

Рассмотрим каждый из вариантов, чтобы вы могли выбрать наиболее подходящий для своей задачи настройки. Сделаем это на основе тестового случая, подготовленного в демонстрационной среде. Я составил очень простую хранимую процедуру в экземпляре тестовой базы данных SQL Server с именем Wide World Importers (листинг 1).

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

Вариант первый: динамические административные представления

Существует два динамических административных представления (DMV), полезных при оценке показателей процессора и продолжительности запросов SQL Server: sys. dm_exec_query_stats и sys. dm_exec_procedure_stats. Первое предоставляет сведения о нерегламентированных запросах, второе — о выполненных хранимых процедурах. В остальном эти представления очень похожи по структуре и результатам. Каждое из них возвращает информацию о выполнении с минимальным/максимальным/средним и последним значениями всех показателей, в том числе ввода-вывода (чтение и запись), процессора и продолжительности. Поскольку в качестве примера используется хранимая процедура, запрашиваются результаты из dm_exec_procedue_stats. Показанный в листинге 2 запрос предоставляет информацию о последнем исполнении хранимой процедуры.

Значения last_worker_time и last_elapsed_time были получены в микросекундах, но я преобразовал их в миллисекунды для удобства сравнения с двумя другими вариантами, рассматриваемыми в статье. Миллисекунды — более широко распространенная единица измерения для времени выполнения и загрузки центрального процессора. Результаты выглядят таким образом, как показано на экране 1.

Результаты исполнения первого запроса
Экран 1. Результаты исполнения первого запроса

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

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

Это единственный из трех вариантов, который будет хорошим диагностическим средством для начального отбора процедур для настройки: можно строить запросы к sys. dm_exec_procedure_stats и выполнять их в рабочей среде, не запуская самих процедур (в рабочей или тестовой среде). Вы сможете увидеть кэшированное поведение, так как метаданные, поступающие в эти DMV, сохраняются в кэше.

Вариант второй: статистика по времени

Отображение статистики по времени включается и выключается в SQL Server Management Studio (SSMS) на уровне сеансов с помощью следующей команды:

SET STATISTICS IO (ON|OFF)
SET STATISTICS TIME ON
EXEC dbo. pOption1;

Информация выводится на вкладке Messages («Сообщения»), формируемой, когда запрос выполняется в SSMS, как показано на экране 2.

Статистика сеанса
Экран 2. Статистика сеанса

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

Вариант третий: просмотр статистики клиента

Это самый неясный вариант из трех, что удивительно, поскольку кнопка для включения соответствующей функции находится под рукой у каждого пользователя SQL Server Management Studio (экран 3).

Включение просмотра статистики клиента
Экран 3. Включение просмотра статистики клиента 

Данная функция также доступна в панели меню в SSMS в разделе Query\Include Client Statistics («Запрос/Включить статистику клиента») или включается с помощью комбинации клавиш Shift+Alt+S.

Любой из трех вариантов предоставляет дополнительную вкладку при каждом выполнении запроса в окне запроса в SSMS. Каждое окно запроса — отдельный сеанс, и статистика клиента своя для каждого выполнения любого программного кода в конкретном окне запроса. Как выглядит статистика для окна запроса, соответствующего запросу, выполненному в каждом из приведенных выше сценариев, показано на экране 4.

Просмотр статистики клиента
Экран 4. Просмотр статистики клиента 

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

Может возникнуть вопрос, почему производительность на испытании Trial 2 настолько отличается, и это проблема, о которой упоминалось ранее: данная статистика относится к любому программному коду, выполняемому в текущем окне запроса. Испытания Trial 1 и Trial 3 связаны с вызовом EXEC для pOption1, но Trial 2 был результатом кода, запущенного для очистки пула буфера между выполнениями. Помните об этих особенностях при итерациях настройки. При итеративной настройке очень помогают стрелки «вверх» и «вниз», указывающие на улучшения после изменения и повторного исполнения программного кода.

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

Листинг 1. Тестовая хранимая процедура
CREATE PROCEDURE pOption1 AS
BEGIN
       SELECT RecordedWhen, A.Temperature
       FROM dbo.Temps AS A
       WHERE A.Temperature < dbo.OptimalValue(ColdRoomSensorNumber)
              AND A.ColdRoomSensorNumber = 1
END
GO
Листинг 2. Информация о последнем исполнении хранимой процедуры 
SELECT OBJECT_NAME(ePS.object_id,ePS.database_id)
       , ePS.execution_count, ePS.last_worker_time / 1000.0 AS last_worker_time_ms
       , ePS.last_elapsed_time / 1000.0 AS last_elapsed_time_ms
FROM sys.dm_exec_procedure_stats AS ePS
WHERE OBJECT_NAME(ePS.object_id, ePS.database_id) = ‘pOption1’;