Статья
Анализ ожиданий на СУБД
Каждый раз, когда поток СУБД, исполняющий запрос, чего-то ждет, это фиксируется в специальной служебной таблице. Оттуда можно получить очень ценную информацию и составить представление об узких местах вашей базы данных.
Данный механизм делает значительную часть работы: собирает информацию о том, чего именно ожидают запросы при выполнении – остается только верно воспользоваться этими ценными данными и правильно их интерпретировать.
В Мониторе есть возможность получить эти данные - для этого используется показатель Данные СУБД.
В Мониторе есть возможность получить эти данные - для этого используется показатель Данные СУБД.
Настройка показателя Данные СУБД
Показатель Данные СУБД может собирать различные данные, в том числе статистику ожиданий MS SQL именно он нам и нужен. Далее подробно рассмотрим что это за механизм, как он работает и какие именно данные там можно увидеть.
В MS SQL по умолчанию включен механизм, отслеживающий все возможные ожидания. Получить доступ к этим сведениям можно с помощью системного представления sys.dm_os_wait_stats.
В данном представлении находятся данные, накопленные с момента запуска сервера СУБД либо с момента последней очистки этих данных.
В PG эти сведения можно получить с помощью pg_stat_activity.
В дальнейшем все ожидания будем рассматривать на примере MS SQL, но общий принцип работы с ожиданиями схожий, Монитор может отображать ожидания как MS SQL так и PG.
Следующий запрос MS SQL покажет что именно ждали потоки MS SQL, сколько раз ждали, как долго и т.д.:
В MS SQL по умолчанию включен механизм, отслеживающий все возможные ожидания. Получить доступ к этим сведениям можно с помощью системного представления sys.dm_os_wait_stats.
В данном представлении находятся данные, накопленные с момента запуска сервера СУБД либо с момента последней очистки этих данных.
В PG эти сведения можно получить с помощью pg_stat_activity.
В дальнейшем все ожидания будем рассматривать на примере MS SQL, но общий принцип работы с ожиданиями схожий, Монитор может отображать ожидания как MS SQL так и PG.
Следующий запрос MS SQL покажет что именно ждали потоки MS SQL, сколько раз ждали, как долго и т.д.:
SELECT * FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC
Информации, полученной таким образом, окажется много, но вот толку от нее, скорее всего, будет мало, и на это есть две основных причины:
- Большинство ожиданий безобидны, они не указывают на проблемы, но визуально засоряют вывод и отвлекают внимание. Чтобы решить данную проблему, необходимо отсечь те ожидания, которые не показательны. Далее будет приведен запрос, содержащий только значимые ожидания.
- Данные в sys.dm_os_wait_stats накапливаются с момента запуска сервера (если их специально не чистят). В этом случае проблемы, которые возникли недавно, просто не будут заметны. Если сервер работает несколько месяцев, то проблемы, возникшие пару дней назад, просто «утонут» в потоке исторических данных. Решение простое – периодически очищать системное представление с помощью запроса:
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR)
Далее рассмотрим, как можно накапливать и анализировать данную статистику.
Для просмотра только значимых ожиданий можно использовать следующий запрос:
Для просмотра только значимых ожиданий можно использовать следующий запрос:
WITH Waits AS
(SELECT
wait_type,
wait_time_ms / 1000.0 AS WaitS,
(wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
signal_wait_time_ms / 1000.0 AS SignalS,
waiting_tasks_count AS WaitCount,
100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
-- These wait types are almost 100% never a problem and so they are
-- filtered out to avoid them skewing the results. Click on the URL
-- for more information.
N'BROKER_EVENTHANDLER', -- https://www.sqlskills.com/help/waits/BROKER_EVENTHANDLER
N'BROKER_RECEIVE_WAITFOR', -- https://www.sqlskills.com/help/waits/BROKER_RECEIVE_WAITFOR
N'BROKER_TASK_STOP', -- https://www.sqlskills.com/help/waits/BROKER_TASK_STOP
N'BROKER_TO_FLUSH', -- https://www.sqlskills.com/help/waits/BROKER_TO_FLUSH
N'BROKER_TRANSMITTER', -- https://www.sqlskills.com/help/waits/BROKER_TRANSMITTER
N'CHECKPOINT_QUEUE', -- https://www.sqlskills.com/help/waits/CHECKPOINT_QUEUE
N'CHKPT', -- https://www.sqlskills.com/help/waits/CHKPT
N'CLR_AUTO_EVENT', -- https://www.sqlskills.com/help/waits/CLR_AUTO_EVENT
N'CLR_MANUAL_EVENT', -- https://www.sqlskills.com/help/waits/CLR_MANUAL_EVENT
N'CLR_SEMAPHORE', -- https://www.sqlskills.com/help/waits/CLR_SEMAPHORE
-- Maybe comment this out if you have parallelism issues
N'CXCONSUMER', -- https://www.sqlskills.com/help/waits/CXCONSUMER
-- Maybe comment these four out if you have mirroring issues
N'DBMIRROR_DBM_EVENT', -- https://www.sqlskills.com/help/waits/DBMIRROR_DBM_EVENT
N'DBMIRROR_EVENTS_QUEUE', -- https://www.sqlskills.com/help/waits/DBMIRROR_EVENTS_QUEUE
N'DBMIRROR_WORKER_QUEUE', -- https://www.sqlskills.com/help/waits/DBMIRROR_WORKER_QUEUE
N'DBMIRRORING_CMD', -- https://www.sqlskills.com/help/waits/DBMIRRORING_CMD
N'DIRTY_PAGE_POLL', -- https://www.sqlskills.com/help/waits/DIRTY_PAGE_POLL
N'DISPATCHER_QUEUE_SEMAPHORE', -- https://www.sqlskills.com/help/waits/DISPATCHER_QUEUE_SEMAPHORE
N'EXECSYNC', -- https://www.sqlskills.com/help/waits/EXECSYNC
N'FSAGENT', -- https://www.sqlskills.com/help/waits/FSAGENT
N'FT_IFTS_SCHEDULER_IDLE_WAIT', -- https://www.sqlskills.com/help/waits/FT_IFTS_SCHEDULER_IDLE_WAIT
N'FT_IFTSHC_MUTEX', -- https://www.sqlskills.com/help/waits/FT_IFTSHC_MUTEX
-- Maybe comment these six out if you have AG issues
N'HADR_CLUSAPI_CALL', -- https://www.sqlskills.com/help/waits/HADR_CLUSAPI_CALL
N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', -- https://www.sqlskills.com/help/waits/HADR_FILESTREAM_IOMGR_IOCOMPLETION
N'HADR_LOGCAPTURE_WAIT', -- https://www.sqlskills.com/help/waits/HADR_LOGCAPTURE_WAIT
N'HADR_NOTIFICATION_DEQUEUE', -- https://www.sqlskills.com/help/waits/HADR_NOTIFICATION_DEQUEUE
N'HADR_TIMER_TASK', -- https://www.sqlskills.com/help/waits/HADR_TIMER_TASK
N'HADR_WORK_QUEUE', -- https://www.sqlskills.com/help/waits/HADR_WORK_QUEUE
N'KSOURCE_WAKEUP', -- https://www.sqlskills.com/help/waits/KSOURCE_WAKEUP
N'LAZYWRITER_SLEEP', -- https://www.sqlskills.com/help/waits/LAZYWRITER_SLEEP
N'LOGMGR_QUEUE', -- https://www.sqlskills.com/help/waits/LOGMGR_QUEUE
N'MEMORY_ALLOCATION_EXT', -- https://www.sqlskills.com/help/waits/MEMORY_ALLOCATION_EXT
N'ONDEMAND_TASK_QUEUE', -- https://www.sqlskills.com/help/waits/ONDEMAND_TASK_QUEUE
N'PARALLEL_REDO_DRAIN_WORKER', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_DRAIN_WORKER
N'PARALLEL_REDO_LOG_CACHE', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_LOG_CACHE
N'PARALLEL_REDO_TRAN_LIST', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_TRAN_LIST
N'PARALLEL_REDO_WORKER_SYNC', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_WORKER_SYNC
N'PARALLEL_REDO_WORKER_WAIT_WORK', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_WORKER_WAIT_WORK
N'PREEMPTIVE_OS_FLUSHFILEBUFFERS', -- https://www.sqlskills.com/help/waits/PREEMPTIVE_OS_FLUSHFILEBUFFERS
N'PREEMPTIVE_XE_GETTARGETSTATE', -- https://www.sqlskills.com/help/waits/PREEMPTIVE_XE_GETTARGETSTATE
N'PVS_PREALLOCATE', -- https://www.sqlskills.com/help/waits/PVS_PREALLOCATE
N'PWAIT_ALL_COMPONENTS_INITIALIZED', -- https://www.sqlskills.com/help/waits/PWAIT_ALL_COMPONENTS_INITIALIZED
N'PWAIT_DIRECTLOGCONSUMER_GETNEXT', -- https://www.sqlskills.com/help/waits/PWAIT_DIRECTLOGCONSUMER_GETNEXT
N'PWAIT_EXTENSIBILITY_CLEANUP_TASK', -- https://www.sqlskills.com/help/waits/PWAIT_EXTENSIBILITY_CLEANUP_TASK
N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', -- https://www.sqlskills.com/help/waits/QDS_PERSIST_TASK_MAIN_LOOP_SLEEP
N'QDS_ASYNC_QUEUE', -- https://www.sqlskills.com/help/waits/QDS_ASYNC_QUEUE
N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', -- https://www.sqlskills.com/help/waits/QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP
N'QDS_SHUTDOWN_QUEUE', -- https://www.sqlskills.com/help/waits/QDS_SHUTDOWN_QUEUE
N'REDO_THREAD_PENDING_WORK', -- https://www.sqlskills.com/help/waits/REDO_THREAD_PENDING_WORK
N'REQUEST_FOR_DEADLOCK_SEARCH', -- https://www.sqlskills.com/help/waits/REQUEST_FOR_DEADLOCK_SEARCH
N'RESOURCE_QUEUE', -- https://www.sqlskills.com/help/waits/RESOURCE_QUEUE
N'SERVER_IDLE_CHECK', -- https://www.sqlskills.com/help/waits/SERVER_IDLE_CHECK
N'SLEEP_BPOOL_FLUSH', -- https://www.sqlskills.com/help/waits/SLEEP_BPOOL_FLUSH
N'SLEEP_DBSTARTUP', -- https://www.sqlskills.com/help/waits/SLEEP_DBSTARTUP
N'SLEEP_DCOMSTARTUP', -- https://www.sqlskills.com/help/waits/SLEEP_DCOMSTARTUP
N'SLEEP_MASTERDBREADY', -- https://www.sqlskills.com/help/waits/SLEEP_MASTERDBREADY
N'SLEEP_MASTERMDREADY', -- https://www.sqlskills.com/help/waits/SLEEP_MASTERMDREADY
N'SLEEP_MASTERUPGRADED', -- https://www.sqlskills.com/help/waits/SLEEP_MASTERUPGRADED
N'SLEEP_MSDBSTARTUP', -- https://www.sqlskills.com/help/waits/SLEEP_MSDBSTARTUP
N'SLEEP_SYSTEMTASK', -- https://www.sqlskills.com/help/waits/SLEEP_SYSTEMTASK
N'SLEEP_TASK', -- https://www.sqlskills.com/help/waits/SLEEP_TASK
N'SLEEP_TEMPDBSTARTUP', -- https://www.sqlskills.com/help/waits/SLEEP_TEMPDBSTARTUP
N'SNI_HTTP_ACCEPT', -- https://www.sqlskills.com/help/waits/SNI_HTTP_ACCEPT
N'SOS_WORK_DISPATCHER', -- https://www.sqlskills.com/help/waits/SOS_WORK_DISPATCHER
N'SP_SERVER_DIAGNOSTICS_SLEEP', -- https://www.sqlskills.com/help/waits/SP_SERVER_DIAGNOSTICS_SLEEP
N'SQLTRACE_BUFFER_FLUSH', -- https://www.sqlskills.com/help/waits/SQLTRACE_BUFFER_FLUSH
N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', -- https://www.sqlskills.com/help/waits/SQLTRACE_INCREMENTAL_FLUSH_SLEEP
N'SQLTRACE_WAIT_ENTRIES', -- https://www.sqlskills.com/help/waits/SQLTRACE_WAIT_ENTRIES
N'VDI_CLIENT_OTHER', -- https://www.sqlskills.com/help/waits/VDI_CLIENT_OTHER
N'WAIT_FOR_RESULTS', -- https://www.sqlskills.com/help/waits/WAIT_FOR_RESULTS
N'WAITFOR', -- https://www.sqlskills.com/help/waits/WAITFOR
N'WAITFOR_TASKSHUTDOWN', -- https://www.sqlskills.com/help/waits/WAITFOR_TASKSHUTDOWN
N'WAIT_XTP_RECOVERY', -- https://www.sqlskills.com/help/waits/WAIT_XTP_RECOVERY
N'WAIT_XTP_HOST_WAIT', -- https://www.sqlskills.com/help/waits/WAIT_XTP_HOST_WAIT
N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', -- https://www.sqlskills.com/help/waits/WAIT_XTP_OFFLINE_CKPT_NEW_LOG
N'WAIT_XTP_CKPT_CLOSE', -- https://www.sqlskills.com/help/waits/WAIT_XTP_CKPT_CLOSE
N'XE_DISPATCHER_JOIN', -- https://www.sqlskills.com/help/waits/XE_DISPATCHER_JOIN
N'XE_DISPATCHER_WAIT', -- https://www.sqlskills.com/help/waits/XE_DISPATCHER_WAIT
N'XE_TIMER_EVENT', -- https://www.sqlskills.com/help/waits/XE_TIMER_EVENT
N'XE_LIVE_TARGET_TVF' -- https://www.sqlskills.com/help/waits/XE_LIVE_TARGET_TVF
)
AND waiting_tasks_count > 0
)
SELECT
wait_type AS WaitType,
CAST (Percentage AS DECIMAL(4, 2)) AS Percentage,
CAST (WaitS AS DECIMAL(14, 2)) AS Wait_S,
CAST (ResourceS AS DECIMAL(14, 2)) AS Resource_S,
CAST (SignalS AS DECIMAL(14, 2)) AS Signal_S,
WaitCount AS WaitCount,
CAST ((WaitS / WaitCount) AS DECIMAL (14, 4)) AS AvgWait_S,
CAST ((ResourceS / WaitCount) AS DECIMAL (14, 4)) AS AvgRes_S,
CAST ((SignalS / WaitCount) AS DECIMAL (14, 4)) AS AvgSig_S,
'https://www.sqlskills.com/help/' + wait_type AS Description
FROM Waits
Order By Percentage DESC
Пример результата запроса
Рассмотрим полученную информацию:
WaitType – тип ожидания, благодаря этой информации можно определить, чего именно ждали потоки MS SQL. Далее мы рассмотрим основные типы ожиданий, их причины и способы решения.
Percentage – процент, который занимает данное ожидание
WaitS – общее время ожидания в секундах, состоит из времени ожидания ресурса и времени ожидания сигнала. Подробнее об этих показателях будет сказано далее.
ResourceS – время ожидания ресурса в секундах
SignalS – время ожидания сигнала в секундах
WaitCount – количество ожиданий
Avg… – среднее время ожидания общее, ресурса и сигнала соответственно
Description – ссылка на подробное описание данного типа ожидания.
Обратите внимание, что здесь нет колонки с именем базы данных, статистика ведется в рамках сервера СУБД, вернее, в рамках инстанса, т.е. экземпляра сервера СУБД.
В данном примере бесспорным лидером является ожидание CXPACKET. Это, кстати, довольно частая ситуация для баз 1С.
Но прежде чем перейти к описанию типов ожиданий и их трактовке, необходимо разобраться, как именно фиксируются ожидания и что же такое время ожидания ресурса и время сигнала.
Когда в MS SQL поступает запрос от пользователя, ему назначается один или несколько потоков для исполнения ядром процессора. Несколько потоков может быть в том случае, если запрос «тяжелый» и можно ускорить его выполнение параллельным выполнением на нескольких ядрах.
Поток может находиться в одном из трех состояний:
RUNNING – выполняется на процессоре. Каждому потоку дается квант времени в 4 мс. для выполнения.
SUSPENDED – ожидает какой-то ресурс. Если потоку понадобился какой-то ресурс, то его выполнение прекращается. При этом фиксируется, чего именно ожидал поток (WaitType), и включается счетчик ресурса (ResourceS), увеличивающего время ожидания. Например, поток может ждать, пока будет прочитана страница данных из памяти, или ждать освобождения заблокированных данных и т.д.
RUNNABLE – ожидает выполнения на процессоре. Как только ресурс получен и поток снова готов к исполнению на процессоре, он помещается в очередь, переходит в статус RUNNABLE и ждет от процессора сигнала, что можно начать выполнение. Время, которое готовый к выполнению поток проводит в ожидании процессора, и есть время сигнала (SignalS). Даже если поток не ожидал ресурс, а просто не успел выполниться за 4 мс, он все равно переходит в статус RUNNABLE и ждет своей очереди с типом ожидания SOS_SCHEDULER_YIELD.
Схематично состояния потока можно представить следующим образом:
WaitType – тип ожидания, благодаря этой информации можно определить, чего именно ждали потоки MS SQL. Далее мы рассмотрим основные типы ожиданий, их причины и способы решения.
Percentage – процент, который занимает данное ожидание
WaitS – общее время ожидания в секундах, состоит из времени ожидания ресурса и времени ожидания сигнала. Подробнее об этих показателях будет сказано далее.
ResourceS – время ожидания ресурса в секундах
SignalS – время ожидания сигнала в секундах
WaitCount – количество ожиданий
Avg… – среднее время ожидания общее, ресурса и сигнала соответственно
Description – ссылка на подробное описание данного типа ожидания.
Обратите внимание, что здесь нет колонки с именем базы данных, статистика ведется в рамках сервера СУБД, вернее, в рамках инстанса, т.е. экземпляра сервера СУБД.
В данном примере бесспорным лидером является ожидание CXPACKET. Это, кстати, довольно частая ситуация для баз 1С.
Но прежде чем перейти к описанию типов ожиданий и их трактовке, необходимо разобраться, как именно фиксируются ожидания и что же такое время ожидания ресурса и время сигнала.
Когда в MS SQL поступает запрос от пользователя, ему назначается один или несколько потоков для исполнения ядром процессора. Несколько потоков может быть в том случае, если запрос «тяжелый» и можно ускорить его выполнение параллельным выполнением на нескольких ядрах.
Поток может находиться в одном из трех состояний:
RUNNING – выполняется на процессоре. Каждому потоку дается квант времени в 4 мс. для выполнения.
SUSPENDED – ожидает какой-то ресурс. Если потоку понадобился какой-то ресурс, то его выполнение прекращается. При этом фиксируется, чего именно ожидал поток (WaitType), и включается счетчик ресурса (ResourceS), увеличивающего время ожидания. Например, поток может ждать, пока будет прочитана страница данных из памяти, или ждать освобождения заблокированных данных и т.д.
RUNNABLE – ожидает выполнения на процессоре. Как только ресурс получен и поток снова готов к исполнению на процессоре, он помещается в очередь, переходит в статус RUNNABLE и ждет от процессора сигнала, что можно начать выполнение. Время, которое готовый к выполнению поток проводит в ожидании процессора, и есть время сигнала (SignalS). Даже если поток не ожидал ресурс, а просто не успел выполниться за 4 мс, он все равно переходит в статус RUNNABLE и ждет своей очереди с типом ожидания SOS_SCHEDULER_YIELD.
Схематично состояния потока можно представить следующим образом:
Состояния потока MS SQL
С помощью запроса, представленного выше, можно оценить, каких типов ожидания больше, и посмотреть отношение ожидания ресурса к ожиданию сигнала. Если ожидание сигнала больше, чем ожидание ресурса, это может говорить о возможном недостатке процессорных мощностей.
Такой способ позволяет получить очень подробную информацию об ожиданиях, иногда даже слишком подробную т.к. такое число колонок может запутать.
В Мониторе та же информация отображается более лаконично, при этом так же есть возможность перейти по ссылке и почитать про типы ожиданий.
Такой способ позволяет получить очень подробную информацию об ожиданиях, иногда даже слишком подробную т.к. такое число колонок может запутать.
В Мониторе та же информация отображается более лаконично, при этом так же есть возможность перейти по ссылке и почитать про типы ожиданий.
Ожидания СУБД в Мониторе
CXPACKET
Название CXPACKET образуется от Class Exchange Packet. То есть это ожидание возникает, когда запрос выполняется в несколько потоков и один или несколько потоков ждут данные от остальных.
Это самый обманчивый тип ожиданий. Многие думают, что он указывает на проблемы с параллельностью, и меняют параметр MAXDOP, при этом зачастую снижая, а не повышая производительность системы.
На самом деле CXPACKET – это, скорее, индикатор, который показывает, что есть запросы, которые выполняются параллельно, в несколько потоков. Тем не менее, если таких ожиданий слишком много, то это все же может указывать на определенные проблемы.
Чтобы понять, в каком случае стоит разбираться с этими ожиданиями, необходимо понять, как именно работают потоки при параллельном выполнении запроса.
Ожидания потоков при параллельном выполнении запроса.
Когда запрос выполняется параллельно, потоки делятся на производителей и потребителей. Производители условно добывают данные, а потребители их используют.
Ожидания потоков-производителей значимы, они могут указывать на проблемы, такие ожидания записываются с типом CXPACKET. Потоки-потребители всегда ждут производителей, это естественно и не является проблемой, т.к. они не могут ничего сделать, пока не получат данные.
Проблема в том, что время ожидания потребителей тоже записывается как ожидание с типом CXPACKET. Получается, что значительная часть этого ожидания – это норма, а не проблема.
Чтобы разделить значимые ожидания от незначимых, в SQL Server 2016 SP2 и SQL Server 2017 RTM CU3 из ожидания CXPACKET отдельно выделили ожидания потоков потребителей CXCONSUMER. Именно поэтому в скрипте выше данный тип ожиданий находится в списке исключений – в большинстве случаев он не указывает на проблему. К сожалению, в более младших версиях MS SQL нет такого разделения и ожидания CXPACKET содержат в себе ожидания обоих типов потоков.
Основная причина большого числа ожиданий CXPACKET – это неоптимальные запросы, которые читают или обрабатывают большой объем данных. Легкие запросы никогда не выполняются параллельно, эта мера применяется только к тяжелым запросам.
Какой запрос считать легким, а какой тяжелым, определяется стоимостью плана запроса, которая высчитывается еще до его выполнения. Чем выше стоимость, тем тяжелее запрос для исполнения. По умолчанию, если стоимость плана выше 5, то запрос может начать выполняться параллельно.
Это значение регулируется настройкой Cost Threshold for Parallelism сервера MS SQL.
Название CXPACKET образуется от Class Exchange Packet. То есть это ожидание возникает, когда запрос выполняется в несколько потоков и один или несколько потоков ждут данные от остальных.
Это самый обманчивый тип ожиданий. Многие думают, что он указывает на проблемы с параллельностью, и меняют параметр MAXDOP, при этом зачастую снижая, а не повышая производительность системы.
На самом деле CXPACKET – это, скорее, индикатор, который показывает, что есть запросы, которые выполняются параллельно, в несколько потоков. Тем не менее, если таких ожиданий слишком много, то это все же может указывать на определенные проблемы.
Чтобы понять, в каком случае стоит разбираться с этими ожиданиями, необходимо понять, как именно работают потоки при параллельном выполнении запроса.
Ожидания потоков при параллельном выполнении запроса.
Когда запрос выполняется параллельно, потоки делятся на производителей и потребителей. Производители условно добывают данные, а потребители их используют.
Ожидания потоков-производителей значимы, они могут указывать на проблемы, такие ожидания записываются с типом CXPACKET. Потоки-потребители всегда ждут производителей, это естественно и не является проблемой, т.к. они не могут ничего сделать, пока не получат данные.
Проблема в том, что время ожидания потребителей тоже записывается как ожидание с типом CXPACKET. Получается, что значительная часть этого ожидания – это норма, а не проблема.
Чтобы разделить значимые ожидания от незначимых, в SQL Server 2016 SP2 и SQL Server 2017 RTM CU3 из ожидания CXPACKET отдельно выделили ожидания потоков потребителей CXCONSUMER. Именно поэтому в скрипте выше данный тип ожиданий находится в списке исключений – в большинстве случаев он не указывает на проблему. К сожалению, в более младших версиях MS SQL нет такого разделения и ожидания CXPACKET содержат в себе ожидания обоих типов потоков.
Основная причина большого числа ожиданий CXPACKET – это неоптимальные запросы, которые читают или обрабатывают большой объем данных. Легкие запросы никогда не выполняются параллельно, эта мера применяется только к тяжелым запросам.
Какой запрос считать легким, а какой тяжелым, определяется стоимостью плана запроса, которая высчитывается еще до его выполнения. Чем выше стоимость, тем тяжелее запрос для исполнения. По умолчанию, если стоимость плана выше 5, то запрос может начать выполняться параллельно.
Это значение регулируется настройкой Cost Threshold for Parallelism сервера MS SQL.
Настройки параллелизма MS SQL
Значение 5 является устаревшим, т.к. слишком простые запросы будут выбирать параллельное выполнение, в результате затраты на распараллеливание будут выше, чем выигрыш в скорости. Для большинства систем более оптимальным по умолчанию будет значение 30 и более, можно поставить 50.
Максимальное количество потоков для одного запроса регулируется параметром Max Degree of Parallelism. Если он равен 0, то максимальное число потоков равно числу ядер процессора, которые может использовать MS SQL. Если же параметр равен 1, тогда даже самые тяжелые запросы будут выполняться в один поток.
В материалах 1С часто можно встретить рекомендацию установить Max Degree of Parallelism в значение 1, но тогда Вы вообще отключаете параллельность, в том числе и там, где она будет полезна.
Лучше всего оставить значение Max Degree of Parallelism = 0 и просто поднять значение параметра Cost Threshold for Parallelism минимум до 30.
Если оптимизация запросов и повышение стоимости параллелизма не дали результат и ожидания CXPACKET все равно в топе и при этом сопровождаются высокими значениями ожиданий LATCH_XX и PAGEIOLATCH_XX или SOS_SCHEDULER_YIELD, только тогда это указывает на то, что медленный/неэффективный параллелизм сам по себе является причиной проблем с производительностью. В этом случае стоит рассмотреть возможность изменения параметра Max Degree of Parallelism.
Подведем небольшой итог:
PAGEIOLATCH_XX
Ожидания PAGEIOLATCH_XX появляются, когда сеансу нужна страница, которой нет в памяти, и ему приходится ждать, пока она будет прочитана с диска, чтобы заблокировать ее и прочитать или изменить.
Если сеанс обращается к странице для чтения, то мы увидим ожидание PAGEIOLATCH_SH. Если для записи, то PAGEIOLATCH_EX. Здесь имеются в виду служебные блокировки СУБД, а не блокировки данных для параллельного доступа.
Ожидания PAGEIOLATCH_XX – также одни из самых сложных для правильной диагностики, поскольку выглядят как симптом слабой дисковой подсистемы. Причина действительно может быть в ней, еще причиной может быть недостаточное количество оперативной памяти. Но более вероятно, что причиной являются плохо написанные запросы, выполняющие гораздо больше операций ввода-вывода, чем необходимо.
Давайте разберем все три возможные причины по очереди.
1. Неоптимальные запросы
Неоптимальные запросы – это наиболее частая и вероятная причина, особенно для баз 1С.
Если запрос выполняется со сканированием вместо поиска по индексу, то, скорее всего, он читает гораздо больше данных, чем необходимо, а это приводит к чрезмерной нагрузке на диск. SQL Server обычно распараллеливает эти большие сканы, поэтому часто можно увидеть ожидание PAGEIOLATCH_XX в сочетании с ожиданием CXPACKET.
С помощью следующего запроса можно увидеть топ запросов, использующих диск:
Максимальное количество потоков для одного запроса регулируется параметром Max Degree of Parallelism. Если он равен 0, то максимальное число потоков равно числу ядер процессора, которые может использовать MS SQL. Если же параметр равен 1, тогда даже самые тяжелые запросы будут выполняться в один поток.
В материалах 1С часто можно встретить рекомендацию установить Max Degree of Parallelism в значение 1, но тогда Вы вообще отключаете параллельность, в том числе и там, где она будет полезна.
Лучше всего оставить значение Max Degree of Parallelism = 0 и просто поднять значение параметра Cost Threshold for Parallelism минимум до 30.
Если оптимизация запросов и повышение стоимости параллелизма не дали результат и ожидания CXPACKET все равно в топе и при этом сопровождаются высокими значениями ожиданий LATCH_XX и PAGEIOLATCH_XX или SOS_SCHEDULER_YIELD, только тогда это указывает на то, что медленный/неэффективный параллелизм сам по себе является причиной проблем с производительностью. В этом случае стоит рассмотреть возможность изменения параметра Max Degree of Parallelism.
Подведем небольшой итог:
- Далеко не всегда ожидание CXPACKET означает проблемы, скорее, это признак распараллеливания запросов.
- Большинство ожиданий такого типа решаются повышением стоимости параллелизма в настройках сервера и оптимизацией тяжелых запросов.
- После изменения настроек сервера не забудьте сбросить статистику ожиданий MS SQL с помощью команды DBCC SQLPERF, описанной выше, чтобы проверить влияние изменившейся настройки.
PAGEIOLATCH_XX
Ожидания PAGEIOLATCH_XX появляются, когда сеансу нужна страница, которой нет в памяти, и ему приходится ждать, пока она будет прочитана с диска, чтобы заблокировать ее и прочитать или изменить.
Если сеанс обращается к странице для чтения, то мы увидим ожидание PAGEIOLATCH_SH. Если для записи, то PAGEIOLATCH_EX. Здесь имеются в виду служебные блокировки СУБД, а не блокировки данных для параллельного доступа.
Ожидания PAGEIOLATCH_XX – также одни из самых сложных для правильной диагностики, поскольку выглядят как симптом слабой дисковой подсистемы. Причина действительно может быть в ней, еще причиной может быть недостаточное количество оперативной памяти. Но более вероятно, что причиной являются плохо написанные запросы, выполняющие гораздо больше операций ввода-вывода, чем необходимо.
Давайте разберем все три возможные причины по очереди.
1. Неоптимальные запросы
Неоптимальные запросы – это наиболее частая и вероятная причина, особенно для баз 1С.
Если запрос выполняется со сканированием вместо поиска по индексу, то, скорее всего, он читает гораздо больше данных, чем необходимо, а это приводит к чрезмерной нагрузке на диск. SQL Server обычно распараллеливает эти большие сканы, поэтому часто можно увидеть ожидание PAGEIOLATCH_XX в сочетании с ожиданием CXPACKET.
С помощью следующего запроса можно увидеть топ запросов, использующих диск:
IF object_id('T1') IS NOT NULL
DROP TABLE t1;
SELECT
SUM(qs.total_worker_time) AS worker_time,
SUM(total_logical_reads + total_logical_writes) AS IO
INTO T1
FROM sys.dm_exec_query_stats qs
SELECT TOP 20
qs.execution_count AS 'Количество выполнений',
(qs.total_elapsed_time / qs.execution_count) / 1000000 AS 'Среднее время', -- Среднее время
(qs.total_rows / qs.execution_count) AS 'Среднее количество строк', -- Среднее количество строк
qs.total_worker_time * 100 / T1.worker_time AS '% CPU', -- Процент CPU
((total_logical_reads + total_logical_writes) * 100) / T1.IO AS '% IO', -- Процент нагрузки на диск
st.text AS 'Запрос', -- Текст запроса
qp.query_plan 'План' -- План запроса
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
INNER JOIN T1 AS T1 ON 1 = 1
WHERE qp.dbid = DB_ID()
ORDER BY '% IO' DESC;
DROP TABLE T1
2. Недостаток оперативной памяти
Второй вариант, системе действительно может не хватать памяти. Например, значительно выросли объемы данных или количество пользователей – и памяти объективно стало требоваться больше.
Чтобы убедиться в этом, мы можем проверить счетчики памяти PerfMon.
Если видим, что счетчик Page Life Expectancy (если используется процессор с NUMA, то смотрим Buffer Node:Page Life Expectancy) низкий или постоянно колеблется, при этом наблюдаются ненулевые значения для счетчика Free List Stalls/sec и более 20 для Lazy Writes/sec, то можем быть уверены – буфер испытывает нехватку памяти. Это означает, что он не может сохранять страницы в памяти в течение разумного периода времени.
Однако причиной, опять же, могут быть неоптимальные запросы, с ними нужно работать в первую очередь.
3. Медленная дисковая подсистема
Иногда основная причина заключается в медленном вводе-выводе, и нам нужно рассмотреть возможность обновления дисковой подсистемы или перераспределения рабочей нагрузки.
Здесь на помощь снова могут прийти счетчики PerfMon, например, Physical Disk\Avg. Disk sec/Read и Physical Disk\Avg. Disk sec/Write. Задержка по каждому из счетчиков не должна превышать 10 мс, хотя сейчас, в эпоху SSD, требования могут быть еще жестче.
Итоги по данному ожиданию:
SOS_SCHEDULER_YIELD
Если общее время данного типа ожидания невелико, то можно не беспокоиться. Однако если это ожидание находится в топе, то это признак запросов с чрезвычайно интенсивной нагрузкой на процессор.
Опять же, возможны два варианта: либо запросы неоптимальные и сильно нагружают процессор (чаще всего), либо процессор перестал справляться с увеличенной нагрузкой.
Если есть высокие ожидания SOS_SCHEDULER_YIELD, но нет высоких PAGEIOLATCH, то это признак, что потоки постоянно используют выделенный квант времени процессора в 4 мс без дополнительных ожиданий на ресурсе, т.е. в данном случае узкое место – процессор.
Если же ожидания PAGEIOLATCH тоже высоки, то, вероятно, дело в неоптимальных запросах.
PAGELATCH_XX
Ожидание PAGELATCH_XX происходит, когда поток вынужден ждать доступа к странице данных, уже находящейся в буфере оперативной памяти.
Обратите внимание: не нужно путать этот тип ожидания с ожиданием PAGEIOLATCH_XX, т.к. там страницы в буфере еще нет.
Наиболее известной причиной ожидания PAGELATCH_XX является высококонкурентный доступ к базе данных TempDB. Дело в том, что при создании временных таблиц в TempDB необходимо внести записи в некоторые служебные таблицы, и вот на этих служебных таблицах зачастую и происходит ожидание, т.к. интенсивность работы с TempDB очень большая.
Для решения рекомендуется создать несколько файлов TempDB, и если у вас версия младше MS SQL 2016, то нужно обязательно включить флаг 1118.
Кроме того, ожидания PAGELATCH_XX могут возникать при большом количестве одновременных вставок (INSERT) в таблицу, которая использует кластерный индекс с постоянно возрастающим значением ключа. Если значение ключа индекса всегда растет, то вставка происходит в последнюю страницу индекса, и поскольку конкуренция высокая, то начинаются ожидания.
Для анализа таких ожиданий можно запустить следующий запрос в моменты высокой нагрузки:
Второй вариант, системе действительно может не хватать памяти. Например, значительно выросли объемы данных или количество пользователей – и памяти объективно стало требоваться больше.
Чтобы убедиться в этом, мы можем проверить счетчики памяти PerfMon.
Если видим, что счетчик Page Life Expectancy (если используется процессор с NUMA, то смотрим Buffer Node:Page Life Expectancy) низкий или постоянно колеблется, при этом наблюдаются ненулевые значения для счетчика Free List Stalls/sec и более 20 для Lazy Writes/sec, то можем быть уверены – буфер испытывает нехватку памяти. Это означает, что он не может сохранять страницы в памяти в течение разумного периода времени.
Однако причиной, опять же, могут быть неоптимальные запросы, с ними нужно работать в первую очередь.
3. Медленная дисковая подсистема
Иногда основная причина заключается в медленном вводе-выводе, и нам нужно рассмотреть возможность обновления дисковой подсистемы или перераспределения рабочей нагрузки.
Здесь на помощь снова могут прийти счетчики PerfMon, например, Physical Disk\Avg. Disk sec/Read и Physical Disk\Avg. Disk sec/Write. Задержка по каждому из счетчиков не должна превышать 10 мс, хотя сейчас, в эпоху SSD, требования могут быть еще жестче.
Итоги по данному ожиданию:
- Этот тип ожиданий указывает на очень интенсивный ввод-вывод, обычно проблема в неоптимальных запросах, которые читают много лишних данных
- Иногда это может быть показателем малого объема оперативной памяти, для анализа нужно использовать счетчики PerfMon
- Также может быть симптомом слабой дисковой подсистемы, для анализа также нужны данные PerfMon.
SOS_SCHEDULER_YIELD
Если общее время данного типа ожидания невелико, то можно не беспокоиться. Однако если это ожидание находится в топе, то это признак запросов с чрезвычайно интенсивной нагрузкой на процессор.
Опять же, возможны два варианта: либо запросы неоптимальные и сильно нагружают процессор (чаще всего), либо процессор перестал справляться с увеличенной нагрузкой.
Если есть высокие ожидания SOS_SCHEDULER_YIELD, но нет высоких PAGEIOLATCH, то это признак, что потоки постоянно используют выделенный квант времени процессора в 4 мс без дополнительных ожиданий на ресурсе, т.е. в данном случае узкое место – процессор.
Если же ожидания PAGEIOLATCH тоже высоки, то, вероятно, дело в неоптимальных запросах.
PAGELATCH_XX
Ожидание PAGELATCH_XX происходит, когда поток вынужден ждать доступа к странице данных, уже находящейся в буфере оперативной памяти.
Обратите внимание: не нужно путать этот тип ожидания с ожиданием PAGEIOLATCH_XX, т.к. там страницы в буфере еще нет.
Наиболее известной причиной ожидания PAGELATCH_XX является высококонкурентный доступ к базе данных TempDB. Дело в том, что при создании временных таблиц в TempDB необходимо внести записи в некоторые служебные таблицы, и вот на этих служебных таблицах зачастую и происходит ожидание, т.к. интенсивность работы с TempDB очень большая.
Для решения рекомендуется создать несколько файлов TempDB, и если у вас версия младше MS SQL 2016, то нужно обязательно включить флаг 1118.
Кроме того, ожидания PAGELATCH_XX могут возникать при большом количестве одновременных вставок (INSERT) в таблицу, которая использует кластерный индекс с постоянно возрастающим значением ключа. Если значение ключа индекса всегда растет, то вставка происходит в последнюю страницу индекса, и поскольку конкуренция высокая, то начинаются ожидания.
Для анализа таких ожиданий можно запустить следующий запрос в моменты высокой нагрузки:
SELECT owt.session_id,
owt.wait_duration_ms, owt.wait_type, owt.blocking_session_id,
owt.resource_description, es.program_name, est.text,
est.dbid, eqp.query_plan, er.database_id, es.cpu_time,
es.memory_usage*8 AS memory_usage_KB
FROM sys.dm_os_waiting_tasks owt
INNER JOIN sys.dm_exec_sessions es ON owt.session_id = es.session_id
INNER JOIN sys.dm_exec_requests er ON es.session_id = er.session_id
OUTER APPLY sys.dm_exec_sql_text (er.sql_handle) est
OUTER APPLY sys.dm_exec_query_plan (er.plan_handle) eqp
WHERE es.is_user_process = 1
ORDER BY owt.session_id;
GO
С помощью представления dm_os_waiting_tasks можно посмотреть, кто прямо сейчас вызывает ожидания – вплоть до текста запроса и его плана.
При необходимости можно сделать фильтр на тип ожидания PAGEIOLATCH.
В отличии от dm_os_wait_stats, которое показывает накопленные данные, представление dm_os_waiting_tasks отображает данные на текущий момент.
Мы рассмотрели лишь четыре распространенных типа ожиданий. На самом деле их великое множество, более подробно узнать обо всех типах ожиданий можно в документации MS SQL.
При необходимости можно сделать фильтр на тип ожидания PAGEIOLATCH.
В отличии от dm_os_wait_stats, которое показывает накопленные данные, представление dm_os_waiting_tasks отображает данные на текущий момент.
Мы рассмотрели лишь четыре распространенных типа ожиданий. На самом деле их великое множество, более подробно узнать обо всех типах ожиданий можно в документации MS SQL.
Подпишитесь на наш канал в Telegram чтобы не пропускать новые материалы
Если вы хотите поделиться своим кейсом, напишите нам на support@1smonitor.ru