Описание проблемы
Начиная с релиза платформы 1С 8.3.22, таблицы в базе данных создаются с ALLOW_PAGE_LOCKS = OFF. При этом стандартный план обслуживания с реорганизацией индекса заканчивается с ошибкой. При перестроении индекса тоже будет ошибка, если в параметр MaxDOP установлено значение больше 1. Это поведение платформы отражено в документации V8Update (1c.ru) и в ИТС Регламентные операции на уровне СУБД для MS SQL Server.
Решения
В Интернет есть множество решений этой проблемы. Можно включить ALLOW_PAGE_LOCKS для всех индексов базы: EXEC sp_MSforeachtable ‘ALTER INDEX ALL ON ? SET (ALLOW_PAGE_LOCKS = ON)’. На моем быстром сервере процесс включения и выключения для одной базы ERP занимает две минуты. Есть готовые скрипты, например: Дефрагментация и реиндексация после перехода на платформу 8.3.22 (infostart.ru). Тут в перестроение попадают все таблицы баз разбора. На анализируется ни степень их фрагментации, ни размер. Это очень плохо, так как оптимизация индексов продолжается очень долго и формирует приличный журнал транзакций.
SQL Server Maintenance Solution
В своих проектах я всегда использую SQL Server Backup, Integrity Check, Index and Statistics Maintenance (hallengren.com). Это очень мощный инструмент для обслуживания баз MS SQL. Проблема ALLOW_PAGE_LOCKS = OFF затронула это решение в куда меньшей степени, чем стандартные планы обслуживания. Решение проверяет ALLOW_PAGE_LOCKS и, если оно установлено в OFF, заменяет операцию INDEX REORGANIZE на более затратную по ресурсам INDEX REBUILD ONLINE да еще и со степенью параллелизма (MAXDOP) установленным в 1. Для продолжения использования своего любимого решения, я разработал патч, который перед каждой операцией INDEX REORGANIZE и INDEX REBUILD ONLINE, устанавливает ALLOW_PAGE_LOCKS = ON, а после в OFF.
*** sql-server-maintenance-solution/MaintenanceSolution.sql 2024-07-19 23:41:19.724061263 +0300
--- sql-server-maintenance-solution_1Cpathed/MaintenanceSolution.sql 2024-07-20 00:00:56.763873586 +0300
*************** BEGIN
*** 8294,8300 ****
-- Which actions are allowed?
IF @CurrentIndexID IS NOT NULL AND EXISTS(SELECT * FROM @ActionsPreferred)
BEGIN
! IF @CurrentOnReadOnlyFileGroup = 0 AND @CurrentIndexType IN (1,2,3,4,5) AND (@CurrentIsMemoryOptimized = 0 OR @CurrentIsMemoryOptimized IS NULL) AND (@CurrentAllowPageLocks = 1 OR @CurrentIndexType = 5)
BEGIN
INSERT INTO @CurrentActionsAllowed ([Action])
VALUES ('INDEX_REORGANIZE')
--- 8294,8302 ----
-- Which actions are allowed?
IF @CurrentIndexID IS NOT NULL AND EXISTS(SELECT * FROM @ActionsPreferred)
BEGIN
! --//Проверка на ALLOW_PAGE_LOCKS больше не нужна, так как мы установим значение ON перед выполнением.
! -- IF @CurrentOnReadOnlyFileGroup = 0 AND @CurrentIndexType IN (1,2,3,4,5) AND (@CurrentIsMemoryOptimized = 0 OR @CurrentIsMemoryOptimized IS NULL) AND (@CurrentAllowPageLocks = 1 OR @CurrentIndexType = 5)
! IF @CurrentOnReadOnlyFileGroup = 0 AND @CurrentIndexType IN (1,2,3,4,5) AND (@CurrentIsMemoryOptimized = 0 OR @CurrentIsMemoryOptimized IS NULL)
BEGIN
INSERT INTO @CurrentActionsAllowed ([Action])
VALUES ('INDEX_REORGANIZE')
*************** BEGIN
*** 8356,8365 ****
BEGIN
SET @CurrentMaxDOP = @MaxDOP
! IF @CurrentAction = 'INDEX_REBUILD_ONLINE' AND @CurrentAllowPageLocks = 0
! BEGIN
! SET @CurrentMaxDOP = 1
! END
END
-- Update statistics?
--- 8358,8368 ----
BEGIN
SET @CurrentMaxDOP = @MaxDOP
! --//Проверка на ALLOW_PAGE_LOCKS больше не нужна, так как мы установим значение ON перед выполнением. Это изменение необходимо для установки MaxDOP большего, чем 1.
! -- IF @CurrentAction = 'INDEX_REBUILD_ONLINE' AND @CurrentAllowPageLocks = 0
! -- BEGIN
! -- SET @CurrentMaxDOP = 1
! -- END
END
-- Update statistics?
*************** BEGIN
*** 8424,8429 ****
--- 8427,8434 ----
SET @CurrentCommand = ''
IF @LockTimeout IS NOT NULL SET @CurrentCommand = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
+ --//Устанавливаем ALLOW_PAGE_LOCKS = ON, если это необходимо. Для INDEX_REORGANIZE потому, что иначе закончится с ошибкой. Для INDEX_REBUILD_ONLINE потому, что при MaxDOP > 1 закончится с ошибкой.
+ IF @CurrentAction IN('INDEX_REORGANIZE','INDEX_REBUILD_ONLINE') AND @CurrentAllowPageLocks = 0 SET @CurrentCommand += 'ALTER INDEX ' + QUOTENAME(@CurrentIndexName) + ' ON ' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + 'SET (ALLOW_PAGE_LOCKS = ON); '
SET @CurrentCommand += 'ALTER INDEX ' + QUOTENAME(@CurrentIndexName) + ' ON ' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName)
IF @CurrentResumableIndexOperation = 1 SET @CurrentCommand += ' RESUME'
IF @CurrentAction IN('INDEX_REBUILD_ONLINE','INDEX_REBUILD_OFFLINE') AND @CurrentResumableIndexOperation = 0 SET @CurrentCommand += ' REBUILD'
*************** BEGIN
*** 8529,8534 ****
--- 8534,8541 ----
IF @CurrentAlterIndexWithClause IS NOT NULL SET @CurrentCommand += @CurrentAlterIndexWithClause
+ --//Возвращаем на место ALLOW_PAGE_LOCKS = OFF.
+ IF @CurrentAction IN('INDEX_REORGANIZE','INDEX_REBUILD_ONLINE') AND @CurrentAllowPageLocks = 0 SET @CurrentCommand += '; ALTER INDEX ' + QUOTENAME(@CurrentIndexName) + ' ON ' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + 'SET (ALLOW_PAGE_LOCKS = OFF); '
EXECUTE @CurrentCommandOutput = dbo.CommandExecute @DatabaseContext = @CurrentDatabaseName, @Command = @CurrentCommand, @CommandType = @CurrentCommandType, @Mode = 2, @Comment = @CurrentComment, @DatabaseName = @CurrentDatabaseName, @SchemaName = @CurrentSchemaName, @ObjectName = @CurrentObjectName, @ObjectType = @CurrentObjectType, @IndexName = @CurrentIndexName, @IndexType = @CurrentIndexType, @PartitionNumber = @CurrentPartitionNumber, @ExtendedInfo = @CurrentExtendedInfo, @LockMessageSeverity = @LockMessageSeverity, @ExecuteAsUser = @ExecuteAsUser, @LogToTable = @LogToTable, @Execute = @Execute
SET @Error = @@ERROR
IF @Error <> 0 SET @CurrentCommandOutput = @Error
Выкладываю измененное решение целиком.
https://ola.hallengren.com
The SQL Server Maintenance Solution is licensed under the MIT license, a popular and widely used open source license.
Copyright (c) 2023 Ola Hallengren
Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the “Software”), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.