Tips for DBA: Signal Waits vs. Resource Waits

Прочитал сегодня интересный рецепт, как быстро определить, кто даёт слабину, процессор или дисковая подсистема. Ну и заодно в очередной раз убедился в полезности книжки: Проектированиеи оптимизация доступа к базам данных Microsoft SQL Server 2005. Учебный курсMicrosoft (+ CD-ROM)

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

Одной из самых сложных задач, с которыми сталкиваются разработчики баз данных, является прогнозирование поведения приложения в реальных условиях. Чтобы иметь точное представление о характеристиках производительности приложения, разработчик должен знать конкретную причину любого состояния ожидания, которое возникает во время выполнения приложения. На самом общем уровне состояния ожидания можно разбить на две категории: ожидание сигнала и ожидание ресурса. В SQL Server ожидание сигнала происходит, когда планировщик ждет процессор, чтобы запланировать задачу, а ожидание ресурса возникает, когда задача получила время процессора, но находится в ожидании ресурсов диска или памяти. При исследовании состояний ожидания в приложении нужно иметь в виду следующее. Если отношение времени ожидания сигнала ко времени ожидания ресурса велико (т.е. если состояний ожидания сигнала значительно больше, чем состояний ожидания ресурса), это может служить показателем неэффективного использования процессора. Если же велик коэффициент отношения времени ожидания ресурса ко времени ожидания сигнала, это может указывать на неэффективное использование ресурсов диска. Приступая к оценке производительности приложения, разработчики должны понимать, какой из типов ожидания преобладает. К счастью, SQLOS предоставляет "окно" в систему, достаточно прозрачное для того, чтобы разработчики могли использовать его для ответа на этот вопрос.

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

USE master
GO
--DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
--GO
WITH ByWaitTypes([Тип ожидания], [ожидания сигнала %], [ожидания ресурса %], [ожидания ms]) AS
(
SELECT TOP 20 wait_type
   , cast(100.0 * sum(signal_wait_time_ms)/sum(wait_time_ms) AS NUMERIC (20,2))
   , cast(100.0 * sum(wait_time_ms - signal_wait_time_ms)/sum(wait_time_ms) AS NUMERIC(20,2))
   , sum(wait_time_ms)
FROM sys.dm_os_wait_stats
WHERE wait_time_ms <> 0
GROUP BY wait_type
ORDER BY sum(wait_time_ms) DESC
)
SELECT TOP 1 'Тип ожидания' = N'BCE!'
   , 'ожидания сигнала %' = (SELECT cast(100.0 * sum(signal_wait_time_ms)/
    sum (wait_time_ms) AS NUMERIC (20,2)) FROM sys.dm_os_wait_stats)
   , 'ожидания ресурса %' =(SELECT cast(100.0 * sum(wait_time_ms - signal_wait_time_ms)/
    sum(wait_time_ms) AS NUMERIC(20,2)) FROM sys.dm_os_wait_stats)
   , 'ожидания ms' =(SELECT sum(wait_time_ms) FROM sys.dm_os_wait_stats)
FROM sys.dm_os_wait_stats
UNION
SELECT [Тип ожидания], [ожидания сигнала %], [ожидания ресурса %], [ожидания ms]
FROM ByWaitTypes
ORDER BY 'ожидания ms' DESC

Один из моих подопечных серверов вернул следующие результаты:

Тип ожидания	ожидания сигнала %	ожидания ресурса %	ожидания ms
BCE!	10.18	89.82	109070003
LCK_M_U	0.19	99.81	63195933
BROKER_TASK_STOP	0.14	99.86	4831680
LAZYWRITER_SLEEP	0.02	99.98	4768764
REQUEST_FOR_DEADLOCK_SEARCH	100.00	0.00	4764971
XE_TIMER_EVENT	100.00	0.00	4741221
LOGMGR_QUEUE	4.04	95.96	4729823
FT_IFTS_SCHEDULER_IDLE_WAIT	0.00	100.00	4680082
CHECKPOINT_QUEUE	0.00	100.00	4519827
WRITELOG	21.61	78.39	2546141
SLEEP_TASK	0.26	99.74	2398521
BROKER_TO_FLUSH	0.03	99.97	2384867
XE_DISPATCHER_WAIT	0.00	100.00	1770518
PAGEIOLATCH_EX	0.75	99.25	1307584
CXPACKET	14.20	85.80	955856
PAGELATCH_EX	84.97	15.03	486757
IO_COMPLETION	2.24	97.76	270499
SLEEP_BPOOL_FLUSH	2.22	97.78	224846
PAGEIOLATCH_SH	1.04	98.96	151759
SOS_SCHEDULER_YIELD	99.89	0.11	133034
ASYNC_NETWORK_IO	5.06	94.94	79275

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

В книге утверждается что, как правило, системы, имеющие низкий показатель ожидания сигнал/ресурс демонстрируют лучшую общую производительность. Так ли это, я надеюсь, обсудить вместе с вами, поскольку мои наблюдения говорят о том, сто слишком низкие показатели тоже говорят о проблемах (например, ввода-вывода). А как обстоит дело на ваших серверах?