Записки оптимизатора 1С (ч.10): Как понять, что процессор — основная боль на вашем сервере СУБД MS SQL Server?

Назрел пост, связанный с нагрузкой на процессор сервера СУБД MS SQL Server.

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

Если нагрузка на процессоре ~100%, огромные очереди к нему, то тут действительно всё просто и обсуждать особо нечего. Сценарий простой: либо ищем что его нагружает, либо система переросла процессор и ему пора накинуть мощностей. В общем, статью не напишешь.

А вот когда нагрузка держится на среднем уровне, но при этом есть очереди и ожидания, то здесь далеко не все так очевидно.

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

Поэтому пойдем от симптомов, и по ходу повествования я буду показывать как размотать ситуацию – на что смотреть, что менять и как оценить результат. Все скрины ниже со счетчиками производительности получены из программы мониторинга производительности Perfexpert.

Типичные симптомы, когда с процессором что-то не то

Основной симптом – процессор не нагружен даже до условных 70-80% и при этом к нему фиксируются очереди. Не эпизодические очереди, а носящие перманентный характер.

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

Разберем несколько примеров.

1. Процессор не нагружен, явной планки сверху нет, но фиксируются постоянные очереди

Хорошо видно, что процессор еле-еле нагружен даже на 50%, а фактически там в среднем процентов 25, но при этом очередь постоянная. Это тревожный звоночек.

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

Или вот пример с менее выраженными очередями, но их всё равно не должно быть при нагрузке на процессор 30-40%.

2. Общая нагрузка на CPU как будто ограничена сверху каким-то значением, очереди к CPU.

На рисунке ниже показана ситуация, когда что-то не дает раскачаться процессору выше 50%. И обратите внимание – в эти интервалы времени очереди возрастают еще больше. То есть очередь растет, а нагрузка на CPU нет. Определенно, часть ядер простаивает, а другая перенапрягается.

Поэтому смотрим внимательно на графики CPU и контролируем наличие горизонтальной планки.

Вот, пожалуй, и все симптомы, не считая жалоб пользователей на медленную работу. А теперь традиционный вопрос «Что делать?».

Причины могут быть разные, комбинироваться они могут тоже по-разному. Поэтому пойдем по шагам.

Проверка лицензирования SQL Server

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

Вот возможные ограничения по процессору в разных редакциях MS SQL Server, о которых нам сообщает вендор:

Итого мы видим, что в Standard Edition (SE) можно использовать максимум 24 ядра или 4 процессора, а в Enterprise Edition (EE) ограничений нет. На самом деле, ограничения и в EE могут быть, но они накладываются не редакцией, а самой лицензией. Сейчас разберем оба этих ограничения. Особенно интересен второй случай с EE, где ограничение может быть совсем неочевидным.

Standard Edition

Рассмотрим оба вида ограничений: и 24 ядра, и 4 процессора. Начнем с ядер.

На входе у нас есть процессор 2 x 16 ядер вот с такой, казалось бы, маленькой нагрузкой и очередями:

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

Один процессор напрягается (80-90%), а второй почти простаивает (5% в пике). Соответственно в среднем по больнице он и показывает нагрузку на CPU 25-30% (см. предыдущий график).

Эта ситуация достаточно легко диагностируется и ее хорошо видно в обычном диспетчере задач:

Задействовано ровно 24 ядра, которые позволяет использовать SQL Standard Edition.

Еще можно зайти в журнал событий SQL Server и увидеть какую запись он делает при старте службы:

«SQL Server detected 2 sockets with 16 cores per socket and 16 logical processors per socket, 32 total logical processors; using 24 logical processors based on SQL Server licensing.»

С ядрами всё, разобрались, тут совсем несложно. Теперь с процессорами.

Ситуация, когда на сервере более 4-х процессоров и одновременно установлен SQL Server SE возможна, скорее всего, по одной причине – виртуализация. Сценарий, когда на какой-нибудь восьмипроцессорный сервер с 384 ядрами ставят SQL Standard Edition отбросим как бредовую.

Например, используя VMware, часто выделяют виртуальной машине не один процессор с 16 ядрами, а 16 процессоров по одному ядру. В мониторинге мы сразу видим в информации о сервере такую конфигурацию.

Соответственно, срабатывает ограничение на 4 процессора и 16 ядерный процессор превращаются в ̶т̶ы̶к̶в̶у̶ четырехядерный.

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

Первый график – общая нагрузка на процессор, и она почти не превышает 25%. Тут, кстати видна та самая горизонтальная полка – работают 4 ядра из 16, как раз 25%. Второй график – нагрузка самого процесса SQL Server. Если это значение поделить на 100, то увидим сколько ядер в пределе он использует. Максимально возможное значение равно 1600 (или 16 ядер), а на вертикальной синей линейке в выбранный момент времени указано 375,93 – это почти максимальное значение на выбранном временном интервале. Т.е. получается 3,75 или с округлением 4 ядра. Третий график – это нулевое ядро процессора. Форма графика совпадает с общей нагрузкой. Только когда общая нагрузка всего 25%, то нулевое ядро нагружено на 100%. Тоже часто бывает хорошим маркером, пользуемся этим счетчиком в работе. Ну и последний график – очереди, тут всё понятно.

Enterprise Edition

Если у вас вид лицензии называется «Enterprise Edition with Server + Client Access License (CAL)», то вы также попадаете на ограничение по ядрам, по аналогии со Standard Edition.

Обратимся опять к вендору и внимательно смотрим на строчку под табличкой:

Вот что можно получить по нагрузке на CPU при таком виде лицензии:

Лог sql сервера:

SQL Server detected 2 sockets with 30 cores per socket and 30 logical processors per socket, 60 total logical processors; using 20 logical processors based on SQL Server licensing. This is an informational message; no user action is required.

Если бы Hyper-Threading был включен, то доступных ядер бы было в 2 раза больше. Но, как вы понимаете, это всё ненастоящие ядра.

В Perfexpert мы сразу видим в параметрах системы тип лицензии, глаз уже привык, срабатывает триггер и сразу понимаем, что надо проверить как там обстоят дела с CPU:

В зеленой рамке – «правильный» вид лицензии, без ограничений. Соответственно, в красной – случай на скрине выше.

Итого по лицензированию:

1) Помним, что MS SQL Server Standard Edition имеет ограничение на 24 ядра или 4 процессора. Если у вас больше ядер или процессоров, то процессор в целом будет гарантированно недозагружен. При этом те ядра, которые используются, будут перегружены и к ним будут образовываться очереди.

2) Если у вас MS SQL Server Enterprise Edition с серверной лицензией + лицензией клиентского доступа (CAL), то вы попадаете на ограничение по 20 физическим или 40 логическим ядрам. Желательно иметь Core-based Licensing.

Проверка балансировки по NUMA-узлам

Стоит сказать пару слов про NUMA и про soft-NUMA, чтобы не было путаницы в терминологии.

NUMA (Non-uniform Memory Access или неравномерный доступ к памяти) – системная архитектура для оптимизации эффективности многопроцессорных компьютерных систем. В отличие от однопроцессорных или однородных систем доступа к памяти (UMA), где каждый процессор имеет равный доступ к одному пулу памяти, NUMA настраивает компьютерную систему с несколькими узлами памяти, подключенными к одному или нескольким процессорам. В NUMA-системах память не является общим, однородным ресурсом, а сегментирована и выделена определенным узлам.

Такая конфигурация позволяет сократить задержку при доступе к памяти, гарантируя, что процессоры в первую очередь обращаются к памяти, расположенной физически ближе к ним. Естественно, есть трафик данных между NUMA-узлами и от того как он управляется зависит общая производительность системы.

Как правило, весь процесс управления маршрутизацией трафика данных ложится либо на операционную систему, либо на ПО (в нашем случае SQL Server) – они понимают и учитывают физическую компоновку процессоров и узлов памяти при распределении процессов. Цель – размещать процессы на узлах таким образом, чтобы максимально использовать локальную память и минимизировать частоту и объем удаленных обращений к памяти.

Soft-NUMA. MS SQL Server разделяет потоки обслуживания на узлы NUMA. Считается (learn.microsoft.com), что при использовании процессоров, содержащих 10 и более ядер на сокет, использование soft-NUMA для разделения аппаратных узлов NUMA на более маленькие узлы обычно повышает масштабируемость и производительность БД. Начиная с SQL Server 2014 (12.x) с пакетом обновления 2 (SP2) и SQL Server 2016 (13.x), soft-NUMA настраивается автоматически на уровне экземпляра базы данных. Если обнаруженное число физических ядер превышает восемь на сокет, ядро СУБД SQL Server создает узлы soft-NUMA, которые в идеале содержат восемь ядер.

Возвращаемся к примерам. Некорректная балансировка может быть как из-за некорректной работы soft-NUMA, так и из-за аппаратного NUMA.

Проблема та же – очереди к процессору при его откровенной недозагруженности.

Начну с примера, когда лицензирование MS SQL Server EE наложилось на разбалансировку по NUMA узлам.

Дано: CPU 2×32 при включенном Hyper-Threading + SQL Server 2014 EE с лицензией с ограничением на 20 физических ядер.

В этой версии SQL Server Auto soft-NUMA отключено, поэтому управление NUMA-узлами ложится на плечи операционки. И разделила она как бы всё поровну, ибо про лицензию SQL Server ничего не знала:

То есть, здесь не просто не используются 24 ядра, а всегда 24 ядра одного и того же процессора.

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

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

Бывают случаи, когда все равномерно, никаких отклонений не видно, но очереди есть.

Вот пример по достаточно нагруженной системе на 4-х процессорной машине со 192 ядрами (c HT):

По графикам все четыре процессора с виду нагружены равномерно, да и по отдельным ядрам все тоже более или менее ровно:

Можно попытаться выделить некоторые ядра, нагруженные более остальных, но на 192 ядрах картина несколько размазывается и тренд совсем неявный. Что может являться причиной неравномерности? Что-то не так с NUMA-узлами?

В данном случае на сервере установлен SQL Server 2019, и опция Auto soft-NUMA включена по умолчанию.

Вот как были распределены soft-NUMA:

Запрос для получения таблички выше (позаимствовал у Glenn Berry на его видеоканале):

<code>-- SQL Server NUMA Node information (SQL Server NUMA Info)
<strong>SELECT</strong> osn<strong>.node_id</strong>, osn<strong>.node_state_desc</strong>, osn<strong>.memory_node_id</strong>, osn<strong>.processor_group</strong>, osn<strong>.cpu_count</strong>, osn<strong>.online_scheduler_count</strong>, 
       osn<strong>.idle_scheduler_count</strong>, osn<strong>.active_worker_count</strong>, 
     osmn<strong>.pages_kb</strong>/1024 <strong>AS</strong> [Committed Memory (MB)], 
     osmn<strong>.locked_page_allocations_kb</strong>/1024 <strong>AS</strong> [Locked Physical (MB)],
     CONVERT(<strong>DECIMAL</strong>(18,2), osmn<strong>.foreign_committed_kb</strong>/1024.0) <strong>AS</strong> [Foreign Commited (MB)],
     osmn<strong>.target_kb</strong>/1024 <strong>AS</strong> [Target Memory Goal (MB)],
     osn<strong>.avg_load_balance</strong>, osn<strong>.resource_monitor_state</strong>
<strong>FROM</strong> sys<strong>.dm_os_nodes</strong> <strong>AS</strong> osn 
INNER <strong>JOIN</strong> sys<strong>.dm_os_memory_nodes</strong> <strong>AS</strong> osmn
<strong>ON</strong> osn<strong>.memory_node_id</strong> = osmn<strong>.memory_node_id</strong>
<strong>WHERE</strong> osn<strong>.node_state_desc</strong> <> N'ONLINE DAC' OPTION (RECOMPILE);</code>Code language: HTML, XML (xml)

SQL Server раздробил аппаратные 4 NUMA-узла на 16 программных по 12 ядер в каждом. Возможно, в этом все дело. Мы не видим (пока) как распределены планировщики (schedulers) в каждой активной sql-сессии, но очень вероятно, что происходит частое дублирование одних и тех же планировщиков на разных сессиях. Замерить это на продуктивной базе тяжело, а отключить soft-NUMA и посмотреть на результат довольно просто – надо лишь выкроить техокно для перезапуска службы.

После перезапуска стало четыре аппаратных NUMA-узла – по количеству socket’ов. В каждом по 48 ядер.

Смотрим очереди и видим совсем другую картину. Не считая точечных пиков, очередь редко поднимается выше 1. Да, она не пропала полностью, но значения «1..2» против «20..30» это прекрасный результат.

Оставшаяся очередь, скорее всего, – результат работы Hyper-Threading на достаточно высоконагруженной системе (20 000+ запросов/сек).

Еще один маркер, который показывает улучшение – это ожидание на процессоре SOS_SCHEDULER_YIELD. Сравним ожидание SOS_SCHEDULER_YIELD по дням с включенным и выключенным soft-NUMA:

Дата	Тип ожидания	WaitS	WaitCount
12.02.2025	soft-NUMA ON	449 713	1 098 221 815
13.02.2025		1 734 262	1 595 936 908
14.02.2025		1 646 684	1 495 175 243

10.03.2025	soft-NUMA OFF	155 225	552 438 182
11.03.2025		265 514	729 054 230
12.03.2025		133 266	616 367 165

Т.е. ожидания и их количество снизились в разы, а в некоторые дни даже на порядок.

Итого по NUMA:

Если с лицензией SQL Server всё хорошо, но на сервере СУБД есть/сохраняются очереди к процессору и/или неравномерная нагрузка по ядрам, то имеет смысл отключить auto soft-NUMA, которая в высоконагруженных системах может приводить к увеличению времени ожиданий SOS_SCHEDULER_YIELD. Следующим шагом можно попробовать отказаться от использования NUMA на уровне BIOS, т.е. перейти к SMP-архитектуре, когда все процессоры имеют равный доступ к ресурсам сервера, в частности, к любому участку оперативной памяти.

Виртуализация на сервере СУБД и Нyper-Threading

Виртуализация сейчас используется повсеместно. В какой-то мере это удобно. Есть соблазн раздать виртуальных ядер больше, чем их есть на физическом хосте (переподписка или overcommit). А Нyper-threading как бы удваивает количество ядер. И администраторы действительно часто этим ̶з̶л̶о̶у̶п̶о̶т̶р̶е̶б̶л̶я̶ю̶т̶ пользуются.

Чем эти технологии не очень хороши для высоконагруженных систем, точнее для серверов СУБД высоконагруженных систем? В таких базах данных количество мелких и очень мелких запросов просто колоссальное. Например, поток в 30-40 тыс. sql-запросов в секунду – это абсолютно нормальная ситуация. Если запрос, выполняемый на сервере СУБД не будет иметь гарантированного доступа к процессорному ядру в свой квант времени, то он попадает на ожидание. И этого кванта у него часто нет, потому что одно и то же физическое ядро либо разрывается между потоками hyper-threading, либо занято другими процессами, а vCPU находятся в режиме ожидания. Либо всё вместе.

Поэтому, очень аккуратно настраивайте виртуализацию и включайте Hyper-threading на серверах СУБД.

Итого

Соберу всё в кучу. Напомню, обсуждали сегодня высоконагруженные сервера баз данных с проблемой недозагрузки процессора и при этом с очередями к нему. Остальные сервера (app, web, терминалы) –не так критичны ко всему описанному. Сегодня только сервера баз данных.

1. Одна из наиболее частых причин возникновения подобных проблем – неудачный выбор лицензии MS SQL Server. Редакция Standard имеет ограничения и по процессору, и по памяти. Поэтому, если вы используете сервер СУБД с более чем 24 ядрами или более с чем 4 процессорами, то ваш вариант лицензии – это только Enterprise Edition Core-based Licensing. Всё остальное приведет к тому, что SQL Server будет использовать не все доступные ядра на сервере. А те, которые будут использоваться, могут стать перегруженными и к ним образуются очереди.

2. Если вы применяете виртуализацию, то проконтролируйте, чтобы сервер СУБД всегда имел гарантированный доступ к тому кол-ву ядер, что ему выделено на хосте гипервизора. Совокупные повышенные нагрузки на всех виртуальных машинах, развернутых на физическом хосте, потенциально могут провоцировать возникновение очередей к процессору на каждой из них. А переподписка (overcommit) в случае сервера СУБД – это плохая история.

3. Использование Hyper-Threading также не всегда дает положительный эффект. С одной стороны, Hyper-threading позволяет обеспечить многопоточность, тем самым ускоряя некоторые процессы, но с другой – логическое ядро никогда не заменит в полной мере физическое и могут наблюдаться ожидания.

4. Ну и последнее – NUMA-узлы. Как мы не раз наблюдали в своей практике, опция auto soft-NUMA в настройках MS SQL Server может давать отрицательный эффект. По умолчанию она включена на всех версиях MS SQL Server, начиная с 2016.

Чем больше ядер на сервере и выше параллелизм (MAXDOP) в настройках SQL Server, тем больше вероятность, что планировщики (schedulers) некорректно распределяются по сессиям и на процессоре образуются избыточные ожидания с типом SOS_SCHEDULER_YIELD. Отключение этой опции передаёт управление NUMA-узлами операционной системе, и в некоторых случаях она делает это успешнее SQL Server.

Кроме того, вы можете отключить использование архитектуры NUMA в BIOS. В разных версиях BIOS это называется по-разному, например, NUMA Group Size Optimization. Чаще эта настройка находится в разделе управления памятью или производительностью.

Буду рад, если поделитесь своими наблюдениями или примерами по неравномерной утилизации CPU и методами борьбы с проблемой.

Ссылки на остальные части Записок оптимизатора 1С: