SQL Server 2005 Index Fragmentation

Question

Several keys in a SQL Server 2005 database I inherited have very high fragmentation percentages. When using the following SQL:

select  OBJECT_NAME(object_id), avg_fragmentation_in_percent, record_count, *
from    sys.dm_db_index_physical_stats (DB_ID(N'FragmentedDB'), NULL, NULL, NULL, 'Detailed') s

I see that several tables with fragmentation % between 50 and 99. These tables all have over 100,000 rows, some with 2,000,000+. I believe this is causing significant performance problems for our application, so I have tried to rebuild some of these indexes with the following sql:

ALTER INDEX ALL ON [dbo].[FragmentedTable] 
REBUILD WITH ( FILLFACTOR = 90, ONLINE = ON )

However, after I rebuild the index and look at the fragmentation % again, they are all unchanged. Is there something I am missing? I have done some searches on this topic but have come up empty so far.

Thanks!

Answer 1

Have you reorganized the index?

Answer 2

Couple of things come to mind - first would be if you are using multiple files to back the indexes in the table, next would be the parallelism you are seeing while rebuilding the index. Next, you mention you believe this is causing performance problems, have you verified this is the case? i.e. with some exceptions, fragmentation is typically a bigger problem for scans vs. seeks. For a complete detailed review of fragmentation, how to address, where to concentrate, and differences you see with different methods of repairing, see this series of blog posts.

Answer 3

Thoughts..

• Is it partitioned? REBUILD PARTITION = partition_number
• Need LOB_COMPACTION = ON?
• Do you have a clustered index?

Answer 4

Here is a good article on the topic.

Index Fragmentation Anxiety: a Doctor Speaks

Answer 5

You are using 'Detailed' with dm_db_index_physical_stats. This will show the non-leaf levels as well as the leaf levels of the indexes.

Is the fragmentation for the leaf levels (leaf_level = 0), for non-leaf levels (leaf_level > 0), or both?

If the fragmentation is at the non-leaf level, this is less of a problem, or no problem.

If you still want to get rid of all of the fragmentation, try adding PAD_INDEX.