Производительность сброса данных журнала на диск

Одной из распространённых задач систем с высокой транзакционной загрузкой является определение того, достаточно ли производительна подсистема ввода-вывода, обслуживающая журнал транзакций. Часто «узким местом» становиться дисковая подсистема, используемая в качестве долговременного носителя для файла журнала транзакций обслуживаемой 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. Если значения превышают 5ms, то можно более детально посмотреть задержки для конкретных файлов логов:

	SELECT DB_NAME(dm_io_virtual_file_stats.database_id) AS [Database Name], dm_io_virtual_file_stats.file_id,f.name,f.physical_name, io_stall_read_ms, num_of_reads,
	CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1)) AS [avg_read_stall_ms],io_stall_write_ms, 
	num_of_writes,CAST(io_stall_write_ms/(1.0+num_of_writes) AS NUMERIC(10,1)) AS [avg_write_stall_ms],
	io_stall_read_ms + io_stall_write_ms AS [io_stalls], num_of_reads + num_of_writes AS [total_io],
	CAST((io_stall_read_ms + io_stall_write_ms)/(1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1)) 
	AS [avg_io_stall_ms]
	--INTO virtual_file_stats
	FROM sys.dm_io_virtual_file_stats(null,null) INNER JOIN sys.master_files as f ON dm_io_virtual_file_stats.database_id = f.database_id AND dm_io_virtual_file_stats.file_id = f.file_id
	WHERE physical_name like '%.ldf%' OR dm_io_virtual_file_stats.[file_id] = 2
	ORDER BY io_stalls DESC,avg_io_stall_ms DESC;

Здесь нам интересно поле avg_io_stall_ms, которое показывает среднее время обращений к файлу лога, а так же avg_write_stall_ms и avg_read_stall_ms, которые демонстрируют среднее время записи/чтения. Если где либо встречается значение более 15 ms, значит вам следует улучшить дисковую подсистему для конкретного файла лога.