Рассмотрим классическую техническую дилемму, с которой часто сталкиваются администраторы баз данных и специалисты по оптимизации производительности систем на платформе 1С. Ситуация выглядит следующим образом: после развертывания базы данных из бэкапа или в процессе планового аудита СУБД обнаруживается высокая фрагментация индексов (например, по таблице плана счетов _Acc28 некластеризованный индекс _Acc28_ByCode_SR фрагментирован на 50%). Мы запускаем стандартную операцию перестроения индекса REBUILD, процедура завершается успешно, но при повторном формировании отчета фрагментация остается на том же уровне. При этом выполнение операции реорганизации REORGANIZE неожиданно решает проблему и снижает фрагментацию до нуля. Выясним причину этого странного поведения MS SQL Server и разберем по шагам, как правильно организовать обслуживание индексов для баз данных 1С.
Чтобы понять, почему полная пересборка индекса REBUILD оставляет фрагментацию неизменной, а дефрагментация по месту REORGANIZE убирает её, нам необходимо погрузиться в архитектуру распределения дискового пространства в MS SQL Server. Проанализируем ключевые механизмы работы СУБД с дисковой памятью:
_Acc28 в средних базах 1С часто является относительно небольшой.REBUILD полностью уничтожает старый индекс и строит его структуру заново на свободных страницах. Поскольку объем индекса мал (менее 8 страниц), SQL Server при перестроении снова выделяет ему страницы из смешанных экстентов. Эти страницы физически разбросаны по диску и чередуются со страницами других таблиц. Алгоритм оценки фрагментации видит, что страницы индекса лежат не последовательно, и рапортует о высокой логической фрагментации сразу после завершения REBUILD.REORGANIZE выполняется по месту. Она не создает структуру заново, а сжимает данные внутри уже выделенных страниц, упорядочивая строки. Если в результате оптимизации данные сжались и поместились на одну-две страницы, логическая фрагментация математически падает до 0%, так как внутри одной страницы фрагментации быть не может.Проанализируем практическую значимость фрагментации для малых таблиц. Официальные рекомендации Microsoft и ведущих экспертов по базам данных сходятся в одном: фрагментацию на таблицах и индексах размером менее 1000 страниц (примерно 8 МБ) следует полностью игнорировать.
Посмотрим на причины этого правила:
Рассмотрим, как изменился подход к обслуживанию индексов с переходом на современные серверы с твердотельными накопителями (SSD/NVMe) и большими объемами оперативной памяти:
REBUILD с указанием параметра заполнения (FILLFACTOR). Это позволяет сократить объем оперативной памяти, занимаемой кэшем базы данных 1С.При планировании регламентных работ по дефрагментации баз данных 1С системные администраторы часто сталкиваются со следующей проблемой. Начиная с версии платформы 8.3.22, при создании некоторых индексов 1С принудительно отключает блокировки на уровне страниц:
ALLOW_PAGE_LOCKS = OFF
Это архитектурное решение принято для снижения вероятности возникновения эскалации блокировок в крупных высоконагруженных базах данных. Однако у него есть неприятный побочный эффект: стандартная команда ALTER INDEX ... REORGANIZE не может быть выполнена для индекса, у которого отключены страничные блокировки. Попытка реорганизации завершится системной ошибкой MS SQL Server.
Для решения этой проблемы при обслуживании баз данных 1С приходится использовать либо исключительно операцию REBUILD (которая успешно работает и без блокировок страниц), либо динамически включать страничные блокировки перед реорганизацией и отключать их обратно сразу после её завершения.
Рассмотрим практические методы реализации дефрагментации индексов. Разберем по шагам три варианта решения проблемы.
Если вам необходимо быстро выполнить реорганизацию всех индексов во всех таблицах базы данных (как это успешно сделал автор темы на форуме), воспользуйтесь следующим T-SQL скриптом. Он циклически обходит все таблицы и выполняет команду REORGANIZE:
USE [Имя_Вашей_Базы_Данных]
GO
DECLARE @TableName NVARCHAR(255),
@SQL NVARCHAR(MAX)
SELECT @TableName = MIN(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
WHILE @TableName IS NOT NULL
BEGIN
SET @SQL = 'ALTER INDEX ALL ON [dbo].[' + @TableName + '] REORGANIZE;'
BEGIN TRY
EXEC sp_executesql @SQL
END TRY
BEGIN CATCH
-- Пропускаем таблицы, которые вызывают ошибку из-за ALLOW_PAGE_LOCKS = OFF
PRINT 'Не удалось выполнить REORGANIZE для таблицы: ' + @TableName
END CATCH
SELECT @TableName = MIN(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME > @TableName
END
Чтобы реорганизация успешно отрабатывала на современных версиях платформы 1С (8.3.22 и выше), мы можем модифицировать скрипт так, чтобы он временно разрешал блокировки страниц на время проведения регламентных работ, а затем возвращал настройки в исходное состояние:
USE [Имя_Вашей_Базы_Данных]
GO
DECLARE @TableName NVARCHAR(255),
@IndexName NVARCHAR(255),
@SQL NVARCHAR(MAX)
DECLARE IndexCursor CURSOR FOR
SELECT t.name AS TableName, i.name AS IndexName
FROM sys.indexes i
JOIN sys.tables t ON i.object_id = t.object_id
WHERE i.type > 0 -- Исключаем кучи (Heaps)
AND t.is_ms_shipped = 0; -- Исключаем системные таблицы
OPEN IndexCursor
FETCH NEXT FROM IndexCursor INTO @TableName, @IndexName
WHILE @@FETCH_STATUS = 0
BEGIN
-- 1. Временно включаем блокировки страниц
SET @SQL = 'ALTER INDEX [' + @IndexName + '] ON [dbo].[' + @TableName + '] SET (ALLOW_PAGE_LOCKS = ON);'
EXEC sp_executesql @SQL
-- 2. Выполняем реорганизацию индекса
SET @SQL = 'ALTER INDEX [' + @IndexName + '] ON [dbo].[' + @TableName + '] REORGANIZE;'
BEGIN TRY
EXEC sp_executesql @SQL
END TRY
BEGIN CATCH
PRINT 'Ошибка реорганизации индекса ' + @IndexName + ' на таблице ' + @TableName
END CATCH
-- 3. Возвращаем настройку ALLOW_PAGE_LOCKS в OFF (стандарт для 1С)
SET @SQL = 'ALTER INDEX [' + @IndexName + '] ON [dbo].[' + @TableName + '] SET (ALLOW_PAGE_LOCKS = OFF);'
EXEC sp_executesql @SQL
FETCH NEXT FROM IndexCursor INTO @TableName, @IndexName
END
CLOSE IndexCursor
DEALLOCATE IndexCursor
В реальной продуктивной эксплуатации баз данных 1С использование простых самописных циклов не рекомендуется, так как они нагружают сервер обработкой даже тех индексов, которые в этом не нуждаются. Стандартом де-факто в сообществе администраторов баз данных является бесплатный пакет скриптов обслуживания Ola Hallengren's SQL Server Maintenance Solution (в частности, хранимая процедура IndexOptimize).
Рассмотрим преимущества этого решения:
_Acc28 в примере автора темы) не будут бесполезно перестраиваться каждую ночь, экономя ресурсы СУБД.REORGANIZE), а если фрагментация превышает 30% — выполнять глубокое перестроение (REBUILD).REBUILD автоматически обновляет статистику индекса со 100% сканированием таблицы (аналог WITH FULLSCAN). Решение Олы Халленгрена позволяет гибко сочетать обслуживание индексов и обновление статистик только для изменившихся данных.Рекомендуем настроить запуск процедуры IndexOptimize через агент MS SQL (SQL Server Agent) по расписанию в нерабочее время (ночью) со следующими ключевыми параметрами:
@UpdateStatistics = 'ALL' для поддержания планов запросов в актуальном состоянии.ONLINE = ON), если это позволяет редакция SQL Server (Enterprise/Developer).