September 3, 2007 at 10:24 am
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
September 3, 2007 at 12:25 pm
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.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 4, 2007 at 3:15 am
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.
September 4, 2007 at 6:18 am
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply