Можно ли добавить задание на обслуживание для проверки фрагментации индексов. Если более 50%, то автоматически перестройте эти индексы?
Размер индекса может варьироваться от 100 МБ до 10 ГБ. SQL 2005.
Спасибо.
Можно ли добавить задание на обслуживание для проверки фрагментации индексов. Если более 50%, то автоматически перестройте эти индексы?
Размер индекса может варьироваться от 100 МБ до 10 ГБ. SQL 2005.
Спасибо.
Я использую этот script. Обратите внимание, что я бы посоветовал вам прочитать о dmv, который я использую здесь, это скрытый камень в SQL2005 +.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
CREATE TABLE #FragmentedIndexes
(
DatabaseName SYSNAME
, SchemaName SYSNAME
, TableName SYSNAME
, IndexName SYSNAME
, [Fragmentation%] FLOAT
)
INSERT INTO #FragmentedIndexes
SELECT
DB_NAME(DB_ID()) AS DatabaseName
, ss.name AS SchemaName
, OBJECT_NAME (s.object_id) AS TableName
, i.name AS IndexName
, s.avg_fragmentation_in_percent AS [Fragmentation%]
FROM sys.dm_db_index_physical_stats(db_id(),NULL, NULL, NULL, 'SAMPLED') s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON s.object_id = o.object_id
INNER JOIN sys.schemas ss ON ss.[schema_id] = o.[schema_id]
WHERE s.database_id = DB_ID()
AND i.index_id != 0
AND s.record_count > 0
AND o.is_ms_shipped = 0
DECLARE @RebuildIndexesSQL NVARCHAR(MAX)
SET @RebuildIndexesSQL = ''
SELECT
@RebuildIndexesSQL = @RebuildIndexesSQL +
CASE
WHEN [Fragmentation%] > 30
THEN CHAR(10) + 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON '
+ QUOTENAME(SchemaName) + '.'
+ QUOTENAME(TableName) + ' REBUILD;'
WHEN [Fragmentation%] > 10
THEN CHAR(10) + 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON '
+ QUOTENAME(SchemaName) + '.'
+ QUOTENAME(TableName) + ' REORGANIZE;'
END
FROM #FragmentedIndexes
WHERE [Fragmentation%] > 10
DECLARE @StartOffset INT
DECLARE @Length INT
SET @StartOffset = 0
SET @Length = 4000
WHILE (@StartOffset < LEN(@RebuildIndexesSQL))
BEGIN
PRINT SUBSTRING(@RebuildIndexesSQL, @StartOffset, @Length)
SET @StartOffset = @StartOffset + @Length
END
PRINT SUBSTRING(@RebuildIndexesSQL, @StartOffset, @Length)
EXECUTE sp_executesql @RebuildIndexesSQL
DROP TABLE #FragmentedIndexes
Также имейте в виду, что этот script может работать некоторое время и блокировать доступ к вашим таблицам. Если у вас нет выпусков Enterprise, SQL может блокировать таблицу при перестройке индекса. Это заблокирует все запросы к этой таблице с использованием индекса до завершения дефрагментации индекса. Таким образом, не рекомендуется выполнять восстановление индекса в рабочие часы только во время обслуживания. Если вы используете корпоративную редакцию, вы можете использовать опцию ONLINE = ON для дефрагментации индексов в Интернете. Это будет использовать больше места, но ваши таблицы не будут заблокированы/заблокированы во время операции дефрагментации.
Кричите, если вам нужна дополнительная информация.
ОБНОВЛЕНО:
Если вы выполняете этот запрос в меньшей базе данных, вы, вероятно, можете использовать параметр "ПОДРОБНЫЙ" в вызове sys.dm_db_index_physical_stats. Вероятно, это более подробное исследование индексов. В комментариях в комментариях также указывается, что на гораздо больших таблицах, вероятно, стоит сделать сканирование SAMPLED, так как это поможет сократить время, необходимое для сканирования индекса.
В случае, вы думали, чтобы избежать создания любых временных таблиц и разбора строки для создания списка строк SQL. Вот эффективный способ ее достижения:
USE databasename
GO
DECLARE @Queryresult NVARCHAR(4000)
SET @Queryresult=''
SELECT
@[email protected] + 'ALTER INDEX ' + QUOTENAME(i.name) + ' ON '
+ QUOTENAME('dbo') + '.'
+ QUOTENAME(OBJECT_NAME(i.OBJECT_ID)) + ' REBUILD;'
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ss
INNER JOIN sys.indexes i ON i.OBJECT_ID = ss.OBJECT_ID AND i.index_id = ss.index_id
INNER JOIN sys.objects o ON ss.object_id = o.object_id
WHERE ss.avg_fragmentation_in_percent > 50
AND ss.record_count > 0
AND o.is_ms_shipped = 0 --Excludes any objects created as a part of SQL Server installation
AND ss.index_id > 0 --Excludes heap indexes
EXEC sp_executesql @Queryresult
да, вы можете.
Вы можете получить фрагментированные индексы с помощью этого запроса:
SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') indexstats
INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID
AND i.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 20
и на основе результата просто создайте команду для их воссоздания
Я бы обернул все в хранимую процедуру и вызвал ее из задания SQL Server
FYI, 50% - очень большая фрагментация. Я бы пошел меньше.
Вы можете использовать sys.dm_db_index_physical_stats, чтобы получить информацию о фрагментации индекса (см. столбец avg_fragmentation_in_percent). Затем вы можете сделать индекс alter с предложением rebuild всякий раз, когда достигнут ваш порог.