Настройка параметра Max degree of parallelism при выполнении реструктуризации информационной базы

Параметр max degree of parallelism – дополнительная опция СУБД Microsoft SQL Server, которая определяет максимальное число процессоров, применяемых при выполнении одного запроса. Данная статья описывает сценарий его настройки для оптимизации выполнения реструктуризации информационной базы 1С:Предприятие с помощью нового механизма, который доступен, начиная  с версии Платформы 8.3.11.

Немного теории

На многопроцессорном компьютере при выполнении инструкции (запроса) Microsoft SQL Server  может использовать параллелизм, или, говоря простым языком – выполнять один запрос в несколько потоков. Факторы, влияющие на определение степени параллелизма (количества потоков выполнения), приведены на официальной странице Microsoft: https://technet.microsoft.com/ru-ru/library/ms188611(v=sql.105).aspx.

Одним из них является дополнительный параметр MS SQL Server Max degree of parallelism. Описание приведено в документации Microsoft https://technet.microsoft.com/ru-ru/library/ms181007(v=sql.105).aspx. Согласно нему, "параметр max degree of parallelism используется для ограничения числа процессоров, применяемых в планах параллельного выполнения. Чтобы разрешить серверу определять максимальную степень параллелизма, установите 0 в качестве значения данного параметра, то есть значение по умолчанию. Чтобы отключить создание параллельных планов, присвойте параметру max degree of parallelism значение 1.Параметр максимального значения степени параллелизма определяется выпуском SQL Server, типом ЦП и операционной системой. Если указано значение, превышающее количество доступных процессоров, используется фактическое число доступных процессоров. Если у компьютера только один процессор, то значение параметра max degree of parallelism учитываться не будет."

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

Практика применения параллелизма для ускорения выполнения реструктуризации базы данных

В общем случае мы рекомендуем устанавливать значение параметра max degree of parallelism равным 1, то есть запрещать Database Engine параллельное выполнение запросов. Почему?

• основная причина - исключить сценарий, при которой один длительный запрос с неэффективным планом выполнения создаст за счет параллелизма нагрузку на CPU, не позволяющую другим транзакциям выполняться с обычной для системы скоростью. Например, легко можно представить себе ситуацию, в которой пользователь захочет сформировать отчет, добавив в него "через точку" поле составного типа. В результате запрос, автоматически сгенерированный платформой 1С:Предприятие, особенно в случае добавления к нему ограничений по правам доступа на уровне записей (RLS), может оказаться очень длинным, содержать большое количество соединений и при этом хорошо поддаваться параллельной обработке, вызвав рост нгагрузки на систему, что скажется на ее работе в целом;
• поскольку запрос может иногда «распараллеливаться», а иногда – нет, время его выполнения будет варьироваться, что может мешать работе пользователей;
• параллелизм может вызывать негативные последствия, наличие и влияние которых необходимо специально оценивать. Прежде всего это – ожидания при синхронизации потоков выполнения, когда координатор потоков (поток 0) по тем или иным причинам при ждет передачи данных от «отстающих» потоков (вид ожиданий MS SQL Server CXPACKET).  Оценить наличие и величину этого ожидания можно, использовав данные DMV sys.dm_os_wait_stats, выполнив подобный запрос:

with waits
as
(
select
       wait_type,
       wait_time_ms,
       waiting_tasks_count
from sys.dm_os_wait_stats
)
select
       waits.wait_type Wait_type,
       waits.waiting_tasks_count Waiting_tasks,
       waits.wait_time_ms Wait_time,
       100 * waits.wait_time_ms / Totals.Total Percentage
from waits
inner join
             (
             select
                    sum (waits.wait_time_ms) Total
             from waits
             ) Totals
on 1=1
where waits.wait_type = N'CXPACKET'  

Кроме того, при слабой пропускной способности дисковой подсистемы параллелизм может приводить к замедлениям при выполнении запросов ввиду наличия ожиданий ввода-вывода, оценить которые можно, изменив условие в запросе выше на where waits.wait_type like N'%IO%'.

Крайне редки, но возможны появления взаимоблокировок потоков выполнения друг другом (intra-query parallelism).

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

Пример того, когда параллельное выполнение запросов может принести ощутимый эффект – реструктуризация информационной базы, и в особенности - с использованием нового её механизма, доступного с версии 8.3.11 платформы 1С: Предприятие .

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

О преимуществах и основных отличиях нового механизма реструктуризации от «традиционного»  подробнее можно прочитать в статье https://wonderland.v8.1c.ru/blog/optimizatsiya-restrukturizatsii-bazy-dannykh/, но главное – это, пожалуй, то, что максимально возможный объем необходимых к выполнению операций с данными делегирован на уровень СУБД, возможности которой задействованы максимально.

Добавление и удаление колонок, добавление, удаление и изменение индексов таблиц, обновление записей существующих таблиц и перенос данных в новые таблицы с помощью единых запросов UPDATE или INSERT  - это именно те операции, которые выполняются при реструктуризации, и именно они как нельзя лучше подходят для параллельной обработки. Почти всегда эти операции выполняются над большими объемами данных, а сами запросы ввиду особенностей выполняемых действий имеют достаточно простой синтаксис, что позволяет Database Engine при наличии доступных процессорных ресурсов почти всегда предпочитать параллельный план выполнения последовательному и получать от этого существенный  оптимизационный эффект.

Для того, чтобы это стало возможным, рекомендованное значение параметра max degree of parallelism - 1 - должно быть изменено.  Рекомендация по определению его значения для многопроцессорной системы обычно звучит как «общее количество ядер процессора / 2». Значение 0 предоставляет СУБД возможность самостоятельного определения максимально возможного количества потоков исходя из доступности ресурсов, и применимо к выполнению реструктуризации мы рекомендуем использовать именно его.

Изменить значение параметра можно с использованием SSMS двумя способами:

• С помощью системной хранимой процедуры sp_configure, выполнив в среде Management Studio следующий код:

            sp_configure 'show advanced options', 1;
            go
            reconfigure;
            go
            sp_configure 'max degree of parallelism', 0;
            go
            reconfigure;
            go

• В пользовательском режиме. Для этого необходимо открыть свойства сервера Microsoft  SQL на закладке Advanced в среде SSMS и изменить значение на желаемое :

Изменения вступят в силу сразу без необходимости перезапуска сервера баз данных.

После завершения реструктуризации мы рекомендуем в общем случае вернуть значение в исходное состояние. Конечно же, в процессе ее выполнения стоит выполнять мониторинг ожиданий СУБД, о котором сказано выше, а также производительности дисковой подсистемы при помощи, например, Performance Monitor.

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