В этой статье предлагается рассмотреть вариант методики поиска причин высокой загрузки процессора (CPU) на SQL-сервере.

Возможно со стороны участников club-a будут дополнения, это только приветствуется.

Исходные данные

Высокая загрузка CPU на SQL-сервере.

Анализ

Для начала определимся, что каждый из нас подразумевает под высокой нагрузкой.

Варианта тут может быть два:

• Высокая загрузка CPU в диспетчере задач (Task Manager);
• Высокая загрузка CPU в Activity Monitor-е.

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

Шаг 1

Поэтому на шаге 1 в Диспетчере задач убеждаемся, что ресурсы сервера потребляет именно процесс sqlservr.exe:

Если Диспетчер задач показывает значительную загрузку процесса sqlservr.exe, переходим к следующему шагу.

Если это не так, то ваш анализ практически завершен и достаточно выяснить почему другие процессы потребляют значительные ресурсы CPU, при необходимости завершив их.

Частность 1

Стоит отметить, если на сервере с SQL установлены такие службы DIRECTUM-a как WorkFlow, то для уменьшения нагрузки на CPU имеет смысл перенести их на другой сервер, т.к. в отдельные моменты служба WorkFlow может оказывать значительное влияние на весь сервер в целом (естественно это утверждение актуально в случае большого числа процессов, указанных в файле настроек SBWorkflowSrvSettings.xml).

Частность 2

В некоторых случаях в Activity Monitor фиксируется постоянная 100% загрузка ресурсов процессора SQL-сервера, при этом в диспетчере задач загрузка CPU составляет значительно меньше (например, 50 и менее процентов) и используется только часть ядер.

Основными причинами м.б.:
1. Отключена автоматическая установка соответствия использующихся ядер/потоков SQL-серверу, т.н. Affinity Mask. Ядра/потоки процессора заданы вручную. Для автоматического задания необходимо включить следующую настройку:

• Automatically set processor affinity mask for all processors.

2. Используется неверный/ограниченный тип лицензирования по ядрам, тем самым используется только часть ядер, либо часть ядер по какой-либо причине стала неактивна. Проверить можно выполнив следующий t-sql запрос:

select cpu_id, status, is_online from sys.dm_os_schedulers

Статус VISIBLE OFFLINE свидетельствует о неактивном состоянии ядра/потока, т.е. никакие пользовательские запросы не обрабатываются данным ядром. Статус VISIBLE ONLINE свидетельствует о активном состоянии ядра/потока, т.е. пользовательские запросы обрабатываются данным ядром. Более подробно о статусах можно узнать в описании DMV – sys.dm_os_schedulers.

После корректной активации SQL-сервера не д.б. ядер со статусом VISIBLE OFFLINE.

Шаг 2

Итак, мы определили, что основная нагрузка исходит от процесса SQL-сервера.

Далее предположим, что к высокой нагрузке могли привести запросы, выполняемые в конкретных сессиях.

Наиболее тяжелые запросы и сессии можно определить следующими способами:

• Activity Monitor. Способ прост, но имеет один существенный недостаток, в случае высокой загрузки CPU, Activity Monitor просто перестает открываться, либо обновлять данные. Кроме того, нет информации по сессии (картинка кликабельна).

• Динамические представления: sys.dm_exec_query_stats, sys.dm_exec_sql_text и sys.dm_exec_query_plan. Основным достоинством данного способа является то, что мы узнаем среднюю нагрузку на SQL от запроса, сам текст запроса и, в случае необходимости, план запроса.

Пример запроса, использующего эти динамические представления:

SELECT TOP(15) total_worker_time/execution_count as AVGCPU, ST.[text], QP.query_plan
   FROM sys.dm_exec_query_stats QS
   CROSS APPLY sys.dm_exec_sql_text(QS.[sql_handle]) ST
   CROSS APPLY sys.dm_exec_query_plan(QS.plan_handle) QP
ORDER BY AVGCPU DESC

Результат:

Таким образом, имеем запрос который тратит ресурсов CPU на порядок больше всех остальных. Далее можем использовать план запроса для дальнейшего анализа, который может выявить проблему с индексами, статистикой и т.д.

Частность 3

Согласно документации Microsoft при первоначальной выборке из sys.dm_exec_query_stats могут быть неточности в результатах. Для повышения точности рекомендуется выполнить запрос повторно.

• Системные хранимые процедуры (ХП) sp_who, sp_who2 и их производные. Основное достоинство данного способа – идентификация наиболее нагружающей сессии. Сама по себе sp_who2 не очень удобна в использовании, но если несколько доработать Запрос, то можно получить вполне удобный вариант. Недостатком является то, что sp_who2 является недокументированной ХП, т.е. в последующих редакциях она м.б. удалена/изменена/неработоспособна.

Результат:

Итак, получив SPID конкретной сессии, которая нагружает ваш SQL, в случае необходимости можно эту сессию принудительно завершить.

Шаг 3

В случае, если первые шаги не принесли никакого результата, то следующим вариантом, требующим проверки является проверка параметра Degree of Parallelism (DOP).

Если кратко, то это настройка отвечает за то, на сколько ядер может быть распараллелен запрос. Чем сложнее запрос, тем выгоднее его распараллеливать на большее число ядер процессора. С другой стороны, когда большая часть запросов простые (а именно так в большинстве своем в системе DIRECTUM), то необходимости в распараллеливании нет. Стоит учитывать, что при установке DOP=1, тяжелые запросы будут выполняться медленнее.

Изменение данной настройки не требует перезапуска SQL-сервера.

Оценить влияние можно так:

• Сравнить среднее значение загрузки CPU, например, за день до изменения и после;
• Оценить задержки вида CXPACKET в динамическом представлении dm_os_wait_stats, например, запросом вида:

select top 25 * from sys.dm_os_wait_stats order by wait_time_ms desc

Наличие в топе CXPACKET как раз и будет говорить о проблемах с параметром DOP.

Эмпирически можно подобрать наиболее подходящее значение DOP для каждой конкретной системы.

Шаг 4

В случае, если первые три шага по-прежнему не выявили проблемы с высокой нагрузкой на SQL-сервер, то наиболее вероятная причина в запросах, используемых в системе. И с точки зрения администратора системы вряд ли можно как-то повлиять на ее производительность. Примером этого может служить большое число компиляций/ре-компиляций при использовании непараметризированных запросов, описанных в статье Серия статей. SQL внутри. Статья первая. Компиляция запроса.

Выводы

Анализ нагрузки на SQL-сервер нужно проводить комплексно, а именно:

1. Необходимо проверить установлены ли оптимальные настройки ОС и самого SQL-сервера.
2. Необходимо убедится, что высокая нагрузка на SQL-сервер не создается отдельными видами запросов или несколькими тяжелыми запросами от отдельных пользователей.
3. В случае если не удается явно выявить причину высокой загрузки, необходимо начать работы по анализу и оптимизации кода, возможно он был написан неоптимально, с точки зрения высоких нагрузок на БД.

 sp_who2_ex.sql (1,33 Кб)