План обслуживания MS SQL для базы 1С: рабочий шаблон на неделю

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

Встроенный конструктор планов обслуживания решает задачу «дать возможность настроить что-то мышкой». Он с ней справляется. Для боевой базы 1С этого мало.

Четыре конкретные претензии.

Не различает пороги фрагментации. Задача «Перестроение индекса» перестраивает все индексы базы независимо от их состояния. На базе с сорока тысячами индексов, из которых реально фрагментированы двести, вы тратите часы вместо минут.

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

Плохо сообщает о сбоях. История выполнения ведётся, но по умолчанию никто её не читает.

Логика зашита в графическую схему. Её нельзя положить в систему контроля версий, скопировать на другой сервер текстом, отревьюить.

Альтернатива — набор скриптов Оли Халленгрена. Это де-факто отраслевой стандарт, свободно доступный, поддерживаемый много лет и работающий на всех редакциях, включая Express.

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

Ставится одним скриптом, создающим служебную базу и процедуры.

Создаём отдельную базу под служебные объекты — не в master:

CREATE DATABASE [DBA];
ALTER DATABASE [DBA] SET RECOVERY SIMPLE;
-- дальше выполнить MaintenanceSolution.sql в контексте [DBA]

После установки появляются три ключевые процедуры и таблица журнала:

Таблица CommandLog — недооценённая часть. Она хранит каждую выполненную команду с длительностью и статусом. По ней потом видно, что реально делалось ночью и сколько это заняло, без догадок.

SELECT TOP 30 StartTime, DatabaseName, CommandType, ObjectName,
       DATEDIFF(second, StartTime, EndTime) AS sec, ErrorNumber
FROM dbo.CommandLog
ORDER BY ID DESC;

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

Пять отдельных заданий. Именно отдельных: падение одного не должно мешать остальным.

1. Обновление статистики, ежедневно.

EXECUTE [DBA].dbo.IndexOptimize
  @Databases = 'USER_DATABASES',
  @FragmentationLow = NULL,
  @FragmentationMedium = NULL,
  @FragmentationHigh = NULL,
  @UpdateStatistics = 'ALL',
  @StatisticsSample = 100,
  @OnlyModifiedStatistics = 'Y',
  @LogToTable = 'Y';

Три NULL отключают работу с индексами — задание занимается только статистикой. @StatisticsSample = 100 означает полное сканирование, @OnlyModifiedStatistics пропускает неизменившиеся.

2. Индексы, ежедневно после статистики.

EXECUTE [DBA].dbo.IndexOptimize
  @Databases = 'USER_DATABASES',
  @FragmentationLow = NULL,
  @FragmentationMedium = 'INDEX_REORGANIZE',
  @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
  @FragmentationLevel1 = 10,
  @FragmentationLevel2 = 40,
  @MinNumberOfPages = 1000,
  @TimeLimit = 5400,
  @UpdateStatistics = NULL,
  @LogToTable = 'Y';

Ключевые параметры: @MinNumberOfPages = 1000 отсекает мелочь, @TimeLimit = 5400 ограничивает работу полутора часами, чтобы задание не наехало на утро.

3. Полный бэкап, ежедневно.

EXECUTE [DBA].dbo.DatabaseBackup
  @Databases = 'USER_DATABASES',
  @Directory = 'E:\backup',
  @BackupType = 'FULL',
  @Compress = 'Y',
  @Verify = 'Y',
  @CheckSum = 'Y',
  @CleanupTime = 72,
  @LogToTable = 'Y';

4. Бэкап журнала, каждые 30 минут в рабочее время. То же с @BackupType = 'LOG' и @CleanupTime = 48.

5. Проверка целостности, еженедельно.

EXECUTE [DBA].dbo.DatabaseIntegrityCheck
  @Databases = 'USER_DATABASES',
  @CheckCommands = 'CHECKDB',
  @PhysicalOnly = 'N',
  @LogToTable = 'Y';

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

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

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

Важно про @CleanupTime в бэкапах. Значение в часах, и оно определяет, сколько копий хранится локально. 72 часа для полных и 48 для журнала — минимум, при котором можно восстановиться на любую точку за последние двое суток. Долгосрочное хранение — задача внешней системы бэкапа, а не SQL Server.

И ещё: параметры @Verify = 'Y' и @CheckSum = 'Y' удлиняют бэкап примерно вдвое. Мы их всё равно включаем — бэкап, целостность которого не проверена, стоит ровно столько же, сколько его отсутствие.

Самая пропускаемая часть настройки, и одновременно самая важная.

Настраивается в три шага.

Database Mail. Профиль с реальным SMTP-сервером. Проверяется отправкой тестового письма — не «настроили и ладно».

EXEC msdb.dbo.sp_send_dbmail
  @profile_name = 'SQLAlerts',
  @recipients = 'it@example.ru',
  @subject = 'Тест уведомлений SQL Agent',
  @body = 'Если письмо пришло — почта работает.';

Оператор. Получатель уведомлений. Один адрес — почтовая группа, а не личная почта конкретного человека, который уйдёт в отпуск.

Привязка к заданиям. У каждого задания в свойствах — уведомлять оператора при сбое.

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

Поэтому добавляем внешнюю проверку — запрос, который смотрит на факт выполнения:

SELECT j.name,
       MAX(msdb.dbo.agent_datetime(h.run_date, h.run_time)) AS last_run,
       DATEDIFF(hour, MAX(msdb.dbo.agent_datetime(h.run_date, h.run_time)), GETDATE()) AS hours_ago
FROM msdb.dbo.sysjobs j
LEFT JOIN msdb.dbo.sysjobhistory h ON h.job_id = j.job_id AND h.step_id = 0 AND h.run_status = 1
WHERE j.enabled = 1
GROUP BY j.name
HAVING DATEDIFF(hour, MAX(msdb.dbo.agent_datetime(h.run_date, h.run_time)), GETDATE()) > 30
    OR MAX(h.run_date) IS NULL;

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

Если у клиента уже есть план обслуживания, собранный мастером, мы первым делом смотрим, что оттуда надо выбросить.

Сжатие базы данных. Задача SHRINK в регулярном плане — самое вредное, что там может быть. Она доводит фрагментацию до максимума и сводит на нет работу задачи перестроения индексов, которая обычно стоит рядом.

Перестроение всех индексов подряд. Заменяется на IndexOptimize с порогами.

Обновление статистики без полного сканирования. Задача мастера использует выборку по умолчанию — для 1С этого мало.

Реорганизация вместе с перестроением на одних и тех же индексах. Встречается, когда обе задачи добавили «чтобы наверняка». Работа делается дважды.

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

Что оставить и перенести в новую схему: расписания и адреса каталогов бэкапа. Всё остальное собирается заново.

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

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

Один момент, который меняет схему и о котором вспоминают уже при разборе ночного инцидента.

В редакции Standard перестроение индекса в режиме ONLINE недоступно до SQL Server 2019. Это значит, что на 2016 и 2017 в редакции Standard любое перестроение блокирует таблицу целиком на всё время операции.

Для базы 1С это критично. Перестроение индекса на регистре в 30 миллионов строк идёт десятки минут, и всё это время таблица недоступна. Если задание наехало на утро — пользователи получают наглухо висящую программу без единого сообщения об ошибке.

Отсюда три следствия для редакции Standard.

Первое: параметр @TimeLimit становится обязательным, а не желательным. Он не прерывает уже начатую операцию, но не даёт начать новую после истечения лимита — значит, лимит надо ставить с запасом относительно конца окна, а не впритык.

Второе: имеет смысл поднять порог перехода к перестроению. Мы на Standard ставим @FragmentationLevel2 в 50 вместо 40 — реорганизация работает онлайн и безопаснее, пусть даже результат чуть хуже.

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

-- отдельное задание: только самые крупные, только по воскресеньям
EXECUTE [DBA].dbo.IndexOptimize
  @Databases = 'buh_prod',
  @Indexes = 'buh_prod.dbo._AccumRg%',
  @FragmentationLow = NULL,
  @FragmentationMedium = 'INDEX_REORGANIZE',
  @FragmentationHigh = 'INDEX_REBUILD_OFFLINE',
  @MinNumberOfPages = 50000,
  @TimeLimit = 10800,
  @LogToTable = 'Y';

Начиная с SQL Server 2019 в Standard появилось онлайн-перестроение, и проблема снимается. Но у клиентов с историей 2016 и 2017 встречаются регулярно, и проверять редакцию с версией надо до того, как собирать расписание, а не после.

SELECT SERVERPROPERTY('ProductVersion') AS ver,
       SERVERPROPERTY('Edition') AS edition;

Финальная проверка после развёртывания. Через неделю после запуска отвечаем на семь вопросов.

1. Все ли задания отработали каждую ночь? — sysjobhistory.
2. Сколько времени занимала каждая задача? — CommandLog.
3. Укладывается ли ночной цикл в окно? — сумма длительностей.
4. Была ли реорганизация или перестроение реально нужны? — если CommandLog пуст по индексам, пороги подобраны верно и база здорова.
5. Проходит ли CHECKDB без ошибок? — ErrorNumber в журнале.
6. Восстанавливается ли бэкап? — обязательный тестовый RESTORE на другой сервер.
7. Приходят ли уведомления? — намеренно уронить тестовое задание и убедиться, что письмо пришло.

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

Мы проверяем уведомления при развёртывании и потом раз в квартал. Способ простой: создаётся задание с шагом RAISERROR('test', 16, 1), запускается вручную, ждём письмо.

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

Чем скрипты Ola Hallengren лучше мастера планов обслуживания?

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

Почему статистику обновлять отдельным заданием, а не вместе с индексами?

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

Обязательно ли включать Verify и CheckSum при бэкапе?

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

Как узнать, что задание вообще перестало запускаться?

Уведомления о сбоях этого не покажут — при незапуске сбоя нет. Нужна отдельная проверка факта выполнения: запрос к sysjobhistory, который возвращает задания, не отрабатывавшие успешно дольше заданного порога. Вешается на мониторинг и проверяется ежедневно.