Rebuilding indexes does not change the fragmentation % for nonclustered indexes.
- by Noddy
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
