Технологические вопросы крупных внедрений
16.11.2022

Методика расследования причин медленной работы Microsoft SQL Server


При оценке текущей нагрузки на Microsoft SQL Server, стандартные средства мониторинга (например, Task Manager) могут отобразить неверный результат. Microsoft SQL Server содержит в себе подсистему SQLOS – это надстройка над операционной системой (фактически ОС внутри Windows). SQLOS сама управляет: распределением памяти, состоянием процессов… Таким образом, оценка загрузки СУБД стандартными средствами Windows не отобразит реальной ситуации. 

 

Подробнее про SQLOS:

http://www.sql.ru/articles/mssql/2005/032402sqlosunleashed.shtml

http://blogs.msdn.com/b/slavao/archive/2005/07/20/441058.aspx

 

Более правильный подход – оценивать проблему исходя из анализа "ожиданий".

 

Механизм ожиданий

Механизм ожиданий был изначально придуман разработчиками Microsoft SQL Server для того, чтобы самим понимать в каком месте проблема, т.е. это был некий отладочный механизм. По данной причине, ожидания были малоизвестны и практически не документированы.

Сейчас ситуация исправляется. Ожидания выходят на первый план при анализе работы Microsoft SQL Server.

https://msdn.microsoft.com/ru-ru/library/ms179984(v=sql.120).aspx

Что внутри

Операции в Microsoft SQL Server (например, запросы) никогда не исполняются непрерывно от начала и до конца. Операция разбивается на мелкие кванты (около 4-х миллисекунд). Microsoft SQL Server циклично бежит по всем соединениям и исполняет по одному кванту из каждого. Данный подход обеспечивает работоспособность сервера при огромном количестве соединений.

Перед тем, как выполнить очередной квант, проверяется доступность необходимых ресурсов: диск, процессор, память, сеть, блокировки… Если необходимый ресурс недоступен, то соединение переходит в режим ожидания. При переходе в режим ожидания, соединение сигнализирует о том, какой ресурс ему необходим. Для каждого такого ожидания накапливается время простоя. 

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

 

Соответственно, собрав статистику по ожидаемым ресурсам, можно определить узкие места в работе СУБД.

Activity Monitor

Самым простым способом просмотра текущих ожиданий в Microsoft SQL Server, является утилита - "Activity Monitor". Открыть ее можно с помощью Management Studio.

 

 

 

Период обновления данных задается путем нажатия правой клавиши мыши в группе "Overview".

 

 

Overview

В группе "Overview" отображается информация:

• загрузка процессоров

• количество задач в состоянии ожидания ресурсов

• нагрузка на подсистему ввода-вывода

• число запросов в секунду

 

О проблемах в работе Microsoft SQL Server свидетельствует наличие значений на графике Waiting Tasks. Если значения есть, то необходимо раскрыть группу "Resource Waits".

Resource Waits

В группе "Resource Waits" отображаются типы ресурсов на которых возникают ожидания.

 

 

Таблицу необходимо упорядочить по убыванию значений колонки "Average Waiter Count". 

 

Wait Time (ms/sec)

Время ожидания в "миллисекундах в секунду" с момента последнего обновления.

 

Recent Wait Time (ms/sec)

Самое большое среднее время ожидания ресурса в "миллисекундах в секунду" с момента последнего обновления.

 

Average Waiter Count

Среднее количество задач ждущих ресурсы данной категории за период перед последним обновлением Activity Monitor

 

Cumulative Wait Time (sec)

Накопленное время (в секундах) ожидания ресурсов данной категории с момента старта SQL Server или с момента принудительной очистки статистики по ожиданиям.

 

После определения категории проблемы, необходимо открыть группу "Processes".

Processes

В группе "Processes" отображается список текущих подключений к серверу БД и их состояние.

 

 

Необходимо установить отбор по колонке Wait Type в значение (NonBlanks). В таблице останутся только соединения, ожидающие освобождения ресурсов.

 

Колонка "Task State" может содержать значения:  

Колонка "Wait Time (ms)" показывает сколько миллисекунд соединение находилось в режиме ожидания ресурсов за период обновления.

 

Колонка "Wait Type" содержит имя ожидаемого ресурса.

 

По нажатию правой кнопки мыши на строке соединения, можно посмотреть текущий запрос или завершить это соединение

 

 

 

Для детального расследования причин медленной работы приложения с Microsoft SQL Server, необходимо ориентироваться на значение в колонке "Wait Type". 

 

Разберем подробно основные типы ожиданий, характерные для 1С-Предприятия.

 

Типы ожиданий

Процессор

Ожидание CXPACKET в большинстве случаев возникает из-за настройки max degree of parallelism = 0. Значение настройки равное 0 устанавливается по умолчанию.

 

 

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

 

При этом ,стоит учесть, что запаздывающий поток может работать медленно, например, из-за того, что он выбрал обход сильно фрагментированной части индекса или ожидает отклика от системы ввода-вывода. Т.е. источником проблем может являться совсем не процессор. Однако, для более предсказуемого поведения сервера, стоит уменьшить параллельность операций, а потом разбираться с первопричиной возникновения ожидания  CXPACKET (если в этом будет необходимость).

 

 

При возникновении данного типа ожидания, первым делом необходимо установить настройку max degree of parallelism = 1. Значение данной настройки большее 1 может быть полезно в системах, где нет большого числа мелких операций, а есть большие запросы, исполняемые небольшим количеством соединений (например, OLAP системы).

 

Внутренние ресурсы

Ожидания вида LATCH_* относятся к внутренним механизмам работы Microsoft SQL Server. Механизм latch (защелки) обеспечивает целостность внутренних структур данных, необходимых для работы сервера, путем наложения кратковременных блокировок. 

Данный тип ожиданий плохо описан. В случае, когда ожидание LATCH_* является проблемой, можно попытаться найти его описание в интернете. Если описание не найдено, то необходимо обращаться в службу тех. поддержки Microsoft.

 

Подсистема ввода-вывода

PAGEIOLATCH_* - Соединение пытается заблокировать страницу данных (в буферном пуле), которая в этот момент либо обновляется с диска, либо сбрасывается на диск. Кратковременные ожидания не являются проблемой. Длительное время ожидания указывает на проблему в дисковой подсистеме, на которой расположены файлы данных (.mdf). 

 

Длительное ожидание WRITELOG указывает на медленную работу дисковой подсистемы, где расположен журнал транзакций. 

 

Очень часто встречается ошибочное мнение, что скорость дисковой подсистемы для журнала транзакций не важна. Однако, согласно рекомендации от Microsoft время отклика "диска" с файлами базы данных должно составлять 10-20 миллисекунд, а "диска" с файлами журнала транзакций 1-5 мс (подробнее про оценку скорости отклика дисковой подсистемы см. http://social.technet.microsoft.com/wiki/contents/articles/3214.monitoring-disk-usage.aspx). 

 

Ожидание LOGBUFFER это следствие ожидания WRITELOG. После COMMIT TRANSACTION необходимая информация, для записи в журнал, помещается в буфер (LOGBUFFER). Из этого буфера она должна быть сброшена на диск. Если Microsoft SQL Server не успевает сбрасывать транзакции из буфера на диск (ожидание WRITELOG), то буфер переполняется и транзакции не могут поместить в него новые данные. В этот момент весь сервер "останавливается" и начинает заниматься только сбросом буфера транзакций на диск.

 

IO_COMPLETION появляется, чаще всего, если дисковая подсистема работает недостаточно быстро при операциях:

• создание базы данных

• запись trace-файлов

• операция Sort при исполнении запроса

 

Большое значение ASYNC_IO_COMPLETION может указывать, что происходит слишком частое расширение файлов журналов транзакций (см. свойства базы данных).

 

Операция расширения файлов (Autogrows) – крайне затратная. После того, как выделено новое место на диске, Microsoft SQL Server начинает "забивать" это пространство нулями. В этот момент ни одна транзакция не может изменить данные в базе.

Чтобы избежать операции очистки нового пространства, необходимо использовать механизм мгновенной инициализации файлов (Instant File Initialization

https://msdn.microsoft.com/en-us/library/ms175935.aspx). Instant File Initialization не работает при расширении журнала транзакций, поэтому размер журнала транзакций следует устанавливать достаточным для накопления в нем информации о транзакциях (без расширения файла) до выполнения операции backup, которая уберет из журнала все "старые" транзакции.

 

Для того, чтобы определить файлы с максимальной нагрузкой, можно воспользоваться группой Data File I/O в Activity Monitor.

 

Сеть

Ожидание ASYNC_NETWORK_IO появляются когда Microsoft SQL Server отдает результат запроса приложению, но приложение забирает его медленно. Это может указывать как на проблемы с сетью, так и на проблемы с производительностью приложения. Для начала необходимо посмотреть загрузку канала между сервером баз данных и приложением. Если канал не загружен, то разбираться с приложением.

 

Блокировки

Ожидания LCK_* указывают на конфликты блокировок. Методы устранения конфликтов блокировок описаны в статьях :

https://its.1c.ru/db/metod8dev#content:5841:hdoc

https://its.1c.ru/db/metod8dev#content:4051:hdoc

 

Память

RESOURCE_SEMAPHORE возникает, чаще всего, когда в запросе есть операции Hash или Sort и сервер решает, что для выполнения этой операции необходимо дополнительно выделить память. Постоянное наличие данного типа ожидания может свидетельствовать о нехватке оперативной памяти.

 

"Не опасные" ожидания

Существует множество ожиданий, наличие которых не является проблемой. Например, SLEEP_TASK означает, что задача находится в неактивном состоянии и ждет события на "пробуждение". Такие типы ожиданий, как правило, имеют огромное время ожидания, что вызывает беспокойство у начинающих администраторов баз данных.

 

Примерный перечень таких ожиданий: 

 

BROKER_TASK_STOP, BROKER_TO_FLUSH, BROKER_TRANSMITTER, CHECKPOINT_QUEUE, CHKPT, CLR_AUTO_EVENT, CLR_MANUAL_EVENT, CLR_SEMAPHORE, DBMIRROR_DBM_EVENT, DBMIRROR_EVENTS_QUEUE, DBMIRROR_WORKER_QUEUE, DBMIRRORING_CMD, DIRTY_PAGE_POLL, DISPATCHER_QUEUE_SEMAPHORE, EXECSYNC, FSAGENT, FT_IFTS_SCHEDULER_IDLE_WAIT, FT_IFTSHC_MUTEX, HADR_CLUSAPI_CALL, HADR_FILESTREAM_IOMGR_IOCOMPLETION, HADR_LOGCAPTURE_WAIT, HADR_NOTIFICATION_DEQUEUE, HADR_TIMER_TASK, HADR_WORK_QUEUE, KSOURCE_WAKEUP, LAZYWRITER_SLEEP, LOGMGR_QUEUE, ONDEMAND_TASK_QUEUE, PWAIT_ALL_COMPONENTS_INITIALIZED, QDS_PERSIST_TASK_MAIN_LOOP_SLEEP, QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP, REQUEST_FOR_DEADLOCK_SEARCH, RESOURCE_QUEUE, SERVER_IDLE_CHECK, SLEEP_BPOOL_FLUSH, SLEEP_DBSTARTUP, SLEEP_DCOMSTARTUP, SLEEP_MASTERDBREADY, SLEEP_MASTERMDREADY, SLEEP_MASTERUPGRADED, SLEEP_MSDBSTARTUP, SLEEP_SYSTEMTASK, SLEEP_TASK, SLEEP_TEMPDBSTARTUP, SNI_HTTP_ACCEPT, SP_SERVER_DIAGNOSTICS_SLEEP, SQLTRACE_BUFFER_FLUSH, SQLTRACE_INCREMENTAL_FLUSH_SLEEP, SQLTRACE_WAIT_ENTRIES, WAIT_FOR_RESULTS, WAITFOR, WAITFOR_TASKSHUTDOWN, WAIT_XTP_HOST_WAIT, WAIT_XTP_OFFLINE_CKPT_NEW_LOG, WAIT_XTP_CKPT_CLOSE, XE_DISPATCHER_JOIN, XE_DISPATCHER_WAIT, XE_TIMER_EVENT

 

Получение статистики по ожиданиям запросом

Копировать в буфер обмена
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 AND wait_type NOT LIKE '%SLEEP%'
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
 
Ожидания, полученные данным запросом, являются накопительными, т.е. данные по ним суммируются с момента старта Microsoft SQL Server.

Чтобы сбросить статистику по ожиданиям, необходимо выполнить команду:

 
Копировать в буфер обмена
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
GO

 

Административное подключение

В ситуации, когда Microsoft SQL Server критично загружен и нет возможности подключиться, однако необходимо диагностировать проблему или удалить соединение, создавшее нагрузку, можно воспользоваться выделенным административным подключением "diagnostic connection for administrators (DAC)". Под это подключение выделены отдельные ресурсы, которые не могут быть заняты ни каким другим соединением. По умолчанию, соединение разрешено только из клиента, запущенного на сервере. Сетевые подключения не разрешаются, пока они не включены с помощью хранимой процедуры sp_configure с параметром "remote admin connections".

EXEC sp_configure 'remote admin connections'

 

При разрешенных сетевых административных подключениях, к Microsoft SQL Server можно подключиться через Management Studio, указав в имени сервера префикс "admin:" (admin:<имя инстанса>).

 

Можно воспользоваться утилитой командной строки sqlcmd.exe (https://www.microsoft.com/ru-ru/download/details.aspx?id=36433), указав в параметрах подключения ключ –A.

 

Подробнее см. https://technet.microsoft.com/ru-ru/library/ms189595(v=sql.120).aspx