Проверка узких мест ввода-вывода за 45 секунд

Автор: Luca Biondi, Check IO Bottlenecks in 45 Seconds. The "45 Seconds DBA Series" – What Real DBAs Check First | Part 5

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

TL;DR

• ✔️ Задержка (Latency) vs Пропускная способность (Throughput): Задержка — это скорость доступа (мс), пропускная способность — это объём передачи (МБ/с). Знайте разницу! 🧠
• ✔️ sys.dm_io_virtual_file_stats: Ключевое DMV для проверки задержки чтения и записи файлов базы данных 🛠️
• ✔️ sys.dm_os_wait_stats: Показывает глобальные ожидания ввода-вывода, такие как PAGEIOLATCH_SH и WRITELOG 💣
• ✔️ Правило 20 мс: Постоянная задержка > 20 мс для файлов данных означает серьёзное узкое место в системе хранения 🚨

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

🧠 Что это на самом деле: Задержка (Latency) vs. Пропускная способность (Throughput)

Прежде чем запускать какие-либо скрипты, мы должны перестать путать эти два понятия:

• 🏎️ Задержка (Latency): Как быстро завершается одна операция ввода-вывода. Измеряется в миллисекундах (мс). Если чтение одной страницы занимает 50 мс, ваш запрос останавливается.
• 🌊 Пропускная способность (Throughput): Какой объём данных может быть передан по каналу в секунду. Измеряется в МБ/с или ГБ/с.

💣 Ловушка: Ваш массив хранения может выдавать 2 ГБ/с пропускной способности (выглядит отлично на бумаге!), но если задержка чтения каждой отдельной страницы размером 8 КБ составляет 40 мс, SQL Server будет ощущаться невероятно медленным. Вот почему для выявления проблемы необходимо измерять именно задержку.

🛠️ Основные динамические административные представления (DMV)

Чтобы измерить это за 45 секунд, мы полагаемся на два критически важных динамических административных представления:

1. sys.dm_io_virtual_file_stats:

   Что это на самом деле: Это DMV отслеживает общее количество операций ввода-вывода и общее время, затраченное на ожидание этих операций, в разрезе отдельных файлов базы данных (MDF, NDF, LDF). Это золотой стандарт для поиска того, какой именно диск или файл является узким местом.

2. sys.dm_os_wait_stats:

   Что это на самом деле: Это DMV показывает, ожидания каких событий глобально испытывают потоки SQL Server. Если вы видите высокие ожидания для PAGEIOLATCH_SH (чтение данных с диска в память) или WRITELOG (запись в журнал транзакций), значит у вас системная нагрузка на ввод-вывод.

⚡ Проверка задержки ввода-вывода за 45 секунд

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

SELECT 
    DB_NAME(vfs.database_id) AS [Имя базы данных],
    mf.physical_name AS [Физический файл],
    vfs.num_of_reads,
    (vfs.io_stall_read_ms / NULLIF(vfs.num_of_reads, 0)) AS [Средняя задержка чтения (мс)],
    vfs.num_of_writes,
    (vfs.io_stall_write_ms / NULLIF(vfs.num_of_writes, 0)) AS [Средняя задержка записи (мс)]
FROM 
    sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS mf 
    ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
ORDER BY 
    [Средняя задержка чтения (мс)] DESC;

• ✔️ Цель: Для твердотельных накопителей (SSD/NVMe) желательно иметь эти значения ниже 5–10 мс.
• 💣 Красная черта: Если вы постоянно видите значения > 20 мс – ваша система хранения является серьёзным узким местом.

⚡ Проверка ожиданий ввода-вывода (sys.dm_os_wait_stats)

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

SELECT 
    wait_type, 
    wait_time_ms / 1000.0 AS [Время ожидания (секунды)],
    waiting_tasks_count AS [Количество ожиданий],
    (wait_time_ms / NULLIF(waiting_tasks_count, 0)) AS [Среднее время ожидания (мс)]
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'PAGEIOLATCH_%' 
   OR wait_type = 'WRITELOG'
   OR wait_type = 'ASYNC_IO_COMPLETION'
ORDER BY wait_time_ms DESC;

🚀 Моя настоящая стратегия

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

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