Обслуживание индексов БД с помощью встроенного инструмента “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