Прочитал сегодня интересный рецепт, как быстро определить, кто даёт слабину, процессор или дисковая подсистема. Ну и заодно в очередной раз убедился в полезности книжки: Проектирование и оптимизация доступа к базам данных 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 |
Результирующий набор будет содержать суммарное время ожидания и процентное соотношение этих значений. В верхней строке сумма по всем типам ожиданий (такой же результат даёт запрос из книжки), я ниже расположены уточняющие сведения, т.е. группировка по самой прожорливой десятке типов ожиданий.
В книге утверждается что, как правило, системы, имеющие низкий показатель ожидания сигнал/ресурс демонстрируют лучшую общую производительность. Так ли это, я надеюсь, обсудить вместе с вами, поскольку мои наблюдения говорят о том, сто слишком низкие показатели тоже говорят о проблемах (например, ввода-вывода). А как обстоит дело на ваших серверах?