Измеряем задержку ввода-вывода SQL Server по интервалам

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

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

Сначала разность, затем деление

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

SELECT database_id, file_id, sample_ms, num_of_reads, num_of_writes,
       io_stall_read_ms, io_stall_write_ms
INTO #IoBefore
FROM sys.dm_io_virtual_file_stats(NULL, NULL);

WAITFOR DELAY '00:00:05';

SELECT DB_NAME(a.database_id) AS DatabaseName,
       mf.name AS LogicalFileName, mf.type_desc,
       a.sample_ms - b.sample_ms AS SampleMilliseconds,
       a.num_of_reads - b.num_of_reads AS Reads,
       a.num_of_writes - b.num_of_writes AS Writes,
       CAST(1.0 * (a.io_stall_read_ms - b.io_stall_read_ms)
            / NULLIF(a.num_of_reads - b.num_of_reads, 0)
            AS decimal(12,2)) AS AverageReadMs,
       CAST(1.0 * (a.io_stall_write_ms - b.io_stall_write_ms)
            / NULLIF(a.num_of_writes - b.num_of_writes, 0)
            AS decimal(12,2)) AS AverageWriteMs
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS a
JOIN #IoBefore AS b
  ON b.database_id = a.database_id AND b.file_id = a.file_id
JOIN sys.master_files AS mf
  ON mf.database_id = a.database_id AND mf.file_id = a.file_id
WHERE a.sample_ms > b.sample_ms
  AND a.num_of_reads >= b.num_of_reads
  AND a.num_of_writes >= b.num_of_writes
  AND a.io_stall_read_ms >= b.io_stall_read_ms
  AND a.io_stall_write_ms >= b.io_stall_write_ms
ORDER BY DatabaseName, mf.type_desc, LogicalFileName;
DROP TABLE #IoBefore;

AverageReadMs делит прирост ожидания чтений в миллисекундах на прирост завершённых чтений. AverageWriteMs выполняет то же для записей. Множитель 1.0 исключает целочисленное деление. NULL означает отсутствие соответствующих операций, а не нулевую задержку диска.

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

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

Среднее скрывает распределение. Много быстрых операций и несколько очень медленных могут дать тот же результат, что равномерно посредственные задержки. Эти счётчики не предоставляют точного перцентиля и не называют запрос, ответственный за каждое ожидание.

Связываем цифры с симптомами

Медленное чтение данных может сопровождаться PAGEIOLATCH, но избыток физических чтений бывает следствием запроса или недостаточной резидентности в кэше. Ненужное сканирование большого объёма нагружает даже исправное хранилище. Сначала проверьте план доступа и объём работы.

Медленная запись журнала способна увеличивать COMMIT и сопровождаться WRITELOG. Много маленьких транзакций чувствительны к задержке отдельных записей даже при небольшой пропускной нагрузке. Запись файлов данных иначе связана с checkpoint и фоновым сбросом. Не объединяйте всё в одну оценку диска.

Добавьте частоту операций, переданные байты и длительность прикладных действий. Исчерпанный лимит пропускной способности отличается от редких медленных записей. Сопоставляйте метрики операционной системы и платформы за тот же интервал, а не с произвольными суточными средними.

Отличайте PAGEIOLATCH от PAGELATCH. Второе ожидание относится к защёлке в памяти, а не к вводу-выводу. Похожие имена не означают одну причину. Покупка дисков не исправляет конкуренцию за горячую страницу распределения в памяти.

Выбираем действие по наблюдениям

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

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

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

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

Техническая документация: Microsoft Learn: File I/O statistics · Microsoft Learn: Troubleshoot slow I/O.