Обслуживание баз 1С: реиндексация, статистика и сжатие — что делать и как часто

База, которую не обслуживают, деградирует постепенно и незаметно: сегодня отчёт строится на секунду дольше, через год — на минуту. Причём деградация не связана с ростом объёма напрямую — она про фрагментацию и устаревшую статистику. Разберём, что делать, с какой периодичностью, чем отличается перестроение от реорганизации и какие «обязательные» процедуры на самом деле вредны.

Три независимые вещи, и лечатся они по-разному.

Фрагментация индексов. Данные в индексе хранятся упорядоченно по страницам. Вставки и обновления нарушают порядок: страницы разделяются, между ними появляются пустоты, физический порядок перестаёт совпадать с логическим. Чтобы прочитать те же строки, СУБД приходится обращаться к большему числу страниц.

Устаревшая статистика. Оптимизатор строит план на основе оценок: сколько строк вернёт условие, какое соединение выгоднее. Оценки берутся из статистики. Если статистика описывает базу трёхмесячной давности, планы будут строиться под несуществующую реальность.

Логические ошибки в структуре ИБ. Битые ссылки, несоответствие итогов движениям, повреждённые таблицы. Накапливаются от аварийных завершений, ошибок обменов и сбоев оборудования.

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

Два разных инструмента, и выбирать между ними надо по порогу фрагментации, а не по привычке.

Разница принципиальная. REORGANIZE дефрагментирует листовой уровень на месте, работает всегда онлайн, может быть прервана без последствий и не обновляет статистику. REBUILD создаёт индекс заново, требует места, в редакции Standard блокирует таблицу, зато даёт идеальный результат и попутно обновляет статистику с полным сканированием.

Посмотреть фактическую фрагментацию:

SELECT OBJECT_NAME(ips.object_id) AS tbl, i.name AS idx,
       ips.avg_fragmentation_in_percent AS frag, ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON i.object_id = ips.object_id AND i.index_id = ips.index_id
WHERE ips.page_count > 1000 AND ips.avg_fragmentation_in_percent > 5
ORDER BY ips.avg_fragmentation_in_percent DESC;

Обратите внимание на условие page_count > 1000. Мелкие индексы дефрагментировать бессмысленно: они целиком помещаются в несколько страниц, и показатель фрагментации для них ничего не значит. Типовой план обслуживания из мастера этого не учитывает и честно перестраивает тысячи крохотных индексов, тратя часы впустую.

Именно поэтому мы почти всегда заменяем мастер планов на скрипты Оли Халленгрена — они принимают решение по порогу и по размеру, а не перестраивают всё подряд.

Обновление статистики стоит в разы дешевле перестроения индексов, а на планы влияет сильнее.

Автообновление в SQL Server включено по умолчанию, но срабатывает по порогу изменений, который для больших таблиц слишком высок: раньше это было 20 % строк, что для регистра на 30 миллионов записей означает 6 миллионов изменений до первого обновления.

Поэтому статистику обновляем принудительно. Ключевой момент — с полным сканированием:

UPDATE STATISTICS [dbo].[_AccumRg1234] WITH FULLSCAN;
-- или по всей базе
EXEC sp_updatestats;

Разница между FULLSCAN и выборочным обновлением для баз 1С существенна. Структура таблиц платформы такова, что выборка по умолчанию часто даёт нерепрезентативную картину распределения, и оптимизатор ошибается в оценках на порядки.

Отдельно стоит включить асинхронное автообновление статистики. Тогда запрос, который обнаружил устаревшую статистику, не будет ждать её пересчёта, а выполнится по старой и запустит обновление в фоне:

ALTER DATABASE [buh_prod] SET AUTO_UPDATE_STATISTICS_ASYNC ON;

Это убирает характерные «залипания» первого запроса после массовой загрузки данных.

Частота: ежедневно для базы с активным вводом. Операция лёгкая, идёт минуты, и это самое выгодное обслуживание из всех по соотношению эффекта к затратам.

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

Запускается из конфигуратора или утилитой chdbfl.exe для файловых баз. Требует монопольного доступа.

Что делает:

• проверяет и исправляет битые ссылки на несуществующие объекты;
• пересчитывает итоги регистров, если они разошлись с движениями;
• реиндексирует таблицы средствами платформы;
• проверяет логическую целостность.

Здесь важна осторожность. Режим «удалять объекты» при исправлении битых ссылок делает ровно то, что написано, — удаляет. Если причина битых ссылок в неудачном обмене, вы потеряете данные, которые можно было бы восстановить.

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

Частота: планово — раз в квартал. Внепланово — после любого некорректного завершения работы сервера, после сбоя дисковой подсистемы, при появлении необъяснимых ошибок в интерфейсе.

Ежемесячно и чаще запускать не нужно: операция долгая, требует останова, а логические ошибки в здоровой системе не появляются сами по себе.

Тема, вокруг которой много путаницы, потому что словом «сжатие» называют три разные вещи.

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

ALTER TABLE [dbo].[_AccumRg1234] REBUILD
  WITH (DATA_COMPRESSION = PAGE, ONLINE = ON);

Замечание: доступность ONLINE = ON зависит от редакции. В Standard до определённых версий перестроение с компрессией блокирует таблицу.

Сжатие файла базы (SHRINK). Уменьшает физический файл, возвращая место операционной системе. Вот это — почти всегда вредно.

Shrink перемещает страницы в конец файла, чтобы освободить хвост, и в процессе доводит фрагментацию индексов практически до максимума. То есть вы тратите часы на сжатие, а потом ещё часы на перестроение индексов, которое снова раздует файл.

Единственный оправданный сценарий: разовое сжатие после удаления действительно большого объёма данных, с обязательным последующим перестроением индексов. В регулярном плане обслуживания SHRINK быть не должен никогда.

Сжатие бэкапов. Всегда включать. Уменьшает и объём хранения, и время создания бэкапа, потому что записывать надо меньше.

То, что мы ставим клиентам с базой до 200 ГБ и рабочим днём с 9 до 19.

Три правила по этому расписанию.

Первое: окна не пересекаются. Бэкап заканчивается до старта обновления статистики, статистика — до индексов.

Второе: CHECKDB не пропускать. Это единственная проверка физической целостности базы, и она обнаруживает повреждения от проблем с диском раньше, чем они станут заметны в работе. Найденное повреждение при живых бэкапах — неприятность; оно же, обнаруженное через полгода, когда все бэкапы уже содержат повреждённые страницы, — катастрофа.

Третье: всё это должно писать результат в мониторинг. Молча не отработавший план обслуживания — обычное дело, и заметить это без проверки невозможно.

Клиент — сеть медицинских кабинетов, 24 рабочих места, база бухгалтерии на MS SQL, объём 71 ГБ.

Обратились с жалобой на общую медлительность, которая усилилась за последний месяц. Внутренний администратор объяснял это ростом базы и просил бюджет на новый сервер.

Проверка фрагментации дала неожиданно ровную картину: индексы были в порядке, максимум 12 %. План обслуживания работал.

А вот статистика не обновлялась 14 месяцев. Ни разу с момента переноса базы на текущий сервер.

Причина обнаружилась в самом плане обслуживания: он был собран мастером, и в нём стояла задача «Перестроение индекса» — которая обновляет статистику только по перестроенным индексам. А поскольку фрагментация редко превышала порог, перестроение почти не срабатывало, и статистика оставалась замороженной.

Запустили обновление статистики с FULLSCAN по всей базе. Заняло 22 минуты.

Результат на замерах следующего утра: проведение реализации ушло с 9,4 секунды до 1,8. Оборотно-сальдовая ведомость за квартал — с 3 минут 10 секунд до 26 секунд. Отчёт по взаиморасчётам — с 88 секунд до 11.

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

Мораль в том, что «база выросла» — самое удобное и самое ленивое объяснение медлительности. Рост объёма действительно замедляет систему, но линейно и предсказуемо. Скачкообразное ухудшение почти всегда означает, что что-то конкретное перестало работать: план обслуживания, задание, статистика. И это чинится за вечер, а не за бюджет.

Отдельно про мониторинг самого обслуживания, потому что этот пункт пропускают чаще всех.

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

Мы проверяем три вещи ежедневно: когда последний раз успешно завершился каждый job агента, когда последний раз обновлялась статистика по крупнейшим таблицам, и когда последний раз проходил CHECKDB.

-- когда статистика обновлялась последний раз, по 15 крупнейшим таблицам
SELECT TOP 15 OBJECT_NAME(s.object_id) AS tbl, s.name AS stat_name,
       STATS_DATE(s.object_id, s.stats_id) AS last_updated
FROM sys.stats s
JOIN sys.tables t ON t.object_id = s.object_id
ORDER BY STATS_DATE(s.object_id, s.stats_id) ASC;

Запрос сортирует по возрастанию даты, поэтому наверху окажется самая протухшая статистика. Если там дата полугодовой давности — вы только что нашли причину, по которой база «выросла».

Три проверки. Пять минут на настройку. Экономят разбор, который иначе занимает день.

Нужно ли перестраивать индексы каждую ночь?

Нет. Перестроение имеет смысл только для индексов с фрагментацией выше 30 % и размером больше тысячи страниц. Ежедневное перестроение всего подряд тратит часы и ресурсы впустую — используйте скрипты, принимающие решение по порогу.

Что важнее — индексы или статистика?

Статистика. Она обходится в разы дешевле, а на выбор плана запроса влияет сильнее. Обновляйте её ежедневно с FULLSCAN — это самое выгодное обслуживание базы 1С по соотношению эффекта к затратам.

Можно ли сжимать файл базы данных?

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

Как часто запускать тестирование и исправление информационной базы?

Планово раз в квартал, внепланово — после аварийного завершения сервера, сбоя дисков или появления необъяснимых ошибок. Обязательно с бэкапом непосредственно перед запуском и первым прогоном в режиме только проверки, без исправления.