Rebuilding large SQL indexes isn't changing the fragmentation percentage

Question

I have a large database running on SQL Server 2005. I query the DMV sys.dm_db_index_physical_stats to get index fragmentation information.

I was shocked to see avg_fragment_size_in_percent reporting a large amount of indexes with very bad fragmentation levels (99% for a lot of them). These are on tables with non-trivial page counts (500+ pages on the smallest ones).

I proceded to run the following on the worst offenders:

ALTER INDEX ALL ON myTable REBUILD WITH(ONLINE = ON)

I then re-queried sys.dm_db_index_physical_stats, however it reports exactly the same fragmentation levels before and after the index REBUILD.

Is this information cached, or am I doing something wrong?

Answer 1

Are you sure you're getting the fragmentation of indexes? There are more objects than just indexes in sys.dm_db_index_physical_stats - for example heaps (tables without clustered indexes) are in there as well.

Try inner joining on sys.indexes to limit yourself to Indexes.

Answer 2

Everything you ever wanted to know about SQL Fragmentation and how to deal with it can be found at SQLFool

She has an amazing script for automating index defragmentation and is very helpful if you have questions.

Answer 3

You might want to check into the FILLFACTOR parameter for indices - this can strongly influence your index fragmentation.

On a "busy" index with lots of insert, updates, delete, you should typically go for a fillfactor of between 70 and 90 percent - depending on how many varchar() fields you have.

On an index (especially clustered index) which is ever-increasing (INT IDENTITY, typically), you can use FILLFACTOR 0 (or 100 - both means the same) and you'll only see fragmentation from deletes.

To specify FILLFACTOR, use this syntax:

ALTER INDEX (indexname) 
  ON myTable REBUILD 
  WITH (ONLINE = ON, FILLFACTOR = 80)

Here's some more info on fillfactors and the influence on performance:

• Create and modify SQL Server indexes properly
• Index fill factor and performance considerations
• Who cares about fill factors
• Fill factor option on MSDN

Hope this helps a bit!

Marc