Дефрагментация индексов MS SQL для платформы 8.3.22
База данных - Инструменты администратора БД
Файлы
ВНИМАНИЕ: Файлы из Базы знаний - это исходный код разработки. Это примеры решения задач, шаблоны, заготовки, "строительные материалы" для учетной системы. Файлы ориентированы на специалистов 1С, которые могут разобраться в коде и оптимизировать программу для запуска в базе данных. Гарантии работоспособности нет. Возврата нет. Технической поддержки нет.
Подписка PRO — скачивайте любые файлы со скидкой до 85% из Базы знаний
Оформите подписку на компанию для решения рабочих задач
Оформить подписку и скачать решение со скидкойВы можете заказать платную доработку или адаптацию этой разработки под вашу конфигурацию на «Бирже заказов».
- 0% комиссии — оплата напрямую исполнителю;
- Исполнители любого масштаба — от отдельных специалистов до команд под проект;
- Прямой обмен контактами между заказчиком и исполнителем;
- Безопасная сделка — при необходимости;
- Рейтинги, кейсы и прозрачная система откликов.
Начиная с 8.3.22 фирма "1С" убрала блокировки БД на уровне страниц, если БД расположена в MS SQL, то в индексах параметр Allow page locks стал равен False.
Проблема в том, что при дефрагментации индексов с использованием команды ALTER INDEX REORGANIZE возникает ошибка "The index ... on table ....... cannot be reorganized because page level locking is disabled.".
Предлагаю модификацию скрипта с сайта Microsoft, который позволяет обойти эту проблему.
-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid INT;
DECLARE @indexid INT;
DECLARE @partitioncount BIGINT;
DECLARE @schemaname NVARCHAR(130);
DECLARE @objectname NVARCHAR(130);
DECLARE @indexname NVARCHAR(130);
DECLARE @allowpagelocks INT;
DECLARE @partitionnum BIGINT;
DECLARE @partitions BIGINT;
DECLARE @frag FLOAT;
DECLARE @command NVARCHAR(4000);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0
AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR
FOR
SELECT *
FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1 = 1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid,
@indexid,
@partitionnum,
@frag;
IF @@FETCH_STATUS < 0
BREAK;
SELECT @objectname = QUOTENAME(o.name),
@schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
INNER JOIN sys.schemas AS s
ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT @indexname = QUOTENAME(name), @allowpagelocks = allow_page_locks
FROM sys.indexes
WHERE object_id = @objectid
AND index_id = @indexid;
SELECT @partitioncount = count(*)
FROM sys.partitions
WHERE object_id = @objectid
AND index_id = @indexid;
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @frag < 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @frag >= 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS NVARCHAR(10));
IF @frag < 30.0 AND @allowpagelocks = 0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' SET (ALLOW_PAGE_LOCKS = ON);'
+ @command + N'; ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' SET (ALLOW_PAGE_LOCKS = OFF)';
EXEC (@command);
PRINT N'Executed: ' + @command;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
GO
К публикации приложен скрипт для дефрагментации индексов для нескольких баз.
Вступайте в нашу телеграмм-группу Инфостарт
