Реорганизация индексов базы данных 1С в MSSQL

Симптом: при обслуживании базы 1С реорганизация индексов падает с ошибкой
The index ... on table ... cannot be reorganized because page level locking is disabled
(«невозможно реорганизовать индекс, поскольку отключена блокировка на уровне страницы»).

Почему это происходит

Начиная с версии платформы 8.3.22 сервер 1С создаёт индексы с выключенной страничной блокировкой (ALLOW_PAGE_LOCKS = OFF). Это сделано, чтобы SQL Server реже эскалировал блокировки до уровня таблицы и меньше мешал одновременной работе пользователей.

Побочный эффект: операция ALTER INDEX ... REORGANIZE требует страничных блокировок и на таких индексах выполняться не может. Стандартные планы обслуживания MS SQL (задача Reorganize Index) на базах 1С после обновления платформы начинают завершаться ошибкой, хотя дефрагментация индексов остаётся обязательной частью регламентного обслуживания.

Два способа решения

Способ	Как работает	Когда применять
REBUILD вместо REORGANIZE	Перестроение индекса не требует страничных блокировок и работает без изменения настроек	Есть окно обслуживания; в Standard Edition блокирует таблицу на время операции
Временное включение ALLOW_PAGE_LOCKS	Перед реорганизацией включаем страничные блокировки, после — возвращаем в исходное состояние	Нужна именно онлайн-реорганизация без длительной блокировки таблиц

Если позволяет окно обслуживания, проще и надёжнее использовать REBUILD — он не меняет свойства индексов и заодно обновляет статистику. Скрипт ниже нужен, когда требуется реорганизация.

Скрипт: реорганизация с временным включением страничных блокировок

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

В отличие от «наивного» перебора всех индексов, здесь есть порог фрагментации (по умолчанию 10 %) — иначе задание вхолостую обрабатывает десятки тысяч индексов типовой базы 1С и длится часами.

SET NOCOUNT ON;

DECLARE @MinFragmentation float = 10.0;   -- порог фрагментации, %
DECLARE @MinPageCount     int   = 1000;   -- мелкие индексы не трогаем
DECLARE @DbName  sysname,
        @Sql     nvarchar(max),
        @Schema  sysname,
        @Table   sysname,
        @Index   sysname;

DECLARE db_cur CURSOR LOCAL FAST_FORWARD FOR
    SELECT name
    FROM sys.databases
    WHERE database_id > 4              -- исключаем системные базы
      AND state_desc = 'ONLINE'
      AND is_read_only = 0;

OPEN db_cur;
FETCH NEXT FROM db_cur INTO @DbName;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT '=== База: ' + @DbName;

    -- собираем список индексов текущей базы во временную таблицу
    IF OBJECT_ID('tempdb..#idx') IS NOT NULL DROP TABLE #idx;
    CREATE TABLE #idx (SchemaName sysname, TableName sysname, IndexName sysname, Frag float);

    SET @Sql = N'
        USE ' + QUOTENAME(@DbName) + N';
        INSERT INTO #idx (SchemaName, TableName, IndexName, Frag)
        SELECT sch.name, tab.name, ind.name, st.avg_fragmentation_in_percent
        FROM sys.indexes AS ind
            JOIN sys.tables  AS tab ON ind.object_id = tab.object_id
            JOIN sys.schemas AS sch ON tab.schema_id = sch.schema_id
            CROSS APPLY sys.dm_db_index_physical_stats
                (DB_ID(), ind.object_id, ind.index_id, NULL, ''LIMITED'') AS st
        WHERE ind.allow_page_locks = 0        -- те самые «проблемные» индексы 1С
          AND ind.name IS NOT NULL
          AND ind.is_disabled = 0
          AND st.index_level = 0
          AND st.avg_fragmentation_in_percent >= @frag
          AND st.page_count >= @pages;';

    EXEC sp_executesql @Sql,
         N'@frag float, @pages int', @MinFragmentation, @MinPageCount;

    DECLARE idx_cur CURSOR LOCAL FAST_FORWARD FOR
        SELECT SchemaName, TableName, IndexName, Frag FROM #idx;

    OPEN idx_cur;
    FETCH NEXT FROM idx_cur INTO @Schema, @Table, @Index, @MinFragmentation;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        BEGIN TRY
            SET @Sql =
                N'USE ' + QUOTENAME(@DbName) + N';
                  ALTER INDEX ' + QUOTENAME(@Index) + N' ON '
                    + QUOTENAME(@Schema) + N'.' + QUOTENAME(@Table)
                    + N' SET (ALLOW_PAGE_LOCKS = ON);
                  ALTER INDEX ' + QUOTENAME(@Index) + N' ON '
                    + QUOTENAME(@Schema) + N'.' + QUOTENAME(@Table)
                    + N' REORGANIZE WITH (LOB_COMPACTION = ON);
                  ALTER INDEX ' + QUOTENAME(@Index) + N' ON '
                    + QUOTENAME(@Schema) + N'.' + QUOTENAME(@Table)
                    + N' SET (ALLOW_PAGE_LOCKS = OFF);';
            EXEC sp_executesql @Sql;
            PRINT '    OK  ' + @Table + '.' + @Index;
        END TRY
        BEGIN CATCH
            PRINT '    СБОЙ ' + @Table + '.' + @Index + ' — ' + ERROR_MESSAGE();
            -- возвращаем исходное состояние, если ошибка произошла после включения
            BEGIN TRY
                SET @Sql = N'USE ' + QUOTENAME(@DbName) + N';
                    ALTER INDEX ' + QUOTENAME(@Index) + N' ON '
                    + QUOTENAME(@Schema) + N'.' + QUOTENAME(@Table)
                    + N' SET (ALLOW_PAGE_LOCKS = OFF);';
                EXEC sp_executesql @Sql;
            END TRY BEGIN CATCH END CATCH;
        END CATCH;

        FETCH NEXT FROM idx_cur INTO @Schema, @Table, @Index, @MinFragmentation;
    END

    CLOSE idx_cur; DEALLOCATE idx_cur;
    DROP TABLE #idx;

    FETCH NEXT FROM db_cur INTO @DbName;
END

CLOSE db_cur; DEALLOCATE db_cur;

Скрипт возвращает индексы в состояние ALLOW_PAGE_LOCKS = OFF, то есть к тому виду, который ожидает платформа 1С. Не оставляйте страничные блокировки включёнными «навсегда» — это ухудшит параллельную работу пользователей.

Вариант через REBUILD (без изменения свойств индексов)

DECLARE @Sql nvarchar(max) = N'';
SELECT @Sql = @Sql + N'ALTER INDEX ' + QUOTENAME(ind.name)
       + N' ON ' + QUOTENAME(sch.name) + N'.' + QUOTENAME(tab.name)
       + N' REBUILD WITH (SORT_IN_TEMPDB = ON);' + CHAR(13)
FROM sys.indexes AS ind
    JOIN sys.tables  AS tab ON ind.object_id = tab.object_id
    JOIN sys.schemas AS sch ON tab.schema_id = sch.schema_id
    CROSS APPLY sys.dm_db_index_physical_stats
        (DB_ID(), ind.object_id, ind.index_id, NULL, 'LIMITED') AS st
WHERE ind.name IS NOT NULL
  AND ind.is_disabled = 0
  AND st.index_level = 0
  AND st.avg_fragmentation_in_percent >= 10
  AND st.page_count >= 1000;

EXEC sp_executesql @Sql;

В Enterprise Edition к REBUILD можно добавить ONLINE = ON — тогда таблица не блокируется на время перестроения.

Как встроить в регламент

1. В плане обслуживания MS SQL удалите стандартную задачу Reorganize Index для баз 1С — она будет падать с той же ошибкой.
2. Добавьте задачу Execute T-SQL Statement с одним из скриптов выше.
3. Расписание — еженедельно, вне рабочего времени.
4. После реорганизации обновите статистику (задача Update Statistics или EXEC sp_updatestats).

Подробно про построение плана обслуживания — Создание планов резервного копирования и обслуживания MS SQL.

Проверка результата

SELECT sch.name AS [Схема], tab.name AS [Таблица], ind.name AS [Индекс],
       ind.allow_page_locks AS [PageLocks],
       CAST(st.avg_fragmentation_in_percent AS decimal(5,2)) AS [Фрагментация,%]
FROM sys.indexes AS ind
    JOIN sys.tables  AS tab ON ind.object_id = tab.object_id
    JOIN sys.schemas AS sch ON tab.schema_id = sch.schema_id
    CROSS APPLY sys.dm_db_index_physical_stats
        (DB_ID(), ind.object_id, ind.index_id, NULL, 'LIMITED') AS st
WHERE ind.name IS NOT NULL AND st.index_level = 0
  AND st.page_count >= 1000
ORDER BY st.avg_fragmentation_in_percent DESC;

После обслуживания фрагментация верхних строк должна упасть ниже порога, а PageLocks — остаться равным 0.