Одним из свойств экземпляра SQL Server, которое вы можете настроить для оптимизации запросов, является максимальная степень параллелизма (MAXDOP). MAXDOP – это конфигурация сервера, которая позволяет запускать SQL Server на нескольких процессорах. Он также определяет количество процессоров, используемых для параллельного выполнения плана.
Для запросов, выполняемых параллельно, процесс выполнения распределяет рабочие потоки между несколькими логическими процессорами на основе значения MAXDOP. Настройка MAXDOP на правильное значение может помочь вам сократить время выполнения запроса и максимизировать производительность запроса.
Для SQL Server 2016 и более поздних версий значение MAXDOP по умолчанию зависит от вычислений, основанных на количестве процессоров, доступных во время установки. Примечательно, что значение по умолчанию может работать для большинства запросов, но не для всех. Кроме того, конфигурация вашего оборудования может измениться, и значение MAXDOP по умолчанию может отражать что-то другое. Поэтому мы рекомендуем изменить это значение в соответствии с вашей конкретной средой и типом рабочей нагрузки.
Значение MAXDOP влияет на производительность ваших запросов, а неправильное значение может привести к потере производительности. Выполнение больших одиночных запросов займет больше времени, если значение слишком мало. Но, если значение слишком велико, несколько запросов могут перегрузить сервер.
В этой практической демонстрации будет рассмотрено, как точно настроить значение MAXDOP, и обсуждены рекомендации по успешному выполнению этой задачи.
Предварительные требования
Чтобы продолжить, убедитесь, что у вас есть:
- SQL Server установлен в вашей среде разработки
- Базовое понимание параллелизма
Рекомендации по настройке MAXDOP
Определение идеальной настройки для значения MAXDOP зависит от вашего оборудования, среды, нагрузки, отдельной инструкции SQL и других факторов. Следовательно, установка оптимального значения MAXDOP требует от вас поэкспериментировать и точно настроить его, чтобы получить правильное значение.
Значение MAXDOP должно основываться на конфигурации неравномерного доступа к памяти (NUMA). Вам нужно определить, сколько логических процессоров у вас в узле NUMA, затем установите свое значение в соответствии с рекомендациями Microsoft. Если рекомендуемые значения не подходят для вашей рабочей нагрузки, вам необходимо точно настроить значение, пока вы не добьетесь повышения производительности.
Установите значение MAXDOP
Вы можете переопределить значение MAXDOP, установленное на уровне сервера, значениями MAXDOP на уровнях базы данных, запроса или группы рабочей нагрузки.
Метод, который вы должны выбрать, зависит от того, насколько хорошо работают ваша база данных и приложение. Просто имейте в виду, что существуют уровни приоритета. Конфигурации групп рабочей нагрузки переопределяют остальные конфигурации.
Конфигурации на уровне запросов переопределяют конфигурации в области базы данных и на уровне сервера.
Например, конфигурация уровня сервера влияет на все экземпляры, запущенные на SQL Server. Хотя этот уровень может работать для большинства баз данных на SQL Server, он может работать не для всех. Итак, вместо перенастройки сервера установите значение MAXDOP для тех баз данных, производительность которых отстает. Но помните, это значение может работать только для некоторых запросов в этой базе данных. Здесь пригодятся подсказки по запросам, поскольку вы можете использовать их для оптимизации производительности на уровне запросов.
В следующем разделе будет рассказано, как установить значение на различных уровнях.
Как установить значение MAXDOP на уровне сервера
В дополнение к использованию SQL Server Management Studio для настройки MAXDOP, вы также можете установить значение MAXDOP на уровне экземпляра SQL Server, используя следующий код Transact-SQL (T-SQL):
USE <database_name>; GO EXEC sp_configure 'show advanced options', '1'; GO RECONFIGURE WITH OVERRIDE; GO EXEC sp_configure 'max degree of parallelism', '16'; GO RECONFIGURE WITH OVERRIDE; GO
При этом значение MAXDOP устанавливается равным 16.
Как установить значение MAXDOP на уровне группы рабочей нагрузки
Вы можете установить MAXDOP на уровне группы рабочей нагрузки, связав его с пулом ресурсов Resource Governor, используя следующий код:
CREATE WORKLOAD GROUP <group_name>
WITH
(MAX_DOP = 4)
USING <pool_name>;
GO
Как установить значение MAXDOP на уровне базы данных
Вы можете установить значение MAXDOP на уровне базы данных, используя приведенный ниже код:
USE <database_name>; GO ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 16; GO
Как установить значение MAXDOP на уровне запроса
Вы также можете установить значение MAXDOP на уровне запроса, используя подсказки к запросу, как показано в коде иллюстрации ниже:
SELECT * FROM <table> OPTION (MAXDOP 2); GO
Рекомендации по настройке MAXDOP
При принятии решения о том, где установить значение MAXDOP, учитывайте следующее:
- Конфигурация в области базы данных переопределяет конфигурацию сервера вплоть до предела, установленного на уровне группы рабочей нагрузки.
- Конфигурация в области запроса переопределяет значение MAXDOP, указанное на уровнях базы данных и сервера, вплоть до предела, установленного на уровне группы рабочей нагрузки.
- Конфигурация группы рабочей нагрузки ограничивает ненулевые значения MAXDOP, установленные на всех других уровнях, и переопределяет значения MAXDOP, установленные равными 0.
Вы можете комбинировать эти три конфигурации для достижения наилучшей производительности для каждого запроса.
Получить конфигурацию NUMA
Целью NUMA является настройка кластера микропроцессоров, чтобы они могли совместно использовать память локально. Это помогает увеличить скорость доступа к памяти и повысить производительность системы.
Приведенный ниже иллюстративный запрос иллюстрирует, как можно запросить текущую конфигурацию NUMA для SQL Server начиная с 2016 года:
SELECT @@SERVERNAME,
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [Local Machine Name],
(cpu_count / hyperthread_ratio) AS [Physical CPUs],
hyperthread_ratio AS [Hyperthread Ratio],
cpu_count AS [Logical CPUs],
softnuma_configuration AS [Soft-NUMA Configuration],
softnuma_configuration_desc AS [Soft-NUMA Description],
socket_count AS [Available Sockets],
numa_node_count AS [Available NUMA Nodes]
FROM
sys.dm_os_sys_info;
В приведенном выше фрагменте кода:
- ComputerNamePhysicalNetBIOS – это NetBIOS-имя компьютера, на котором запущен экземпляр SQL Server.
- cpu_count – это количество логических процессоров.
- hyperthread_ratio – это количество процессоров, доступных в одном сокете.
- для softnuma_configuration задано одно из следующих значений:
- 0 (настройка отключена, используются аппаратные значения по умолчанию)
- 1 (автоматизированный программный продукт-NUMA)
- 2 (ручная программная настройка NUMA через реестр)
- softnuma_configuration_desc – это либо:
- ВЫКЛЮЧЕНО (функция soft-NUMA отключена)
- ВКЛЮЧЕНО (SQL Server автоматически определяет размеры узлов NUMA)
- РУКОВОДСТВО ПОЛЬЗОВАТЕЛЯ
- socket_count – это количество доступных процессорных сокетов.
- numa_node_count – это количество доступных узлов NUMA.
Получив конфигурацию узла NUMA, вы можете точно настроить параметры MAXDOP на ее основе.
Точная настройка параметров MAXDOP
Как упоминалось ранее, значение MAXDOP, которое хорошо работает в одной системе для конкретного запроса, может давать разные результаты с точки зрения производительности в другой системе. Поэтому мы рекомендуем изменять значение и отслеживать любые узкие места, указывающие на замедление работы.
Основными факторами, определяющими, какое значение MAXDOP использовать, являются:
- Количество узлов NUMA (один или несколько)
- Количество процессоров на узел NUMA
Ниже приведены некоторые рекомендации по значениям MAXDOP в зависимости от количества узлов NUMA и логических процессоров.
Задайте значение с учетом узлов NUMA
NUMA – это подход к проектированию, при котором банк памяти размещается рядом с процессорным сокетом. Каждая комбинация памяти и процессорного сокета называется узлом. Корпорация Майкрософт рекомендует устанавливать MAXDOP в зависимости от количества узлов и процессоров NUMA. Цель состоит в том, чтобы гарантировать, что запрос не будет выполняться за пределами данного узла. Производительность может снизиться, поскольку запрос будет обращаться к внешней памяти, что является дорогостоящим. В идеале любой запрос, выполняемый параллельно, должен использовать тот же процессор и обращаться к вашей локальной памяти.
Ниже приведены рекомендации Microsoft по значениям MAXDOP для SQL Server 2016 и более поздних версий.
Для сервера с одним узлом NUMA:
- Установите значение MAXDOP равным или меньшим количеству логических процессоров, если на сервере меньше или равно восьми логическим процессорам.
- Установите значение MAXDOP равным 8, если количество логических процессоров больше восьми.
Для сервера с несколькими узлами NUMA:
- Установите значение MAXDOP равным или меньшим, чем количество логических процессоров на узел NUMA, если на сервере меньше или равно шестнадцати логическим процессорам на узел NUMA.
- Установите значение MAXDOP равным половине числа логических процессоров, если количество процессоров превышает шестнадцать на узел NUMA. Максимальное значение MAXDOP должно быть равно 16.
Установите базовый уровень
Чтобы измерить эффективность значения MAXDOP, необходимо установить базовый уровень и сравнить с ним изменение производительности. Это означает запуск тестов с разными значениями, сбор статистики ожидания и запись затрат на ваши наиболее ресурсоемкие запросы в кэш плана за определенный период, затем внесение изменений в MAXDOP и повторный сбор данных. Вы можете изменить значение на 1 или 2 и отслеживать его в течение недели.
Просмотрите информацию до и после изменения MAXDOP, чтобы определить, как новое значение MAXDOP влияет на производительность запроса.
Учитывайте порог параллелизма
При точной настройке MAXDOP необходимо учитывать пороговую стоимость параллелизма, которая определяет, какие запросы вы можете выполнять параллельно. Если пороговое значение стоимости параллелизма для сервера равно 5, любые запросы стоимостью 5 и выше будут выполняться на нескольких процессорах, вплоть до значения MAXDOP. Стоимость представляет собой абстрактную меру обработки на конкретной конфигурации оборудования, а не меру времени.
Вместе пороговое значение стоимости параллелизма и значение MAXDOP определяют, насколько дорогостоящим должен быть запрос для параллельного выполнения и сколько параллельных потоков может быть назначено. При установке этих значений идея состоит в том, чтобы назначить дополнительные потоки для больших запросов, которые более эффективно выполняются параллельно, но при этом избежать нехватки рабочих потоков из-за высокой степени параллелизма.
Если есть топовые запросы с большим количеством логических операций чтения, которые выполняются медленно, они могли бы выиграть от параллелизма, поэтому рассмотрите возможность снижения порогового значения затрат. После того, как они будут работать параллельно, постепенно увеличивайте значение MAXDOP и посмотрите, уменьшится ли продолжительность ожидания сервера.
В SQL Server 16 и более поздних версиях функция обратной связи DOP использует хранилище запросов для разумной самостоятельной настройки MAXDOP. Вместо ручной настройки каждого запроса обратная связь DOP снижает избыточный параллелизм для каждого запроса и создает более ресурсоэффективные и масштабируемые рабочие нагрузки.
Заключение
Хотя некоторые экземпляры SQL Server могут нормально работать с настройкой MAXDOP по умолчанию, иногда требуется изменить значение MAXDOP для оптимизации производительности запросов. Значение MAXDOP определяет количество процессоров, которые может использовать запрос, выполняющийся в параллельном выполнении плана. Но более высокое значение MAXDOP не означает более быстрого выполнения. Вместо этого вы должны точно настроить параметры MAXDOP, пока не достигнете желаемого уровня производительности.
При изменении значения MAXDOP сначала получите конфигурацию узла NUMA. В зависимости от вашей рабочей нагрузки вы можете установить это значение на уровне сервера, базы данных или запроса. Значение на уровне сервера применяется ко всему экземпляру SQL Server, если только база данных не имеет собственного значения MAXDOP. Аналогично, значение MAXDOP базы данных применяется ко всей базе данных, если вы не задали его на уровне запроса.
Чтобы правильно настроить значение MAXDOP, сначала установите базовый уровень. Это означает наличие надлежащего бенчмарка, который можно запускать с другими настройками. Обязательно повторно контролируйте производительность этих запусков при изменении настроек, пока не получите значение MAXDOP, которое лучше всего подходит для вашей рабочей нагрузки.