При администрировании крупных баз данных «1С:Предприятие» (размером в сотни гигабайт) на совмещенных серверах часто возникает классическая проблема: после выполнения тяжелых регламентных операций или ночных расчетов вся выделенная СУБД оперативная память оказывается занятой, а сама система 1С начинает заметно притормаживать. При этом администраторы замечают, что SQL Server не спешит возвращать память операционной системе, даже когда активные вычисления уже завершены.
В этой статье мы подробно разберем, почему возникает этот эффект, как работает управление памятью в MS SQL Server, почему принудительная очистка кэша является опасным «костылем» и как правильно настроить сервер, чтобы пользователи не страдали от падения производительности.
Прежде всего проанализируем базовый принцип работы СУБД. Поведение, при котором SQL Server захватывает выделенный ему объем ОЗУ и удерживает его, является абсолютно штатным и заложенным в архитектуру. СУБД работает по принципу минимизации обращений к медленной дисковой подсистеме. Когда данные считываются с диска для выполнения запроса, они помещаются в оперативную память — в область, называемую Buffer Pool (буферный пул).
Даже когда ваш тяжелый расчет в 1С завершился, SQL Server оставляет эти данные в памяти на случай, если они понадобятся снова. СУБД не освобождает память обратно операционной системе, поскольку процесс выделения памяти у ОС — это крайне дорогая операция. SQL Server берет на себя роль внутреннего диспетчера памяти: он сам решает, какие страницы кэша хранить, а какие замещать новыми при поступлении других запросов. Пытаться заставить SQL Server принудительно «отдавать» память ОС в течение дня — это грубейшая ошибка администрирования.
Выясним истинную причину, почему после тяжелого расчета работа 1С начинает замедляться. Многие думают, что это происходит из-за «забитой» памяти, но корень проблемы кроется в эффекте вымывания кэша.
Во время работы тяжелого алгоритма (например, закрытия месяца, расчета себестоимости или распределения взаиморасчетов) СУБД вынуждена прочитать с диска гигантские массивы исторических данных, которые редко используются в повседневной оперативной работе. Эти данные загружаются в буферный пул, вытесняя оттуда «горячий» кэш: таблицы метаданных конфигурации 1С, кэш планов запросов, часто используемые справочники номенклатуры, контрагентов и актуальные остатки по регистрам.
Когда расчет заканчивается, память СУБД заполнена на 100%, но забита она «мертвыми» историческими данными, которые больше никому не нужны. Обычные пользователи начинают выполнять свои рутинные операции (выписка накладных, создание заказов) и сталкиваются с тем, что их «горячих» данных в оперативной памяти больше нет. SQL Server вынужден считывать их заново с физических дисков, параллельно выгружая старые данные из ОЗУ. Это приводит к резкому падению показателя Page Life Expectancy (время жизни страницы в памяти) и ощутимому замедлению работы интерфейса 1С.
Если сервер 1С и MS SQL Server установлены на одной физической машине с 400 ГБ ОЗУ, неверное распределение ресурсов может парализовать систему. Рассмотрим, как распределяется память в такой конфигурации:
max server memory) на уровне 350 ГБ, то для операционной системы, процессов сервера 1С (rphost), кэша самой ОС и других служб остается всего 50 ГБ.pagefile.sys) на диске.Поэтому на совмещенных серверах крайне важно оставлять достаточный запас оперативной памяти «ненасытным» процессам 1С. В среднем для базы объемом 400 ГБ на совмещенном сервере рекомендуется выделять под SQL Server не более 70–75% от общего объема ОЗУ (в нашем случае — около 280–300 ГБ), чтобы гарантировать стабильность работы ОС и службы 1С.
Чтобы прекратить гадание на кофейной гуще, необходимо выполнить профессиональную диагностику СУБД с помощью анализа системных ожиданий. Воспользуемся проверенной методикой очистки и сбора статистик перед началом замедления работы системы.
Сначала мы сбрасываем накопленную системную статистику ожиданий с помощью следующей команды:
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
GO
После того как расчет прошел и пользователи начали жаловаться на тормоза, выполним скрипт для выявления лидирующих типов ожиданий в системе:
WITH [WaitingTasks] AS
(SELECT
[wait_type],
[wait_time_ms] / 1000.0 AS [WaitS],
([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
[signal_wait_time_ms] / 1000.0 AS [SignalS],
[waiting_tasks_count] AS [WaitCount],
ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
FROM sys.dm_os_wait_stats
WHERE [wait_type] NOT IN (
N'CLR_SEMAPHORE', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE',
N'SQLTRACE_BUFFER_FLUSH', N'SLEEP_TASK', N'SLEEP_SYSTEMTASK',
N'SIGN_LOG', N'XE_TIMER_EVENT', N'XE_DISPATCHER_WAIT',
N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
N'DIRTY_PAGE_TABLE_RELEASE', N'BROKER_TO_FLUSH', N'BROKER_TASK_STOP'
)
)
SELECT
[W1].[wait_type] AS [WaitType],
CAST ([W1].[WaitS] AS DECIMAL (16, 2)) AS [Wait_S],
CAST ([W1].[ResourceS] AS DECIMAL (16, 2)) AS [Resource_S],
CAST ([W1].[SignalS] AS DECIMAL (16, 2)) AS [Signal_S],
[W1].[WaitCount] AS [WaitCount]
FROM [WaitingTasks] AS [W1]
WHERE [W1].[RowNum] <= 15;
Проанализируем полученные результаты:
tempdb при активном использовании временных таблиц.Поскольку алгоритмы 1С активно используют временные таблицы (конструкция ПОМЕСТИТЬ в запросах), база данных tempdb испытывает колоссальную нагрузку. При некорректной настройке tempdb возникает бутылочное горлышко на уровне распределения страниц (allocation bottleneck). Мы рекомендуем выполнить следующие настройки для базы tempdb:
tempdb. Общая рекомендация для SQL Server 2012 — количество файлов должно быть равно количеству логических ядер процессора, но не более 8 файлов (если конкуренция не сохраняется). Это позволит распараллелить запись.tempdb в мегабайтах (например, по 256 или 512 МБ), но ни в коем случае не в процентах.tempdb только на самых производительных твердотельных накопителях (SSD/NVMe).Некоторые администраторы предлагают обходные пути борьбы с зависаниями, такие как запуск заданий по расписанию для принудительного изменения лимитов памяти или регулярная очистка процедурного кэша командами DBCC FREESYSTEMCACHE или DBCC FREEPROCCACHE.
Разберем, почему эти методы опасны:
Для качественного устранения проблемы замедления работы системы после выполнения сложных расчетов выполним следующий комплекс мер:
Ограничим аппетиты SQL Server, чтобы гарантировать стабильную работу операционной системы и рабочих процессов 1С. Для сервера с 400 ГБ ОЗУ установим верхний лимит для SQL Server на уровне 280–300 ГБ. Сделать это можно через СУБД или выполнив скрипт:
EXEC sys.sp_configure N'show advanced options', N'1';
RECONFIGURE WITH OVERRIDE;
GO
EXEC sys.sp_configure N'max server memory (MB)', N'286720'; -- Установка лимита в 280 ГБ
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sys.sp_configure N'show advanced options', N'0';
RECONFIGURE WITH OVERRIDE;
GO
Для баз данных 1С высокая степень параллелизма часто вредит производительности из-за накладных расходов на координацию потоков. В настройках сервера SQL Server рекомендуется установить параметр Max Degree of Parallelism (MAXDOP) в значение 1 (или ограничить его небольшим количеством ядер, например, от 2 до 4, в зависимости от архитектуры процессоров).
После завершения масштабных ночных расчетов, которые вносят миллионы изменений в таблицы базы данных, планы запросов СУБД мгновенно устаревают. Сразу после окончания тяжелого алгоритма настройте регламентное задание на обновление статистик. Это позволит оптимизатору SQL Server строить корректные планы выполнения запросов на основе свежих данных, избегая неэффективных соединений таблиц.
Никакая настройка железа не спасет систему, если сам алгоритм расчета написан неоптимально. Постарайтесь разбить один гигантский транзакционный расчет на более мелкие порции (пакеты данных). Проанализируйте использование временных таблиц в коде: своевременно уничтожайте их с помощью конструкции УНИЧТОЖИТЬ ВременнаяТаблица в запросах 1С, как только надобность в них отпала, чтобы не засорять базу tempdb.