May 26, 2004 at 8:35 am
I have a 12 gig db. At any given time, only half the tables are in use. So, effectively it's only a 6 gig db. The SQL server is a 2 cpu HT 2.8 ghz, 170 gig RAID 5. We only have a c: and d: drive setup on it. The overall stats like disk and cpu remain low. I reorg the indexes once a week and updatestats daily before production starts up. Fortunately, the disk fragmentation looks ok.
The db has been around about 5 years and started out at 5 gig initially. Should I consider moving tables to a new data file and try to split this thing up for future disk defrags to be done "easier"?
I also have another db that is 19 gig. The SQL Server is lower end P3 750mhz, 80 gig d: drive raid 5. I am seeing extremely bad fragmentation on the whole drive. I add about 3 gigs at a time to this db. I've run disk defrag with sql stopped twice now. We try to run reorgs monthly. Is this more of a candidate to be split up so disk defrag can do it's job better?
May 31, 2004 at 8:00 am
This was removed by the editor as SPAM
May 31, 2004 at 9:17 am
That's a good question as to whether the defrags at the OS level help. If you could preallocate teh space, then at there shouldn't be a problem at the OS level.
As far as multiple files, they might help, but SQL will still have to be stopped for the OS level defrag to work. Or at least comfortably work. So I'm not sure that multiple files gains you anything there. Is this SQL 2000? The reorgs should be able to be done online and that might help.
If you move some data to another file, are you moving part of a table or separate tables?
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
May 31, 2004 at 9:32 am
I've run a couple defrags at the os level with sql2k stopped. It defragged one smaller 800 meg db ok, but on the 22 gig db it wouldn't defrag the mdf. I tried a couple times and it immediately comes back with "defrag is done". I even have about 60 gig free on the free on the drive out of an 80 gig drive. I did a reorg on the indexes and it recovered 3 gig.
There's a couple tables with large 2 to 3 gig indexes. Is it worth it to create a new data file just for them? We are planning on doing some deleting (aka archiving), but there's really no date set for that. The process (data warehouse) used to run 5 hours, now it's running upwards of 8 to 12 hours (hence the reasoning for defrags and reorgs).
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply