Параметрический -каждый параметр можно подстроить под себя
Успешно работает на нескольких серверах
Протестирован на MS SQL 2019
Внимание!
Скрипт обрабатывает только часто используемые индексы
чтобы убрать тормоза на других. редко используемых объектах - настройте по ним пртнудительно обновление статистики ( например для отчета по производительности)
Скрипт адаптивной обработки индекса, если индекс используется активно (параметр) и фрагментирован (параметр) или статистика неактуальна (параметр), то выполняет команды по обслуживанию индекса.
Каждая команда выводит в комментарий - почему она выполнилась.
Также можно как показать, что будет делать, так и выполнить сразу
DECLARE @OnlyShow INT = 0; --1 = только показать, 0 = еще и выполнить
DECLARE @threshold_modification_pct INT = 1; -- Сколько процентов считать важными изменениями. Таблицы большие и 1% на миллионах –уже 10 000…
DECLARE @minrows INT = 100; -- брать если строк больше в индексе
DECLARE @minused INT = 1000; -- если использовали больше чем 1000 раз
DECLARE @minSteps INT = 10; -- не берем индкесы если они с точки статистики бестолковые
DECLARE @maxRows INT = 100000; -- Или количество строк меньше чем то игнор предыдущее условие
DECLARE @FragsLevel INT = 5; -- дефраг только если больше
DECLARE @FragsLevelRebuild INT = 30; -- rebuild если фрагментация больше
DECLARE @EnterpriseEdition INT = 0; --case when SERVERPROPERTY ('edition')='Enterprise Edition (64-bit)' then 1 else 0 end
---- служебные переменные
DECLARE @command NVARCHAR(500);
DECLARE @TableName SYSNAME;
DECLARE @IndexName SYSNAME;
DECLARE @modification_counter NVARCHAR(200);
DECLARE @Table_rows NVARCHAR(200);
DECLARE @Table_used NVARCHAR(200);
DECLARE @Steps NVARCHAR(200);
DECLARE @avg_fragmentation_in_percent NUMERIC;
DECLARE @levelForDefrag NUMERIC;
DECLARE @Frag NVARCHAR(200);
DECLARE @Variant NVARCHAR(20);
DECLARE indexes CURSOR FOR
SELECT
obj.NAME [table],
stat.NAME [index],
modification_counter [modification_counter],
sp.rows [Table_rows],
user_seeks + user_scans + user_lookups AS Table_used,
sp.steps Steps,
ips.avg_fragmentation_in_percent AS avg_fragmentation_in_percent ,
ips.avg_fragmentation_in_percent AS Frag,
sp.rows * @threshold_modification_pct / 100 AS levelForDefrag,
CASE
WHEN modification_counter >
sp.rows * @threshold_modification_pct / 100
THEN
N'UPDATE STATISTICS'
WHEN ips.avg_fragmentation_in_percent > @FragsLevelRebuild THEN
'REBUILD'
ELSE 'REORGANIZE'
END AS Variant
FROM sys.objects AS obj
INNER JOIN sys.stats AS stat
ON stat.object_id = obj.object_id
CROSS apply sys.Dm_db_stats_properties(stat.object_id, stat.stats_id)
AS
sp
LEFT JOIN (SELECT *
FROM sys.Dm_db_index_physical_stats(Db_id(), NULL, NULL,
NULL, NULL
)) AS ips
ON stat.object_id = ips.object_id
AND stat.stats_id = ips.index_id
INNER JOIN sys.dm_db_index_usage_stats AS s
ON stat.object_id = s.object_id
AND stat.stats_id = s.index_id
WHERE s.database_id = Db_id()
AND obj.type = 'U'
AND stat.NAME NOT LIKE ( '_WA%' )
AND ips.alloc_unit_type_desc = 'IN_ROW_DATA'
AND @minrows < sp.rows
AND ( user_seeks + user_scans + user_lookups ) >= @minused
AND ( ips.avg_fragmentation_in_percent > @FragsLevel
OR ( modification_counter >
sp.rows * @threshold_modification_pct / 100 )
)
AND ( sp.steps > @minSteps
OR @maxRows > sp.rows )
--ORDER BY stat.NAME
; ---(user_seeks + user_scans + user_lookups) desc;
OPEN indexes;
WHILE ( 1 = 1 )
BEGIN
FETCH next FROM indexes INTO @TableName, @IndexName, @modification_counter ,
@Table_rows, @Table_used, @Steps, @avg_fragmentation_in_percent, @Frag,
@levelForDefrag,@Variant;
IF @@FETCH_STATUS < 0
BREAK;
IF @levelForDefrag < @modification_counter
SET @command = N'UPDATE STATISTICS dbo.' + @TableName + ' '
+ @IndexName
+ N' WITH FULLSCAN, MAXDOP=8; -- Steps '
+ @Steps + ', used ' + @Table_used + ', changed '
+ @modification_counter + ' rows of '
+ @Table_rows;
ELSE
SET @command =
CASE
WHEN @variant='REBUILD'
THEN
''
ELSE
N'ALTER INDEX ' + @IndexName + ' on dbo.' + @TableName+ ' SET (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON);'
END
+ N'ALTER INDEX ' + @IndexName + ' on dbo.' + @TableName
+ CASE
WHEN @variant='REBUILD'
THEN
' REBUILD WITH (SORT_IN_TEMPDB = ON, maxdop=8' + case When @EnterpriseEdition=1 then ', online=on' else '' end +')'
ELSE
' REORGANIZE '
END
+ CASE
WHEN @variant='REBUILD'
THEN
''
ELSE
N'; ALTER INDEX ' + @IndexName + ' on dbo.' + @TableName+ ' SET (ALLOW_PAGE_LOCKS = OFF, ALLOW_ROW_LOCKS = ON)'
END
+ '; --- frag = ' + @Frag + '%' + ', rows = ' + @Table_rows;
PRINT N'Executed: ' + @command;
IF @OnlyShow = 1
CONTINUE;
EXEC (@command);
END;
CLOSE indexes;
DEALLOCATE indexes;
