Check for index fragmentation and generate rebuild script

Userlevel 2
Badge +6


Indexes can get fragmented which causes the index to perform less.

The following script checks for fragmentation on indexes and provides the script to rebuild it.



SELECT as 'Schema'
, as 'Table'
, as 'Index'
,concat('ALTER INDEX ', , ' ON ', 'dbo.', ,' REBUILD;' ) as REBUILD_SCRIPT
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
JOIN sys.tables dbtables
on dbtables.[object_id] = indexstats.[object_id]
JOIN sys.schemas dbschemas
on dbtables.[schema_id] = dbschemas.[schema_id]
JOIN sys.indexes AS dbindexes
on dbindexes.[object_id] = indexstats.[object_id]
and indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
and indexstats.avg_fragmentation_in_percent > 10
and is not null
and indexstats.page_count > 25
ORDER BY indexstats.avg_fragmentation_in_percent desc


2 replies

Badge +1

I had received a sample from Luuk once in the past, this one makes a select statement with the REBUILD/REORGANIZE statements, plus updating the stats is also an advice to do after rebuilding... :

OBJECT_NAME(ind.object_id) AS TableName, AS IndexName,
indexstats.index_type_desc AS IndexType,
CASE WHEN indexstats.avg_fragmentation_in_percent BETWEEN 5 and 30
THEN 'ALTER INDEX '+ +' ON ' + OBJECT_NAME(ind.object_id) + ' REORGANIZE ;'
WHEN indexstats.avg_fragmentation_in_percent > 30
THEN'ALTER INDEX '+ +' ON ' + OBJECT_NAME(ind.object_id) + ' REBUILD ;'
, indexstats.page_count
, indexstats.record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 5
AND is not null
ORDER BY indexstats.avg_fragmentation_in_percent DESC

-- Eventueel Statistics updaten zodat indexen weer met correcte index-stats werken. (LET OP: Onderstaande sp update stats van hele database.)

--EXEC sp_updatestats;

Although I have to say that these days you can also use tsf_optimize in my opinion. But maybe someone from Product Innovation can cite this.

Userlevel 5
Badge +9

Why not use tsf_optimize for this? It reorganises indexes where the fragmentation is between 5% and 30%. And rebuilds indexes with a fragmentation above 30%. With a limitation that the index should have a minimum page count of 11. Afterwards it updates the statistics where necessary.