1

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!

Blather
  • 1,118
  • 5
  • 15
  • 25
  • I also get this same thing happening. But when I use Sql Management studio to check the fragmentation (using the index properties) it shows the correct value of 0.24% fragmentation. When I use an approach like above it tells me it's 100% fragmented. – Kelly Jun 07 '12 at 16:53
  • Have you reorganized the index? – Jack Marchetti Nov 17 '09 at 16:29
  • Yes, I have also ran: ALTER INDEX ALL ON [dbo].[FragmentedTable] REORGANIZE – Blather Nov 17 '09 at 16:31
  • In terms of addressing fragmentation, a rebuild would encompass everything a reorganize addresses and more. – boydc7 Nov 17 '09 at 16:32

4 Answers4

1

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.

Darryl Peterson
  • 2,250
  • 1
  • 16
  • 13
0

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.

boydc7
  • 4,593
  • 20
  • 17
0

Thoughts..

  • Is it partitioned? REBUILD PARTITION = partition_number
  • Need LOB_COMPACTION = ON?
  • Do you have a clustered index?
gbn
  • 422,506
  • 82
  • 585
  • 676
  • At this time it is not partitions. I reran my query with LOB_COMPACTION = ON and nothing changed. I have both clustered and nonclustered indexes that are suffering from the fragmentation issues I described. Thanks for your response! – Blather Nov 17 '09 at 16:49
0

There have been two methods for removing fragmentation. The two methods are necessary because they have quite different characteristics.

Describes the differences between the three methods in SQL Server 2005 onward:

ALTER INDEX … REORGANIZE (the new DBCC INDEXDEFRAG)

ALTER INDEX … REBUILD (the new DBCC DBREINDEX)

ALTER INDEX … REBUILD

For More Info
http://sqlnetcode.blogspot.com/2011/11/sql-server-methods-for-removing.html

gngolakia
  • 2,176
  • 1
  • 18
  • 13