Index will not defragment down any further

Question

Index will not defragment down any further

pgraves

Ten Centuries

Points: 1105
More actions
September 3, 2007 at 10:24 am

#70663

Hi all,
I have a few indexes showing around 30% fragmentation. Regardless of whether I use an ALTER INDEX with reorganize or rebuild, I cannot get the fragmentation down any further.
I have even scripted off the index and manually dropped and recreated it and the fragmentation is still the same.
It's not a big problem, I am more curious as to why this is.
If anyone can shed light on any possible reasons for this I would appreciate hearing them.
The only thing I haven't tried is to defrag at the OS disk level. Could this be the cause?
Thanks,
Paul

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736260 More actions · Answer 1

How much free space do you have in you database v the size of this table? Maybe there's no enough extents in a row to build the table. You might try growing the database a little and then rebuild the indexes.

pgraves Ten Centuries Points: 1105 More actions · Answer 2

Steve, The database is about 5GB in size and sits on a 100GB drive. It can use all of the drive and so the data file is about 6% full.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

With a 5gb data file that is only 6% full, you could use some shrinking. The overkill of disk space can actually hurt your performance a bit because the OS can put parts of your database really far away from each other.

But - on to your issue. Steve is taking about making sure your database actually has enough space in one location for your table. SQL 7 fragments on disk and does not do a good job of cleaning this up or giving disk space back to the OS (at least not a good job compared to SQL 2000 and SQL 2005). You can see this with a free utility on http://www.sqlinternalsviewer.com that will show you where things are. Even with only 6% space used in the data file, you may have lots of little fragments from many tables and indexes everywhere.

If it is really concerning you, you could create a new data file, grow it enough to fit all of your data, and then move everything into the new file. This will ensure you have a clean slate and indexes all have enough free space to be created.