SQL Server Performance Forum – Threads Archive

RAID Config & SAS Config

Alright, sorry for so many questions in one post – but I have several things I’m trying to hash out. First, I’m building a new SQL box. My budget essentially allows for an HP DL380 G5. I’m thinking 8 SAS drives since I can get all eight in the main casing – although I may cough up more money and buy a storage works extension so I can have 8 15K SCSI drives instead. So, I have several questions… 1) If I go 15K U320 SCSI X 8 I will be using 3 channels on a RAID card with 256MB cache. My plan is to have three RAIDs: 2 drives RAID1, 2 drives RAID1, 4 drives RAID5. I’ve never actually built a server with more than one RAID per controller before. How likely am I to over-saturate the card with too many IO’s per second? Also, how likely am I to over-saturate the PCI bus for that matter? 2) If I go with SAS drives, the card I’m looking at doesn’t do channel – just ports. So, how do you setup different RAID’s with the SAS card? Will the RAID setup simply allow me to define port 0 & 1 = RAID1, port 2 & 3 = RAID1, port 4-7 = RAID5. Will it be that easy? Again, what is the standard IO’s per second saturation point for SAS. 3) Finally, I will have 3 RAID’s so I would like opinions on the best way to spread those out. Ideally I should separate:
1) OS/page file
2) Transaction logs
3) TempDB
4) Data Files
5) Heavy use non-clustered indexes. Unfortunately, I have 5 things to separate and only three buckets. Anyone have an opinion on what combo I should use? Thanks in advance! Ryan

---

you cannot saturate a good raid controller (HP Smart Array are good) on random IO
you can saturate on sequential, 3-4 drives per channel, at 250MB/sec per channel SAS probably will not saturate see my post (top in this section) on recommend hardware + storage
try to push for 2 full racks of external storage for the DL380G5, SAS preferred

---

Yes, I’ve seen your post – it’s very helpful. Unfortunately, the listed configs are way above my budget. This server is currently running on a 4x15k U320 SCSI RAID5 array with everything on this one array. It is a dedicated SQL box (dedicated to one app..one function) so I’m lucky to get the number of drives I’ve listed above. Can you comment on the 3 separate RAID arrays over the 8 internal SAS drives? Is this possible/fairly easy? Also, can you comment on the I/O distribution/separation I listed above? If you had 5 workloads to separate:
1) OS/pagefile.sys
2) Tempdb
3) Transaction Logs
4) Heaps & Clustered Indexes
5) Non Clsutered Indexes …but only 3 RAID arrays, which would you (or any other poster) put where?

---

Joe, you’ve made an interesting statement in your hardware recommendation post that I’ve quoted below. I think many other DBA’s would disagree since we are often trying to separate sequential from random I/O – as well as achieve parallel disk operations (multiple RAID arrays working on the same I/O requests). However, I’ve been thinking about this very point. Even on a SQL Standard box I never go less than 4GB. This way SQL gets its full 2GB and the OS gets its own 2GB for OS operations, binaries, etc. The point here is that I expect there is VERY would be very little I/O activity on a dedicated OS/pagefile.sys/binary mirror. This very much feels like I might be wasting 400 IOPS or 160MB/s of total I/O throughput. So, in addition to commenting on my post listing the 5 different I/O workloads across 3 RAID arrays (I am still interested in input on that one), please also comment on: (for 8 SAS internal drives)
[email protected] = transaction logs
[email protected] = OS/pagefile/binaries/tempdb/data/indexes Thanks. RH

---

If you are not in critical situation, wait for Joe answer.
Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS” with no warranties for accuracy.

---

many people seem to be big on rules, without considering the circumstances that the rule were developed for many of the rules for database and disks were developed long ago for different circumstances,
including unix file system limits, lack of raid controller etc everything i have seems to indicate that most database disk loads are bursty
ie, there will be a burst of random, a burst of sequential etc
there is usually not a fixed distribution,
and certainly it can be said that the tempdb load is not guaranteed to be sequential
(assuming you are aware that you can put indexes on temp tables) for this case, i would want all disks to be available to drive the load of the moment also, if there is significant disk activity to OS or page, then there is something wrong with your configuration

---

Thanks. I think we are in agreement. RH

---

]]>