Работа системы «1С:Предприятие» в связке с MS SQL Server накладывает особые требования к администрированию баз данных. Из-за специфики платформы 1С — генерации огромного количества динамических запросов, частого создания и удаления временных таблиц, интенсивной записи данных — индексы СУБД быстро фрагментируются, а статистика распределения данных устаревает. Это приводит к резкому снижению производительности: пользователи начинают жаловаться на «зависания» отчетов, долгую проведение документов и общую медлительность системы.
Для решения этих проблем администраторы СУБД используют регламентные операции. В этой статье мы подробно разберем, какие операции критически важны для оптимизации 1С, научимся правильно настраивать обслуживание индексов и статистик, а также развенчаем популярные мифы администрирования, способные полностью уничтожить производительность вашей СУБД.
В процессе работы пользователи постоянно добавляют, изменяют и удаляют записи в базе данных 1С. Это приводит к физическому разделению и перемешиванию страниц данных в файлах СУБД. Возникает фрагментация индексов. Когда фрагментация высока, SQL Server тратит гораздо больше времени и дисковых операций чтения на поиск нужной информации.
Существует два основных способа борьбы с фрагментацией индексов:
Проводить перестроение абсолютно всех индексов каждую ночь — нерационально, особенно на крупных базах данных объемом в сотни гигабайт. Проанализируем более эффективный, адаптивный подход: выполнять обслуживание на основе текущего процента фрагментации конкретного индекса.
Рассмотрим специальный T-SQL скрипт, который автоматически анализирует состояние каждого индекса в базе данных 1С и принимает оптимальное решение:
REORGANIZE).REBUILD).Скрипт также проверяет редакцию вашего MS SQL Server: если используется версия Enterprise или Developer, перестроение крупных индексов будет автоматически запускаться в режиме «онлайн» (ONLINE = ON), что минимизирует блокировки для пользователей.
USE ИмяБазы;
DECLARE @SQL NVARCHAR(MAX)
DECLARE cur CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR
SELECT
'ALTER INDEX [' + i.name + N'] ON [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] ' +
CASE
WHEN s.avg_fragmentation_in_percent > 30
THEN 'REBUILD WITH (SORT_IN_TEMPDB = ON' -- Enterprise, Developer
+ CASE
WHEN SERVERPROPERTY('EditionID') IN (1804890536, -2117995310)
THEN ', ONLINE = ON'
ELSE ''
END + ')'
ELSE 'REORGANIZE'
END + ';'
FROM (
SELECT
s.[object_id],
s.index_id,
avg_fragmentation_in_percent = MAX(s.avg_fragmentation_in_percent)
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') s
WHERE s.page_count > 128 -- Игнорируем мелкие таблицы (менее 1 МБ)
AND s.index_id > 0 -- Исключаем кучи (HEAP)
AND s.avg_fragmentation_in_percent > 5
GROUP BY s.[object_id], s.index_id
) s
JOIN sys.indexes i WITH(NOLOCK)
ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
JOIN sys.objects o WITH(NOLOCK)
ON o.[object_id] = s.[object_id]
OPEN cur
FETCH NEXT FROM cur INTO @SQL
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sys.sp_executesql @SQL
FETCH NEXT FROM cur INTO @SQL
END
CLOSE cur
DEALLOCATE cur
В кругах начинающих администраторов часто бытует мнение, что базу данных необходимо регулярно сжимать (выполнять команду DBCC SHRINKDATABASE или DBCC SHRINKFILE), чтобы освобождать место на жестких дисках. Это глубокое и крайне опасное заблуждение!
Выясним причину, почему регулярный регламентный Shrink наносит огромный вред производительности 1С:
Золотое правило DBA: Операцию сжатия базы данных можно выполнять только вручную и исключительно как разовое мероприятие. Например, если вы перенесли терабайт исторических данных во внешнюю архивную базу и точно уверены, что этот объем информации больше никогда не вернется. Сразу после завершения процедуры ручного сжатия обязательно выполните полное перестроение индексов (REBUILD), чтобы восстановить их структуру.
Статистика в MS SQL Server — это метаданные, описывающие распределение данных в таблицах и индексах. Опираясь на эту статистику, оптимизатор запросов СУБД строит планы выполнения — решает, использовать ли индекс, в каком порядке соединять таблицы и т.д. Если статистика устарела, SQL Server выберет неэффективный план, и простейший отчет в 1С вместо секунды будет выполняться часами.
При настройке обновления статистик важно знать ключевой технический нюанс: если для индекса было выполнено полное перестроение (REBUILD), статистика по ключевым полям этого индекса обновляется автоматически с 100% выборкой (Fullscan).
Поэтому правильный алгоритм ночных регламентов выглядит следующим образом:
REBUILD, либо сделать выборочное обновление статистик с помощью команды UPDATE STATISTICS.Также для баз данных 1С мы настоятельно рекомендуем включить опцию асинхронного обновления статистик. Выполнить это можно следующим запросом:
ALTER DATABASE ИмяВашейБазы SET AUTO_UPDATE_STATISTICS_ASYNC ON;
Эта настройка гарантирует, что если статистика по какой-то таблице устареет прямо в разгар рабочего дня, SQL Server не заблокирует пользовательский запрос для немедленного пересчета метаданных, а выполнит его по старому плану, параллельно запустив фоновый пересчет в отдельном потоке.
Многих волнует вопрос: обязательно ли выгонять всех пользователей из 1С на время ночного обслуживания? Ответ напрямую зависит от используемой вами редакции MS SQL Server:
ONLINE = ON) недоступна. Любой запуск операции REBUILD заблокирует таблицы монопольно. Если в системе останутся работающие пользователи, они получат ошибки блокировок. Обслуживание на Standard-редакции необходимо проводить строго в технологическое окно, ночью или по выходным.Помимо индексов и статистик, для стабильной работы 1С:Предприятия необходимо правильно сконфигурировать системные параметры MS SQL Server. Разберем три важнейшие настройки:
Параметр MAXDOP (Max Degree of Parallelism) определяет максимальное количество ядер процессора, которое СУБД может задействовать для выполнения одного запроса. По умолчанию SQL Server пытается распараллелить тяжелые запросы на все доступные ядра.
Для архитектуры 1С, генерирующей огромное число мелких параллельных запросов, это губительно. Запросы начинают конкурировать друг с другом, вызывая взаимные блокировки потоков (ожидания типа CXPACKET). Рекомендуется жестко ограничить этот параметр:
MAXDOP = 1 (в редких случаях для высоконагруженных многопроцессорных систем допускается значение 2 или 4).Cost Threshold for Parallelism (порог стоимости для параллелизма) увеличьте с дефолтных 5 до 50.Платформа 1С непрерывно создает миллионы временных таблиц, задействуя базу tempdb. Чтобы избежать узкого горлышка производительности:
tempdb должно быть строго равно числу логических ядер процессора вашего сервера (но не более 8 файлов, если иное не рекомендовано вендором).После того как вы выполнили глобальную реиндексацию и обновили статистики, старые планы запросов, которые SQL Server хранит в оперативной памяти (процедурном кэше), могут оказаться неактуальными. СУБД может продолжить использовать старые неоптимальные планы.
Чтобы заставить SQL Server перестроить планы выполнения на основе свежих данных, добавьте в самый конец вашего ночного сценария обслуживания следующую команду:
DBCC FREEPROCCACHE;
Применяя эти практики и регулярно запуская оптимизацию СУБД по умным скриптам вместо стандартных планов обслуживания «в лоб», мы добьемся максимального быстродействия и стабильности работы 1С:Предприятия даже при экстремальных нагрузках.