Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
aiken
Aged Yak Warrior
525 Posts |
 Posted - 2002-10-28 : 20:23:02
|
| We're going to be upgrading the hardware in a SQL server in the near future. At this same time, we have the opportunity to rejigger the RAID array. As it stands, the array is:2 drives: system, swap (RAID 1)2 drives: transaction log (RAID 1)9 drives: SQL data (RAID 5)2 drives: hot spare...with the new hardware, we won't need those 2 drives for system/swap.Am I correct in thinking that moving them into the existing 9 drive array will provide only negligable benefits (22% read performance throughput, no significant write performance improvement)?If so, am I also correct in thinking that the best use of those 2 drives would be to put the transaction log into RAID 1+0 (4 drives total)?Any advice appreciated.Thanks-b |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2002-10-29 : 11:33:24
|
| Thanks for the link; it definitely helps.Our database is probably 80% read, 20% write... but the writes that happen are constant and amount to about a million transactions a day. I don't think we can sacrifice all of the space we'd lose by making a 10-drive raid 10 array, so I'm starting to think about using the two newly unused drives as a raid 1 array and using filegroups to put the write-intensive tables there.Can you comment on the write performance of a 2-disk raid 1 array versus a 9-disk raid 5 array?Thanks-Brooks |
 |
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2002-10-30 : 01:47:19
|
| I've been doing more research on this, and I've definitely convinced myself that it makes sense to move write-intensive tables to a seperate filegroup on a raid 1 array.My next question is this: for read-intensive tables, does it makes more sense to have the 9 disk raid 5 array that I've got now, or to split that up into a 5 disk and a 4 disk array, with one file on each? Some of my research indicates that that would cause SQL server to use an additional thread for IO, which this disk array can definitely handle. However, I've also seen indications that the old max_async_io setting can do more or less the same thing without introducing the complexity of two arrays and two files in the filegroup. Again, at this point the data in the raid 5 area will be 99% read.Thanks-b |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-30 : 08:33:34
|
| What you can do with two drive arrays is put the data on one filegroup and the indexes on the other on a separate drive/array. This allows index and data operations to run on separate I/O threads and they can run in parallel. You might even be able to use the space on the index drive to add some more indexes, especially if you add covering indexes for your most intensive queries. The server can access just the index and you could see huge improvement. You can also perform filegroup backups instead, and back up only the data files. This might also improve performance, especially if you do index rebuilds as part of your maintenance schedule.There are a number of other ways you can arrange data and indexes in filegroups too, this is just one suggestion.Edited by - robvolk on 10/30/2002 08:34:23 |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-10-31 : 16:36:04
|
quote:
I've been doing more research on this, and I've definitely convinced myself that it makes sense to move write-intensive tables to a seperate filegroup on a raid 1 array.
I don't think that's a good idea. RAID-1 is great for sequential access (e.g. log files), but not as good for random access (datafiles). I would most likely add the two disks you freed up to the RAID-5 and leave the data there. |
|
|
|
|
|
|
|
|
SQL disk configuration