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

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

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

Published Tue, Mar 31 2009 19:47 by gladchenko
Filed under: ,

Comments

# Tips for DBA: Signal Waits vs. Resource Waits

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

Thursday, April 02, 2009 10:30 AM by Russian SQL Server Club

# re: Tips for DBA: Signal Waits vs. Resource Waits

Приветствую!

Я так понял что идеальная система должна стремиться к отношению 50% / 50% ?

Обязательно проверю на своих подопечных! :)

Хорошая заявка на инструмент экспресс оценки прозводительности сервера.

Thursday, April 02, 2009 12:34 PM by Илгиз Мамышев

# re: Tips for DBA: Signal Waits vs. Resource Waits

Нашёл вот такую информацию:

------

sqlcat.com/.../sql-server-2005-oltp.aspx

------

Признаки нехватки ресурсов ЦП

•Значение счетчика SignalWaits превышает 25 % от общего времени ожидания. Просмотреть значения времени ожидания сигнала и общего времени ожидания можно в представлении sys.dm_os_wait_stats. SignalWaits — это время, проведенное в очереди на запуск в ожидании процессора. Высокое значение этого параметра свидетельствует о нехватке ресурсов процессора.

------

т.е. если по колонке "ожидание сигнала %" мы получаем число 25% и выше - необходимо рассмотреть вопрос замены ЦП на более производительные или увеличить их количество.

Friday, April 03, 2009 3:01 AM by Илгиз Мамышев

# re: Tips for DBA: Signal Waits vs. Resource Waits

Похоже, что сооношение должно быть близко к 20/80 :)))

Friday, April 03, 2009 5:14 AM by gladchenko

# re: Tips for DBA: Signal Waits vs. Resource Waits

Я исправил пару ошибок в сценарии и поменял результаты, теперь должно правильно показывать (Спасибо Сергею Гавриленко за внимательность)

Friday, April 03, 2009 9:57 AM by gladchenko

# re: Tips for DBA: Signal Waits vs. Resource Waits

BCE!                                   0.77 99.23 33334353412

BROKER_RECEIVE_WAITFOR  0.00 100.00 5577445296

BROKER_TASK_STOP             0.01 99.99 3467775000

SQLTRACE_BUFFER_FLUSH     0.00 100.00 3466867968

LAZYWRITER_SLEEP              0.02 99.98 3466695109

BACKUPBUFFER                    0.90 99.10 3307504062

WRITELOG                           3.49 96.51 2522062093

CXPACKET                            0.47 99.53 1997941718

PAGEIOLATCH_SH                  0.07 99.93 1726839843

BACKUPIO                            1.10 98.90 1201220781

LCK_M_IX                             0.05 99.95 1052778187

ASYNC_IO_COMPLETION        0.00 100.00 963511093

LCK_M_U                              0.30 99.70 794246921

OLEDB                                  0.00 100.00 648973906

LCK_M_IS                             0.02 99.98 579903093

PAGEIOLATCH_EX                  0.19 99.81 558565531

LCK_M_S                              0.02 99.98 426800484

ASYNC_NETWORK_IO             2.60 97.40 372790531

LATCH_EX                            0.77 99.23 342325265

PAGELATCH_SH                    0.16 99.84 154191937

LCK_M_SCH_S                      0.00 100.00 128086390

Friday, April 03, 2009 10:25 AM by achehov

# re: Tips for DBA: Signal Waits vs. Resource Waits

Счетчики с одного из продакшен серверов, БД "ИТ-Предприятие" http://www.it.ua/

Тип ожидания                                                 ожидания сигнала %     ожидания ресурса %     ожидания ms          

------------------------------------------------------------ ---------------------- ---------------------- --------------------

BCE!                                                                            .04                  99.96           5147048373

LAZYWRITER_SLEEP                                                                .00                 100.00           4898999906

BROKER_TASK_STOP                                                                .00                 100.00             93756515

BROKER_RECEIVE_WAITFOR                                                          .00                 100.00             40067593

PAGEIOLATCH_SH                                                                  .07                  99.93             20876453

MSQL_DQ                                                                         .00                 100.00             15833046

CXPACKET                                                                       2.79                  97.21             13921187

BACKUPIO                                                                        .05                  99.95             11823671

ASYNC_IO_COMPLETION                                                             .00                 100.00              9554437

BACKUPBUFFER                                                                    .10                  99.90              9515000

OLEDB                                                                           .00                 100.00              8276500

BACKUPTHREAD                                                                    .03                  99.97              4446234

DTC                                                                             .00                 100.00              4265875

SLEEP_BPOOL_FLUSH                                                               .38                  99.62              3841156

SLEEP_TASK                                                                     5.71                  94.29              3395703

ASYNC_NETWORK_IO                                                               4.79                  95.21              1505109

PAGEIOLATCH_EX                                                                  .06                  99.94              1464828

WRITELOG                                                                       1.38                  98.62              1354609

SOS_SCHEDULER_YIELD                                                           99.03                    .97              1099296

SQLTRACE_BUFFER_FLUSH                                                           .00                 100.00               608000

LCK_M_IS                                                                        .00                 100.00               566703

Tuesday, April 07, 2009 3:58 AM by MaksBragar

# re: Tips for DBA: Signal Waits vs. Resource Waits

Счетчики с продакшен базы:

Тип ожидания ожидания сигнала % ожидания ресурса % ожидания ms

BCE! .13 99.87 19413365701

LAZYWRITER_SLEEP .01 99.99 6227846031

SQLTRACE_BUFFER_FLUSH .00 100.00 6227180125

OLEDB .00 100.00 6169222328

SLEEP_TASK .65 99.35 538197218

CXPACKET 7.09 92.91 165990468

ASYNC_NETWORK_IO .34 99.66 23205843

SOS_SCHEDULER_YIELD 94.12 5.88 8819953

LCK_M_IS .06 99.94 8420765

MSSEARCH .00 100.00 7732406

ASYNC_IO_COMPLETION .00 100.00 6851875

BACKUPBUFFER .19 99.81 6766156

WRITELOG 7.90 92.10 3994625

LATCH_EX 14.17 85.83 2917812

MSQL_DQ .00 100.00 2289390

BACKUPIO .16 99.84 1883843

SLEEP_BPOOL_FLUSH 2.59 97.41 1578640

PAGEIOLATCH_SH .82 99.18 1454656

BACKUPTHREAD .00 100.00 1445312

WAITFOR .00 100.00 1211234

SQLTRACE_LOCK .06 99.94 1183125

Thursday, June 18, 2009 11:34 AM by 1klassNick

Leave a Comment

(required) 
(required) 
(optional)
(required) 
If you can't read this number refresh your screen
Enter the numbers above: