Настройка MS SQL для 1С: MAXDOP, порог параллелизма и память

SQL Server из коробки настроен универсально — то есть ни под что конкретно. Для 1С это означает, что половина параметров работает против вас: параллелизм включается там, где не нужен, память забирается вся до последнего гигабайта, а файлы растут кусочками по мегабайту. Разберём короткий список изменений, который занимает двадцать минут и обычно даёт больше, чем апгрейд процессора.

По умолчанию max server memory установлен в максимум — примерно два петабайта. Практически это означает «забирай сколько сможешь».

SQL Server так и делает: постепенно занимает всю доступную память под буферный кэш и не спешит её отдавать. Если на той же машине живёт сервер 1С, ему в какой-то момент перестаёт хватать, и начинается вытеснение в файл подкачки.

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

Считаем так. От общего объёма памяти вычитаем: 4 ГБ на операционную систему (на больших серверах — 8), потребность сервера 1С (число rphost, умноженное на ожидаемый размер), запас на прочие службы. Остаток отдаём SQL Server.

EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'max server memory (MB)', 43008; RECONFIGURE;

Если SQL Server стоит на отдельной машине, схема проще: оставляем ОС 10–15 % и всё остальное отдаём СУБД.

Параметр min server memory трогать обычно не нужно. Он не резервирует память заранее, а только не даёт отдавать её обратно ниже указанного порога.

Максимальная степень параллелизма определяет, на сколько потоков SQL Server может разложить один запрос.

По умолчанию стоит 0 — «использовать все ядра». Для аналитической нагрузки это разумно. Для 1С — обычно нет.

Причина в характере нагрузки. Учётная система выполняет много коротких операций: проведение документа, чтение остатка, запись движения. Разложить такую операцию на 16 потоков невозможно с пользой — накладные расходы на распараллеливание и последующую сборку результата съедают весь выигрыш. Зато потоки занимают планировщики, и другим запросам приходится ждать.

Отсюда исторически популярная рекомендация «для 1С ставьте MAXDOP = 1». Она рабочая, но грубая: она полностью запрещает параллелизм, включая тяжёлые отчёты, которым он реально помогает.

Наш подход: MAXDOP ставим равным числу физических ядер одного узла NUMA, но не больше 8. На типичном сервере с двумя процессорами по 10 ядер это даёт 8. На односокетной машине с 8 ядрами — 8. На маленьком сервере с 4 ядрами — 4.

EXEC sp_configure 'max degree of parallelism', 8; RECONFIGURE;

А ограничивать параллелизм для мелких запросов мы предпочитаем не через MAXDOP, а через порог стоимости — это следующий параметр, и он важнее.

Оговорка: если у вас уже стоит MAXDOP = 1 и всё работает хорошо, не спешите менять. Сначала замерьте на копии.

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

Значение по умолчанию — 5. Оно установлено в 1998 году, исходя из производительности тогдашнего железа, и с тех пор не менялось.

Что это означает на практике: почти любой запрос 1С сложнее выборки одной строки превышает порог 5 и становится кандидатом на параллельное выполнение. Сервер начинает распараллеливать операции, которые выполнились бы за 20 миллисекунд в один поток.

Симптом — высокая доля ожиданий CXPACKET и CXCONSUMER при том, что тяжёлых отчётов почти не строят.

Рабочее значение для баз 1С — от 50. Мы обычно ставим 50 и смотрим на изменение картины ожиданий:

EXEC sp_configure 'cost threshold for parallelism', 50; RECONFIGURE;

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

Связка «MAXDOP = 8 плюс порог 50» на нашей практике работает лучше, чем «MAXDOP = 1 и порог по умолчанию», хотя вторая рекомендация встречается чаще.

Проверить эффект просто: сбросить статистику ожиданий, поработать день, посмотреть, ушёл ли CXPACKET из топа.

Настройки автоувеличения по умолчанию порочны для боевой базы.

Файл данных растёт по 64 МБ, журнал — на 10 %. Первое означает частые операции роста на большой базе, второе — экспоненциальный рост шага у журнала.

Каждое увеличение файла — это пауза, в течение которой SQL Server ждёт, пока операционная система выделит место. Для файла данных эту паузу можно почти убрать, для журнала — нельзя.

Что делаем:

ALTER DATABASE [buh_prod] MODIFY FILE (NAME = 'buh_prod', FILEGROWTH = 1024MB);
ALTER DATABASE [buh_prod] MODIFY FILE (NAME = 'buh_prod_log', FILEGROWTH = 512MB);

И главное — заранее задаём файлам размер с запасом на полгода-год вперёд, чтобы рост вообще не происходил в рабочее время.

Мгновенная инициализация файлов. Без неё при увеличении файла данных операционная система заполняет новое пространство нулями. На гигабайте это ощутимая пауза.

Включается выдачей права «Выполнение задач по обслуживанию томов» (SE_MANAGE_VOLUME_NAME) учётной записи службы SQL Server. Право даётся через локальную политику безопасности, после чего службу надо перезапустить.

Проверить, работает ли:

SELECT servicename, instant_file_initialization_enabled
FROM sys.dm_server_services;

На журнал транзакций это не распространяется — он обнуляется всегда по требованиям целостности. Ещё один аргумент за то, чтобы задать журналу нормальный размер сразу.

Право «Блокировка страниц в памяти» не даёт операционной системе вытеснять буферный кэш SQL Server в файл подкачки.

Зачем это нужно: Windows при нехватке памяти может начать выгружать рабочий набор процессов, и SQL Server, занявший десятки гигабайт, оказывается первым кандидатом. Результат — обвальное падение производительности, при котором счётчики процессора и диска выглядят нормально, а всё стоит.

Выдаётся через secpol.msc, раздел назначения прав пользователя, учётной записи службы SQL Server. Требуется перезапуск службы.

Важное условие: включать только вместе с корректно заданным max server memory. Иначе SQL Server заберёт всю память и заблокирует её, а операционной системе останется столько, сколько останется — и тогда встанет уже она.

Проверка, что право применилось:

SELECT sql_memory_model_desc FROM sys.dm_os_sys_info;
-- LOCK_PAGES или LARGE_PAGES означает, что работает

Из прочих настроек уровня инстанса, которые мы трогаем:

• Оптимизация для нерегулярных рабочих нагрузок — включаем. Уменьшает расход памяти на кэш планов, которых в 1С генерируется много и большинство используется один раз.
• Backup compression default — включаем.
• Приоритет SQL Server и Fiber mode — не трогаем никогда, вопреки советам из интернета.

EXEC sp_configure 'optimize for ad hoc workloads', 1; RECONFIGURE;
EXEC sp_configure 'backup compression default', 1; RECONFIGURE;

Короткий список вредных советов, которые встречаются регулярно.

Не ставьте флаг трассировки 1117 и 1118 на новых версиях. Начиная с SQL Server 2016 их поведение включено по умолчанию для tempdb, и ручное включение ничего не даёт.

Не включайте «Boost SQL Server priority». Параметр помечен как устаревший, может привести к нехватке ресурсов у системных процессов и не даёт измеримого выигрыша.

Не отключайте автообновление статистики. Совет встречается под соусом «чтобы не тормозило в рабочее время». Результат — планы деградируют между ночными обновлениями.

Не ставьте recovery model в SIMPLE ради производительности. Выигрыш микроскопический, потеря возможности восстановиться на точку времени — огромная.

Не создавайте индексы по советам missing index DMV без разбора. Об этом отдельный разговор, но коротко: платформа 1С сама управляет индексами, и добавленный вручную индекс может конфликтовать с обновлениями конфигурации.

Не отключайте параллелизм полностью на сервере, где строят тяжёлые отчёты. Правильный инструмент здесь — порог стоимости, а не MAXDOP = 1.

Порядок применения на новом или неразобранном инстансе.

1. Задать max server memory по расчёту с учётом того, живёт ли рядом сервер 1С.
2. Установить MAXDOP по числу ядер узла NUMA, не больше 8.
3. Поднять cost threshold for parallelism до 50.
4. Включить optimize for ad hoc workloads и сжатие бэкапов.
5. Выдать право «Выполнение задач по обслуживанию томов» учётке службы.
6. Выдать право «Блокировка страниц в памяти» — только после пункта 1.
7. Задать файлам базы размер с запасом и фиксированный шаг роста в мегабайтах.
8. Проверить модель восстановления: FULL плюс настроенный бэкап журнала.
9. Проверить коллацию: Cyrillic_General_CI_AS.
10. Перезапустить службу и снять базовые замеры для сравнения.

Пункт десять обязателен. Без замеров до и после вы не сможете ни доказать эффект, ни откатиться осознанно, если что-то станет хуже.

Из нашей практики: на неразобранном инстансе этот список даёт ускорение типовых операций на 20–40 %. Не в разы — в разы дают уже другие вещи вроде статистики и разведения окон обслуживания. Но двадцать минут работы того стоят, и это фундамент, на котором дальнейшая оптимизация вообще имеет смысл.

Ставить ли MAXDOP = 1 для 1С?

Это рабочая, но грубая рекомендация: она запрещает параллелизм и тяжёлым отчётам, которым он помогает. Мы предпочитаем MAXDOP по числу ядер узла NUMA (но не больше 8) в связке с поднятым до 50 порогом стоимости параллелизма — короткие операции остаются однопоточными, тяжёлые получают параллельный план.

Почему нельзя оставить max server memory по умолчанию?

По умолчанию SQL Server заберёт практически всю память машины и не будет спешить её отдавать. Если рядом работает сервер 1С, ему перестанет хватать, начнётся вытеснение в файл подкачки. Характерный симптом — постепенное замедление в течение недели после перезагрузки.

Что даёт мгновенная инициализация файлов?

Убирает паузу на обнуление нового пространства при увеличении файла данных — на гигабайте это заметная задержка. Включается выдачей права «Выполнение задач по обслуживанию томов» учётной записи службы. На журнал транзакций не распространяется, он обнуляется всегда.

Опасно ли включать Lock Pages in Memory?

Опасно включать его при незаданном max server memory: SQL Server заберёт и заблокирует всю память, и встанет уже операционная система. В связке с корректно рассчитанным лимитом памяти это полезная настройка, защищающая буферный кэш от вытеснения в подкачку.