Дефрагментация индексов БД в MS SQL Server

Обслуживание индексов БД с помощью встроенного инструмента «Maintenance Plans» не всегда оправдано, особенно для больших баз. Красиво сформулировано тут:

Некоторые пользователи просто решают восстанавливать или перестраивать все индексы каждую ночь или каждую неделю (используя, например, вариант с планом обслуживания) вместо того, чтобы выяснять, какие индексы фрагментированы и какое преимущества даст устранение фрагментации. Хотя это может быть хорошим решением для невольного администратора базы данных, который просто желает применить какое-то решение с минимальными усилиями, это может оказаться очень плохим выбором для более крупных баз данных и систем, где ресурсы в дефиците.

В контексте 1С Гилев рекомендует скрипт: http://www.gilev.ru/dbreindex/ (раньше было тут: http://www.gilev.ru/1c/mssql/dbreindex.htm ).

Если баз несколько, лучше воспользоваться скриптом, который обрабатывает все пользовательские базы: http://blogs.msmvps.com/gladchenko/2008/03/30/tips-for-dba-using-sys-dm_db_index_physical_stats-in-a-script-to-rebuild-or-reorganize-indexes-no-partitions-sql-server-2005/   (раньше было тут: http://msmvps.com/blogs/gladchenko/archive/2008/03/31/1563721.aspx ). В основном посте скрипты для одной БД, а в этом комментарии — для всех пользовательских баз. Но там он как-то криво опубликован, со странными кавычками и тире. Кроме того, если одна из баз — в режиме offline — вываливается с ошибкой. Ниже исправленный вариант:

SET NOCOUNT ON
 SET DEADLOCK_PRIORITY LOW
 DECLARE @SQL nvarchar(2048), @db int, @@SQL2 nvarchar(max), @ErrorID int
 SET @@SQL2 = 'DECLARE @SQL nvarchar(2048);
 '
 DECLARE usersdatabases CURSOR GLOBAL FAST_FORWARD READ_ONLY FOR
 SELECT database_id FROM sys.databases
 WHERE (CAST(case when name in ('master','model','msdb','tempdb') then 1 else is_distributor end AS bit)=0
 AND CAST(isnull(source_database_id, 0) AS bit)=0
 AND state=0)
 ORDER BY [name]
OPEN GLOBAL usersdatabases
 WHILE 1 = 1
 BEGIN
 FETCH usersdatabases INTO @db
 IF @@fetch_status <> 0 BREAK
 SET @@SQL2 = @@SQL2 + '
 USE ' + DB_NAME(@db) + ';
 DECLARE reindex' + CAST(@db AS nvarchar) + ' CURSOR GLOBAL FAST_FORWARD READ_ONLY FOR
 SELECT ''ALTER INDEX ALL ON ['' + DB_NAME(' + CAST(@db AS nvarchar) + ')+ '']..['' + OBJECT_NAME(afp.[OBJECT_ID]) + ''] REBUILD WITH (SORT_IN_TEMPDB = ON);'' AS [Инструкция T-SQL]
 FROM ' + DB_NAME(@db) + '.sys.dm_db_index_physical_stats (' + CAST(@db AS nvarchar) + ', NULL, NULL, NULL, ''SAMPLED'') AS afp
 WHERE afp.database_id = ' + CAST(@db AS nvarchar) + '
 AND afp.index_type_desc IN (''CLUSTERED INDEX'')
 AND (afp.avg_fragmentation_in_percent >= 15 OR afp.avg_page_space_used_in_percent <= 60)
 AND afp.page_count > 12
 UNION ALL
 SELECT [Инструкция T-SQL] =
 CASE
 WHEN afp.avg_fragmentation_in_percent >= 15
 OR afp.avg_page_space_used_in_percent <= 60
 THEN ''ALTER INDEX ['' + i.name + ''] ON ['' + DB_NAME(' + CAST(@db AS nvarchar) + ') + '']..['' + OBJECT_NAME(afp.[OBJECT_ID]) + ''] REBUILD WITH (SORT_IN_TEMPDB = ON);''
 WHEN (afp.avg_fragmentation_in_percent < 15 AND afp.avg_fragmentation_in_percent >= 10)
 OR (afp.avg_page_space_used_in_percent > 60 AND afp.avg_page_space_used_in_percent < 75)
 THEN ''ALTER INDEX ['' + i.name + ''] ON ['' + DB_NAME(' + CAST(@db AS nvarchar) + ') + '']..['' + OBJECT_NAME(afp.[OBJECT_ID]) + ''] REORGANIZE;''
 END
 FROM ' + DB_NAME(@db) + '.sys.dm_db_index_physical_stats (' + CAST(@db AS nvarchar) + ', NULL, NULL, NULL, ''SAMPLED'') AS afp
 JOIN ' + DB_NAME(@db) + '.sys.indexes AS i
 ON (afp.[OBJECT_ID] = i.[OBJECT_ID] AND afp.index_id = i.index_id)
 AND afp.database_id = ' + CAST(@db AS nvarchar) + '
 AND afp.index_type_desc IN (''NONCLUSTERED INDEX'')
 AND (
 (afp.avg_fragmentation_in_percent >= 10 AND afp.avg_fragmentation_in_percent < 15)
 OR (afp.avg_page_space_used_in_percent > 60 AND afp.avg_page_space_used_in_percent < 75)
 )
 AND afp.page_count > 12
 AND afp.[OBJECT_ID] NOT IN (
 SELECT [OBJECT_ID]
 FROM ' + DB_NAME(@db) + '.sys.dm_db_index_physical_stats (' + CAST(@db AS nvarchar) + ', NULL, NULL, NULL, ''SAMPLED'')
 WHERE database_id = ' + CAST(@db AS nvarchar) + '
 AND index_type_desc IN (''CLUSTERED INDEX'')
 AND (avg_fragmentation_in_percent >= 15 OR avg_page_space_used_in_percent < 60)
 AND page_count > 1
 )
 ORDER BY [Инструкция T-SQL]'
SET @@SQL2 = @@SQL2 + '
 OPEN GLOBAL reindex' + CAST(@db AS nvarchar) + '
 WHILE 1 = 1
 BEGIN
 FETCH reindex' + CAST(@db AS nvarchar) + ' INTO @SQL
 IF @@fetch_status <> 0 BREAK
 EXEC(@SQL)
 PRINT @SQL
 END
 CLOSE GLOBAL reindex' + CAST(@db AS nvarchar) + '
 DEALLOCATE GLOBAL reindex' + CAST(@db AS nvarchar) + ''
--PRINT @db
 END
 CLOSE GLOBAL usersdatabases
 DEALLOCATE usersdatabases
 DECLARE @SQL1 nvarchar(500)
 --PRINT @@SQL2
 EXECUTE master..sp_executesql
 @@SQL2
 ,N'@SQL1 nvarchar(2048) OUTPUT'
 ,@SQL1 = @SQL1 OUTPUT
 --PRINT('———————-')
 --PRINT @SQL1
 GO

Кроме этого варианта, есть чудесный набор скриптов от ola.hallengren.com . Обслуживание индексов тут: https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
 

Обсуждение закрыто.