Rebuilding indexes does not change the fragmentation % for nonclustered indexes

Question

For starters, I am no DBA and I am working on rebuilding the indexes.

I made use of the amazing TSQL script from msdn to alter index based onthe fragmente percent returned by dm_db_index_physical_stats and if the fragment percent is more than 30 then do a REBUILD or do a REORGANISE.

What I found out was, in the first iteration, there were 87 records which needed defrag.I ran the script and all the 87 indexes (clustered & nonclustered) were rebuilt or reindexed. When I got the stats from dm_db_index_physical_stats , there were still 27 records which needed defrag and all of theses were NON CLUSTERED Indexes. All the Clustered indexes were fixed.

No matter how many times I run the script to defrag these records, I still have the same indexes to be defraged and most of them with the same fragmentation %. Nothing seems to change after this.

Note: I did not perform any inserts/ updates/ deletes to the tables during these iterations. Still the Rebuild/reorganise did not result in any change.

More information: Using SQL 2008 Script as available in msdn http://msdn.microsoft.com/en-us/library/ms188917.aspx

Could you please explain why these 27 records of non clustered indexes are not being changed/ modified ?

Any help on this would be highly appreciated.

Nod

Answer 1

SQL Server will not rebuild indexes that are not large enough. Have a look a the fragment_count (that's one of the fields in the sys.dm_db_index_physical_stats view), it's probably quite low for those 27 indexes.