July 1, 2008 at 9:33 am
I have inherited a system which has the current configuration of drives.
Drive Space Raid Current configuration Optimal A ? Optimal B ?
====== ====== ====== ====================== ============== ==============
C: 100 G No Raid OS OS OS
F: 100 G Raid-10 Data, Logs, TempDB, Indexes TempDB TempDB
G: 100 G Raid-10 Logs Data
H: 200 G Raid-5 Data Logs
I: 200 G Raid-5 Backups Indexes, Backups Indexes, Backups
Is it more important to get the TempDB and Logs on R10 versus having the data and indexes on R10? I'm wondering if there would be any degradation of performance if I move the data and indexes to R5.
Thoughts
July 1, 2008 at 11:20 am
The first question is, are these databases OLTP or OLAP?
For transactional databases (OLTP), with lots of updates, inserts and deletes, having the data files on RAID-1/10/01 is almost always best. RAID-5 generates too much overhead in generating check digits and so on.
For warehouse databases (OLAP), RAID-5 is often just fine. Writes are infrequent and often quite large, so are efficient, so no hit from check digits.
Log files also almost always do better on a fast-write array (RAID-1/10/01). Speeds up transactions. Again, that's mainly going to matter in OLTP databases.
The key thing is generally to separate data files from log files, and both of those from backups. That gets you the best performance and is critical for disaster recovery. (If you lose the the backup files, you back up the log and data files. If you lose the data files, you can recover from the backups and log files. And so on.)
Take those general rules, and work out what you need to do with your drives.
On my servers, I'd probably see about getting another 100 G drive, then have 3 RAID-1 arrays. OS and backups on one, data on the biggest one, log files on the last. Unless there's a really compelling reason to put tempdb on its own array, or split it up across arrays, I would generally consider it more important to split logs from data from backups.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 4, 2008 at 6:09 am
GSquared (7/1/2008)
Unless there's a really compelling reason to put tempdb on its own array, ...
I agree with GSquared. One addition to the quote: if you use a lot of temp-tables and/or sorting, etc. then putting the tempdb on its own array could benefit a lot regarding I/O performance.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply