Index should be rebuild when index
fragmentation is great than 40%.
Index should be reorganized when index
fragmentation is between 10% to 40%.
--Fragmentation Checking SQL
Statement
SELECT OBJECT_NAME(OBJECT_ID) as ObjectName,
index_id,index_type_desc,index_level,
avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count
FROM sys.dm_db_index_physical_stats
(DB_ID(N'DatabaseName'), NULL, NULL, NULL , NULL)
ORDER BY avg_fragmentation_in_percent DESC
avg_fragmentation_in_percent:
This is a percentage value that represents external fragmentation. For a
clustered table and leaf level of index pages, this is Logical fragmentation,
while for heap, this is Extent fragmentation. The lower this value, the better
it is. If this value is higher than 10%, some corrective action should be
taken.
avg_page_space_used_in_percent:
This is an average percentage use of pages that represents to internal
fragmentation. Higher the value, the better it is. If this value is lower than
75%, some corrective action should be taken.
---------- Rebuild
Index----------
USE DatabaseName
DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH
(FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO
---------- Reorganize
Index----------
USE DatabaseName
DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + '
REORGANIZE'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO