Tips for DBA: Log Flush Performance

Одной из распространённых задач систем с высокой транзакционной загрузкой является определение того, достаточно ли производительна подсистема ввода-вывода, обслуживающая журнал транзакций. Часто «узким местом» становиться дисковая подсистема, используемая в качестве долговременного носителя для файла журнала транзакций обслуживаемой SQL Server базы данных. Одним из важных параметров дисковой подсистемы является время доступа к данным на диске. Современным дисковым подсистемам характерно время доступа порядка 1 – 5 ms. Проверить, какое время доступа у используемой для размещения файла журнала транзакций дисковой подсистемы можно с помощью административного динамического представления: sys.dm_os_wait_stats (Transact-SQL). Данные в этом представлении накапливаются с момента последнего запуска службы SQL Server, поэтому, рекомендуется очистить эту статистику. Сделать это можно следующей командой:

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
GO

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

SELECT            (wait_time_ms - signal_wait_time_ms) / waiting_tasks_count AS [Время отклика долговременного носителя журнала (ms)]
              ,    max_wait_time_ms AS [Максимальное время ожидания (ms)]
FROM        sys.dm_os_wait_stats
WHERE       wait_type = 'WRITELOG' AND waiting_tasks_count > 0;

Здесь:

 

• wait_time_ms - общее время ожидания данного типа в миллисекундах. Это время включает в себя время signal_wait_time_ms.
• signal_wait_time_ms - разница между временем сигнализации ожидающего потока и временем начала его выполнения.
• waiting_tasks_count - число ожиданий данного типа. Этот счетчик наращивается каждый раз при начале ожидания.
• max_wait_time_ms - максимальное время ожидания данного типа.

 

В публичном документе Майкрософт: «Diagnosing Transaction Log Performance Issues and Limits of the Log Manager» (автор: Mike Ruthruff) рекомендовано чтобы время отклика долговременного носителя журнала было в диапазоне от 1ms до 5ms.