Не работает перестроение индексов SQL для базы 1С: почему зависает процесс и как это исправить

← На главную

При администрировании баз данных 1С на СУБД Microsoft SQL Server системные администраторы часто сталкиваются с ситуацией, когда стандартный план обслуживания (Maintenance Plan) внезапно перестает выполняться. Задание «зависает» на этапе перестроения индексов рабочей базы, выполняется бесконечно долго, не выдает никаких ошибок в логи и не позволяет даже просмотреть собственный T-SQL скрипт в среде SSMS (SQL Server Management Studio). При этом на других базах аналогичные задачи могут отрабатывать за считанные секунды.

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

Почему зависает стандартное перестроение индексов в базах 1С

Для начала проанализируем ситуацию и выясним, почему стандартный механизм СУБД (особенно часто это проявляется на SQL Server 2016) уходит в вечное ожидание. Существует несколько основных причин:

Блокировки метаданных (Metadata Locks). Платформа 1С:Предприятие активно работает с временными таблицами и динамически меняет структуру данных. Если СУБД пытается начать перестроение индекса (даже для оценки степени фрагментации), ей требуется получить монопольный доступ к схеме таблицы (блокировка типа Sch-M — Schema Modification). Если в этот момент выполняется длительный фоновый процесс 1С или завис сеанс пользователя, удерживающий блокировку стабильности схемы (Sch-S), SQL Server будет бесконечно ожидать снятия этой блокировки. Именно поэтому в SSMS зависает даже простая кнопка «Просмотр T-SQL».
Конфликт с режимом Snapshot Isolation. Для баз 1С настоятельно рекомендуется использовать режим READ_COMMITTED_SNAPSHOT (RCSI). В этом режиме версии строк хранятся в системной базе tempdb. Если в системе есть незавершенная («зависшая») транзакция, SQL Server не может очистить версионное хранилище. При попытке выполнить перестроение индекса в режиме ONLINE = ON система будет бесконечно ждать завершения этой старой транзакции для обеспечения согласованности.
Особенности работы с LOB-данными (Large Objects). База 1С (например, Управление производственным предприятием — УПП) содержит таблицы с полями типа IMAGE или VARBINARY(MAX), в которых хранятся файлы, картинки или двоичные данные («Хранилище значения» в терминах 1С). В ранних сборках MS SQL Server 2016 существовала известная ошибка, при которой попытка онлайн-перестроения таких индексов приводила к взаимной блокировке процесса самого на себя.
Асинхронное автообновление статистики. Если в СУБД включена опция асинхронного обновления статистических данных, фоновый поток оптимизатора может конфликтовать с планом обслуживания, вызывая длительные ожидания на уровне дисковой подсистемы.

Шаг 1. Диагностика причин зависания в реальном времени

Прежде чем что-то менять, нам нужно понять, что именно блокирует выполнение операции в данный момент. Для этого откроем SQL Server Management Studio (SSMS), создадим новый запрос к нашей базе данных и выполним следующий скрипт для поиска активных блокировок и типов ожидания:


SELECT 
    r.session_id,
    r.status,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    t.text AS [QueryText]
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id > 50;

Рассмотрим результаты выполнения запроса. Если в колонке wait_type вы видите значение LCK_M_SCH_M (ожидание блокировки изменения схемы) или LCK_M_SCH_S, это подтверждает наличие конфликта блокировок. Обратите внимание на колонку blocking_session_id — там будет указан идентификатор сессии (SPID), которая мешает нашему процессу обслуживания.

Также полезно проверить наличие транзакций, препятствующих очистке версионного хранилища в tempdb:


SELECT 
    transaction_id, 
    name, 
    transaction_begin_time, 
    case transaction_type 
        when 1 then 'Read/Write' 
        when 2 then 'Read-Only' 
        when 3 then 'System' 
    end as [Type]
FROM sys.dm_tran_active_snapshot_database_transactions;

Если обнаружена транзакция, которая активна уже много часов, ее необходимо принудительно завершить с помощью команды KILL [номер_сессии], предварительно согласовав это с пользователями 1С.

Шаг 2. Отказ от стандартных планов обслуживания в пользу профессионального решения

Стандартный Мастер планов обслуживания SQL Server пытается обработать всю базу целиком без детального анализа структуры и текущего состояния индексов. Для баз данных 1С гораздо эффективнее и безопаснее использовать проверенное временем бесплатное решение — скрипты Олы Халленгрена (SQL Server Maintenance Solution).

Преимущества этого решения:

Интеллектуальный анализ: скрипт проверяет уровень фрагментации каждого индекса перед началом работы. Если фрагментация мала, индекс вообще не трогается.
Гибкость: при фрагментации от 5% до 30% выполняется быстрая реорганизация (INDEX_REORGANIZE), и только при фрагментации выше 30% — полное перестроение (INDEX_REBUILD).
Логирование: все операции детально записываются в специальную таблицу, что позволяет легко найти «проблемную» таблицу в случае сбоя.
Безопасность: скрипт динамически формирует и выполняет команды, обходя зависания, свойственные стандартным планам SSMS.

Шаг 3. Пошаговая инструкция по внедрению решения Олы Халленгрена

Выполним развертывание скрипта обслуживания по шагам.

1. Подготовка базы данных для утилит обслуживания

Крайне не рекомендуется устанавливать служебные объекты в системную базу данных master. Создадим для этих целей отдельную небольшую базу данных, например, с именем db_maintenance. Выполним в SSMS следующий запрос:


CREATE DATABASE [db_maintenance];
GO
ALTER DATABASE [db_maintenance] SET RECOVERY SIMPLE;
GO

2. Загрузка и развертывание основного скрипта

Скачайте официальный скрипт MaintenanceSolution.sql с сайта разработчика (или возьмите проверенную версию из надежного источника). Откройте этот файл в SSMS.

В самом начале скрипта найдите строку конфигурирования назначения объектов. Замените базу по умолчанию на созданную нами базу:

USE [db_maintenance]

Запустите скрипт на выполнение (клавиша F5). В результате в вашей базе db_maintenance будут созданы необходимые системные таблицы (включая CommandLog для ведения журнала) и хранимые процедуры, главной из которых является IndexOptimize.

3. Настройка и запуск процедуры оптимизации индексов

Теперь мы можем запустить выборочное перестроение и реорганизацию индексов для конкретной рабочей базы 1С (в нашем примере это база [UPP]). Напишем скрипт для ручного запуска и анализа:


EXEC [db_maintenance].[dbo].[IndexOptimize]
    @Databases = 'UPP',
    @MaxDOP = 2,
    @FragmentationLow = NULL,
    @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationLevel1 = 5,
    @FragmentationLevel2 = 30,
    @UpdateStatistics = 'ALL',
    @OnlyModifiedStatistics = 'Y',
    @LogToTable = 'Y';

Проанализируем ключевые параметры этого вызова:

@Databases = 'UPP' — указывает целевую базу 1С. Можно перечислить несколько баз через запятую.
@MaxDOP = 2 — ограничивает максимальное число ядер процессора, выделяемых на перестроение одного индекса. Это предотвращает стопроцентную загрузку процессора сервера, сохраняя ресурсы для работы пользователей.
@FragmentationLevel1 = 5 и @FragmentationLevel2 = 30 — пороги фрагментации в процентах для запуска реорганизации и перестроения соответственно.
@UpdateStatistics = 'ALL' — попутно обновляет статистику для всех таблиц базы данных.
@OnlyModifiedStatistics = 'Y' — обновляет статистику только для тех таблиц, в которых происходили изменения данных с момента последнего обновления (крайне важная опция для крупных баз).
@LogToTable = 'Y' — включает подробное логирование действий в служебную таблицу.

4. Проверка результатов выполнения

После завершения работы скрипта мы можем легко проанализировать, какие именно действия были выполнены СУБД. Для этого выполним запрос к таблице логов:


SELECT 
    DatabaseName,
    SchemaName,
    ObjectName,
    IndexName,
    Command,
    StartTime,
    EndTime,
    ErrorNumber
FROM [db_maintenance].[dbo].[CommandLog]
ORDER BY StartTime DESC;

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

Шаг 4. Автоматизация процесса через Агент SQL Server

Чтобы обслуживание выполнялось автоматически (например, каждую ночь в нерабочее время), настроим регламентное задание (Job) в SQL Server Agent.

Убедитесь, что служба SQL Server Agent (Агент SQL Server) запущена и настроена на автоматический запуск при старте системы.
В обозревателе объектов SSMS разверните узел SQL Server Agent, нажмите правой кнопкой мыши на раздел Jobs (Работы) и выберите New Job (Создать работу).
На вкладке General (Общие) укажите понятное имя, например: 1C_UPP_Index_Optimization.
Перейдите на вкладку Steps (Шаги) и нажмите кнопку New (Создать).
В поле Step name введите название шага: Run IndexOptimize.
В поле Database выберите нашу базу обслуживания db_maintenance.
В текстовое поле Command вставьте настроенный ранее T-SQL код вызова процедуры IndexOptimize.
Перейдите на вкладку Schedules (Расписания), нажмите New и настройте запуск задания на ночное время (например, ежедневно в 02:00), когда нагрузка на базу данных минимальна.

Использование этого профессионального подхода позволит полностью избавиться от зависаний при обслуживании баз данных 1С любого объема, обеспечит стабильно высокую производительность запросов за счет актуальной статистики и оптимизированных индексов, а также предоставит администратору прозрачный инструмент контроля за состоянием СУБД.