Check for index fragmentation and generate rebuild script


Userlevel 2
Badge +6

Goal

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.

 

Solution

SELECT dbschemas.name as 'Schema'
,dbtables.name as 'Table'
,dbindexes.name as 'Index'
,indexstats.avg_fragmentation_in_percent
,indexstats.page_count
,concat('ALTER INDEX ', dbindexes.name , ' ON ', 'dbo.', dbtables.name ,' 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 dbindexes.name 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... :

     SELECT
OBJECT_NAME(ind.object_id) AS TableName,
ind.name AS IndexName,
indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent,
CASE WHEN indexstats.avg_fragmentation_in_percent BETWEEN 5 and 30
THEN 'ALTER INDEX '+ ind.name +' ON ' + OBJECT_NAME(ind.object_id) + ' REORGANIZE ;'
WHEN indexstats.avg_fragmentation_in_percent > 30
THEN'ALTER INDEX '+ ind.name +' ON ' + OBJECT_NAME(ind.object_id) + ' REBUILD ;'
end
, 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 ind.name 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. 

 

 

Reply