Анализ системы ввода-вывода SQL Server. Часть 1

Microsoft SQL Server располагает системными объектами Dynamic Management Objects, которые предоставляют исчерпывающие метаданные, относящиеся к характеристикам производительности экземпляров SQL Server. В частности, использование одного из них, sys.dm_io_virtual_file_stats, позволяет специалисту в области данных оценить влияние ввода-вывода хранилища данных, структуры файлов и даже плохо спроектированных запросов и гиперактивных конечных пользователей. В предлагаемой статье мы рассмотрим, как определить самые высокие задержки чтения и записи данных и файлов журналов соответственно.

Вечные проблемы

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

Когда я спросил, на чем основаны предположения моего оппонента, он упомянул как системный монитор, так и средство Performance Analysis of Logs (http://pal.codeplex.com/), более известное как PAL и доступное на сайте CodePlex. PAL — средство, которое читает журналы системного монитора и предоставляет отчеты о производительности на основе этих журналов. Оба инструмента предоставляются компанией Microsoft, и в основе их предупреждений лежат иные представления о порогах производительности ввода-вывода, нежели готова признать Microsoft, или мягкие стандарты «реального мира». PAL выдает предупреждение об операциях чтения длительностью более 20 миллисекунд (мс) и записи более 5 мс. Всеми признано, что приемлемо время менее 100 мс для чтения и 20 мс для записи. Однако кому не захочется обвинить «медленный ввод-вывод», увидев, как PAL выдает показатели ввода-вывода, едва превышающие пороговые значения (см. экран 1).

Экран 1. Показатели ввода-вывода утилиты PAL

Этот пример для меня не нов. В течение длительного времени данная проблема докучала нашей группе администраторов баз данных. Мы уже выполнили миграцию с хранилища VNX «уровня 2» на платформу хранения данных «уровня 1» (HP 9500 SAN.) Мне также было известно, что приложение CA Service Desk имеет существенные изъяны, и в дополнение к ним два человека в нашей компании направляли в базу данных чрезвычайно неэффективные запросы и никак не реагировали на рекомендации по настройке производительности от администраторов баз данных. Среда созрела для повышения производительности как внутренних, так и подготовленных внешним поставщиком запросов, но аналитики хотели только одного: чтобы инженеры, отвечающие за инфраструктуру (сервер, хранилище данных, администраторы баз данных), добавляли оборудование, вместо того чтобы внедрять оптимальные стандарты проектирования и кодирования.

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

Далее в статье мы рассмотрим способы определения нагрузки ввода-вывода на нескольких экземплярах в средах SQL Server и сопоставления результатов с задержкой ввода-вывода, что позволит сделать заключение о влиянии соотношения задержки и нагрузки на производительность ввода-вывода. Это поможет заняться плохо настроенными запросами, вместо того чтобы терять драгоценное время в погоне за призраками на уровне хранилища. Однако мы будем не спеша приближаться к цели и сначала посмотрим, как измерить нагрузку и задержку ввода-вывода на уровне экземпляра и как использовать некоторые широко распространенные инструменты анализа и подготовки отчетов для представления данных администраторам баз данных и аналитикам приложений. Надеюсь, с помощью этой статьи вы освоите применение различных динамических объектов управления, а также рассматриваемых здесь средств Excel, Tableau и Microsoft Power Tools и статистических функций mean, median и mode.

Базовая функция динамического управления

В центре всей серии цикла материалов, который мы начинаем этой статьей, находится функция динамического управления. Эта функция принимает два параметра:

• database_id;
• file_id

Поскольку мы обычно не запоминаем наше значение database_ids, оно часто преобразуется из имени с использованием системной функции db_id (). Синтаксис довольно очевиден, как и результаты. Например, приведенный ниже программный код возвращает информацию обо всех файлах, связанных с базой данных master:

SELECT *
FROM sys.dm_io_virtual_file_stats
   (DB_ID ('master'), NULL);

Возвращенные столбцы содержат сведения, относящиеся к файлу и базе данных (database_id и file_id), количество времени в образце в миллисекундах (sample_ms), а затем показатели ввода-вывода для операций обоих типов, количество прочитанных байтов и замедление ввода-вывода в миллисекундах как для чтения, так и для записи (см. экран 2). Динамическое административное представление (DMV) также предоставляет общее замедление ввода-вывода для операций чтения и записи, текущий размер файла и file_handle, уникальный идентификатор для файла в экземпляре SQL. Обратите внимание, что sample_ms в этих выходных данных является отрицательным числом. Это значение нельзя использовать как показатель непрерывной работы, так как оно возвращается к исходному после достижения предела для этого типа данных.

Экран 2. Сведения о показателях ввода-вывода

В процессе подготовки этого материала я заинтересовался идентификаторами файлов database_id и file_id, а также столбцами нагрузки ввода-вывода, в частности num_of_reads, io_stall_read_ms, num_of_writes и io_stall_write_ms. С помощью этих столбцов можно определить среднюю задержку ввода-вывода для операций чтения и записи и назначить ее любому файлу в любой базе данных.

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

Сохранение метаданных DMO на диске

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

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

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

Где были созданы эти таблицы? Вы можете сделать это локально на каждом SQL Server, но я покажу, как собрать информацию для всех баз данных вашего домена для сравнения в масштабах среды. Если вы хотите отыскать шумные серверы и базы данных — скрипучие колеса, сделать это можно следующим образом. У меня есть центральная база данных, именуемая «спасательной лодкой» (lifeboat), и это действительно спасательная лодка в случаях, когда требуется быстро найти ответы на разнообразные вопросы.

Теперь, имея целевые таблицы, настало время представить сценарий сбора, который будет получать данные из sys.dm_io_virtual_file_stats (и некоторых других системных объектов) для заполнения этой базовой таблицы ежедневного сбора данных (листинг 2).

Заметьте, что я расширил некоторые описательные столбцы для базы данных и файла присоединением к системному представлению sys.master_files. Я также рассчитал количество дней, в течение которых ведется сбор данных из DMF с помощью create_date для tempdb. В конечном итоге мы объединим данные из этого экземпляра SQL со всеми остальными данными в нашей среде, которые будут иметь разное время работы, поэтому единственный способ корректного сравнения — на дневной основе.

В завершение теории: простой запрос

Мы углубимся в аналитику чуть позже. А пока попытаемся ответить на три вопроса:

1. Какие пять файлов данных имеют самую высокую среднюю задержку чтения при вводе-выводе?
2. Какие пять файлов журналов имеют самую высокую среднюю задержку записи при вводе-выводе?
3. К какому типу по уровню задержки ввода-вывода относится база данных, которая стала первопричиной всей этой работы?

Пять файлов данных с самой высокой средней задержкой чтения при вводе-выводе определяются с помощью кода листинга 3.

Неудивительно, что три файла с худшими показателями задержки принадлежат к нашей реализации Sharepoint (экран 3). Однако 5 секунд для каждого — это неожиданно и требует разъяснения. Вы заметите, что и здесь я включил ежедневную нагрузку чтения. Высокая задержка для файла с низкой активностью не повод для беспокойства. Однако именно благодаря активности эта проблема оказалась в поле моего зрения.

Экран 3. Три файла с худшими показателями задержки

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

В этом случае становится ясно, почему я включил ежедневную активность записи. Файлы с худшими показателями в данном случае имеют очень низкую активность записи. Я скрыл сервер и имя базы данных, но неизлечимо больной — наш устаревший сервер SharePoint (опять SharePoint!), как показано на экране 4.

Экран 4. Файлы с худшими показателями записи

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

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

Показатели файлов данных и ввода-вывода при чтении этих файлов находятся в пределах приемлемой задержки ввода-вывода (экран 5). Интересно, что основной файл данных находится в верхней десятке самых активных по ежедневной нагрузке чтения при вводе-выводе и при этом имеет превосходную среднюю задержку чтения 21 мс. Тем не менее мой оппонент, аналитик, придерживался иного мнения и не воспринимал моих утверждений о том, что корень проблемы — в плохих запросах, приводящих к большому числу лишних операций чтения. Аналогичная картина с операциями записи (листинг 6).

Экран 5. Приемлемые задержки при чтении файлов

И вновь ежедневная нагрузка ввода-вывода довольно высока (седьмой по величине дневной показатель записи при вводе-выводе), и все же задержка составляет в среднем 1 мс для операции записи — в пределах допусков, установленных компанией Microsoft (экран 6).

Экран 6. Приемлемые задержки при записи файлов

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

Мы не рассматривали распределение данных и использование статистического анализа, чтобы отбросить отклоняющиеся значения (например, результаты 5 секунд и экземпляры с низким объемом ввода-вывода), а также способы применения функций mean, median и mode. Об этом пойдет речь в следующих статьях.

 CREATE TABLE dbo.database_file_io
(
[server] sysname NOT NULL
, database_name sysname NOT NULL
, logical_name sysname NOT NULL
, physical_name NVARCHAR(260) NOT NULL
, file_size_mb BIGINT NOT NULL
, type_desc NVARCHAR(60) NOT NULL
, num_of_reads BIGINT NOT NULL
, num_kb_read BIGINT NOT NULL
, avg_daily_reads BIGINT NOT NULL
, avg_daily_kb_read BIGINT NOT NULL
, io_stall_read_ms BIGINT NOT NULL
, avg_io_stall_read_ms BIGINT NOT NULL
, num_of_writes BIGINT NOT NULL
, num_kb_written BIGINT NOT NULL
, avg_daily_writes BIGINT NOT NULL
, avg_daily_kb_written BIGINT NOT NULL
, io_stall_write_ms BIGINT NOT NULL
, avg_io_stall_write_ms BIGINT NOT NULL
, total_io_stall BIGINT NOT NULL
, avg_io_stall_ms BIGINT NOT NULL
, pct_read DECIMAL(4,1) NOT NULL
, pct_write DECIMAL(4,1) NOT NULL
, date_stamp DATETIME NOT NULL
)

ALTER TABLE dbo.database_file_io ADD CONSTRAINT
PK_database_file_io PRIMARY KEY CLUSTERED
(
logical_name,
server,
database_name,
date_stamp
)
WITH
(
STATISTICS_NORECOMPUTE = OFF
, IGNORE_DUP_KEY = OFF
, ALLOW_ROW_LOCKS = ON
, ALLOW_PAGE_LOCKS = ON
, FILLFACTOR = 70
) ON [PRIMARY];
GO
--====================================================
CREATE TABLE dbo.database_file_io_history
(
[server] sysname NOT NULL
, database_name sysname NOT NULL
, logical_name sysname NOT NULL
, physical_name NVARCHAR(260) NOT NULL
, file_size_mb BIGINT NOT NULL
, type_desc NVARCHAR(60) NOT NULL
, num_of_reads BIGINT NOT NULL
, num_kb_read BIGINT NOT NULL
, avg_daily_reads BIGINT NOT NULL
, avg_daily_kb_read BIGINT NOT NULL
, io_stall_read_ms BIGINT NOT NULL
, avg_io_stall_read_ms BIGINT NOT NULL
, num_of_writes BIGINT NOT NULL
, num_kb_written BIGINT NOT NULL
, avg_daily_writes BIGINT NOT NULL
, avg_daily_kb_written BIGINT NOT NULL
, io_stall_write_ms BIGINT NOT NULL
, avg_io_stall_write_ms BIGINT NOT NULL
, total_io_stall BIGINT NOT NULL
, avg_io_stall_ms BIGINT NOT NULL
, pct_read DECIMAL(4,1) NOT NULL
, pct_write DECIMAL(4,1) NOT NULL
, date_stamp DATETIME NOT NULL
);
ALTER TABLE dbo.database_file_io_history ADD CONSTRAINT
PK_database_file_io_history PRIMARY KEY CLUSTERED
(
logical_name,
server,
database_name,
date_stamp
)
WITH
(
STATISTICS_NORECOMPUTE = OFF
, IGNORE_DUP_KEY = OFF
, ALLOW_ROW_LOCKS = ON
, ALLOW_PAGE_LOCKS = ON
, FILLFACTOR = 70
) ON [PRIMARY];
GO

DECLARE @days INT
SELECT @days = DATEDIFF(d, create_date, GETDATE()) FROM sys.databases WHERE name = 'tempdb'
IF @days = 0
BEGIN
SELECT @days = 1
END
INSERT INTO [dbo].[database_file_io]
(
server
, database_name
, logical_name
, physical_name
, file_size_mb
, type_desc
, num_of_reads
, num_kb_read
, avg_daily_reads
, avg_daily_kb_read
, io_stall_read_ms
, avg_io_stall_read_ms
, num_of_writes
, num_kb_written
, avg_daily_writes
, avg_daily_kb_written
, io_stall_write_ms
, avg_io_stall_write_ms
, total_io_stall
, avg_io_stall_ms
, pct_read
, pct_write
, date_stamp
)
SELECT @@SERVERNAME AS [server]
, DB_NAME(FS.database_id) AS database_name
, DB.[name] AS logical_name
, DB.physical_name
, FS.size_on_disk_bytes/1024/1024 AS file_size_mb
, DB.type_desc
, FS.num_of_reads
, FS.num_of_bytes_read / 8192 AS num_kb_read
, FS.num_of_reads / @days AS avg_daily_reads
, FS.num_of_bytes_read / 8192 / @days AS avg_daily_kb_read
, FS.io_stall_read_ms
, CASE
WHEN FS.num_of_reads = 0 THEN FS.io_stall_read_ms
ELSE FS.io_stall_read_ms / FS.num_of_reads
END AS avg_io_stall_read_ms
, FS.num_of_writes
, FS.num_of_bytes_written / 8192 AS num_of_kb_written
, FS.num_of_writes / @days AS avg_daily_writes
, FS.num_of_bytes_written / 8192 / @days AS avg_daily_kb_written
, FS.io_stall_write_ms
, CASE
WHEN FS.num_of_writes = 0 THEN FS.io_stall_write_ms
ELSE FS.io_stall_write_ms / FS.num_of_writes
END AS avg_io_stall_write_ms
, FS.io_stall AS total_io_stall
, CASE
WHEN (FS.num_of_reads + FS.num_of_writes) = 0 THEN FS.io_stall
ELSE FS.io_stall / (FS.num_of_reads + FS.num_of_writes)
END AS avg_io_stall_ms
, CAST(100. * FS.num_of_reads / (FS.num_of_reads + FS.num_of_writes) AS decimal(4,1)) AS pct_read
, 100.0 - (CAST(100. * FS.num_of_reads / (FS.num_of_reads + FS.num_of_writes) AS decimal(4,1))) AS pct_write
, GETDATE() AS date_stamp
FROM sys.dm_io_virtual_file_stats(NULL, NULL) FS
INNER JOIN master.sys.master_files DB
ON FS.database_id = DB.database_id
AND FS.file_id = DB.file_id
ORDER BY 3
, 1
, 2;

SELECT TOP 5 FIO.server, FIO.database_name, FIO.physical_name, FIO.avg_daily_reads, avg_io_stall_read_ms
FROM dbo.database_file_io FIO
WHERE FIO.type_desc = 'rows' AND FIO.database_name  NOT IN ('tempdb', 'model', 'msdb', 'master', 'iDBA')
ORDER BY avg_io_stall_read_ms DESC;

SELECT TOP 5 FIO.server, FIO.database_name, FIO.physical_name, FIO.avg_daily_writes, avg_io_stall_write_ms
  FROM dbo.database_file_io FIO
WHERE FIO.type_desc = 'log' AND FIO.database_name NOT IN ('tempdb', 'model', 'msdb', 'master', 'iDBA')
ORDER BY avg_io_stall_write_ms DESC;

WITH Results AS (SELECT ROW_NUMBER()
OVER (ORDER BY (FIO.avg_io_stall_read_ms) DESC) AS latency_rank, ROW_NUMBER()
OVER (ORDER BY (FIO.avg_daily_reads) DESC) AS io_load_rank, FIO.server, FIO.database_name, FIO.physical_name, FIO.avg_daily_reads, FIO.avg_io_stall_read_ms
FROM dbo.database_file_io FIO
WHERE FIO.type_desc = 'rows' AND FIO.database_name NOT IN ('tempdb', 'model', 'msdb', 'master', 'iDBA'))
SELECT Results.latency_rank, Results.io_load_rank, Results.physical_name, Results.avg_io_stall_read_ms, Results.avg_daily_reads
FROM Results
WHERE Results.server = '' AND Results.database_name = ''

WITH Results AS (SELECT ROW_NUMBER()
OVER (ORDER BY (FIO.avg_io_stall_write_ms) DESC) AS latency_rank, ROW_NUMBER()
OVER (ORDER BY (FIO.avg_daily_writes) DESC) AS io_load_rank, FIO.server, FIO.database_name, FIO.physical_name, FIO.avg_daily_writes, FIO.avg_io_stall_write_ms
  FROM dbo.database_file_io FIO
  WHERE FIO.type_desc = 'log' AND FIO.database_name NOT IN ('tempdb', 'model', 'msdb', 'master', 'iDBA') )
SELECT Results.latency_rank, Results.io_load_rank, Results.physical_name, Results.avg_io_stall_write_ms, Results.avg_daily_writes
  FROM Results WHERE Results.server = ''  AND Results.database_name = ''