October 25, 2002 at 8:39 am
I have inherited a database and am trying to determine how to cleanup the structure.
There are 3 databases with multiple data files split over several logical drives (c,e,f,h,i,j,k and l). All these logical drives sit on 3 RAID arrays with ONE controller card. Each array is split into several logical drives
One Controller Card
Array - Drives
A - C,E,F
B - H
C - I,J,K,L
I understand seperating the datafiles (and log files) on seperate drives increases performance.
Question 1: In this scenario where each RAID Array is on the same controller card, is there a performance benefit ?
Question 2: If yes to question 1, Does the performance benefit continue on to the logical drives that are created by seperating a particular array into 4 logical drives (as in C).
What I'd like to do to make my administration easier is to compine the 3rd array (c) into one logical drive. (mostly to make it easier to restore to another server that might not have the same drive configuration.)
I'd keep the current situation if I could control where tables are created within each data file, but unfortunately the application creates tables (e.Piphany) and they are all created in the primary partition....my backups would definetly be eaiser if I could control where tables are created.
October 25, 2002 at 9:56 am
Is this SQL Server 2000? If so, the restoring to other servers isn't a big deal. Can always attach files from whatever drive they are on.
As far as performance. From what I understand. there are separate threads used for accessing each filegroup, so this can get your performance.
Also, from Inside SQL Server 2000 (paraphrased), more filegroups gives you some flexibility for large databases. If you have a single 100GB file, then you need a 100GB space to restore it. If you break this up into 4 or 5 smaller files, then if you didn't have 100GB in one place, you could still make it work.
Not sure how beneficial this is, works well for me on a server with a 400GB db, but we also use separate physical arrays for the different drives. Separate logicals on the same physical, I would guess, doesn't give great performance increases.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
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
October 25, 2002 at 10:18 am
I wouldn't worry as much about RAID channels, the more the merrier yes, but that is second ary.
The real issue is the logical dives on a RAID set. Here you have I/O competing for the I/O time to what is realistically a single drive. If you have a DB and a Log on the same drive they will compete for I/O time etc.
Yes RAIDed, what raid level are you using ?
KlK, MCSE
KlK
October 26, 2002 at 8:26 am
Yes, I am using SQL 2000.
It is RAID 0 + 1.
Thanks for all the help so far. I may actually keep it the way it is, as I can't figure out how to combine everything back into one file group...without copying the data out exporting / importing. I do have the other RAID with 200gb on it, so I could detach the database, move it, attach it, rework the other raid into one logical volume, create a new database and copy everything back, doesn't sound like much fun...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply