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 |
|
kioteh
Starting Member
16 Posts |
 Posted - 2011-09-19 : 09:49:14
|
| My company has mandated one large RAID 5 array is to be used with my new SQL Server setup. We have 1TB allocated for the database files. I am being told that we should divide this space up into 700GB for data, 150GB for log, and 150GB for tempdb and am being told this will improve performance. I've always been under the assumption that the only way segregating data/log files like this would be if you had separate RAID arrays. Am I wrong here? If it's one large 1TB RAID 5 array and you're just logically separating them is there a performance benefit here? |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-19 : 10:17:33
|
| "am being told this will improve performance"I doubt it, but most certainly it will not improve performance compared to splitting it onto separate physical drives.So my money is on putting them on DIFFERENT spindles to improve performance.And if you did that you could choose drive configurations which were more suitable.Data needs random access performanceLogs, Backups need sequential access performance (probably works out cheaper than RAID is you only need a pair of drives)We've had a single-drive-failure on RAID5 which has taken the database with in (when supposedly the Parity would prevent that). With Data file and Backups on separate spindles then less chance of total wipeout if that happens.Controller fault on a single RAID subsystem could wreck all WRITES - same fault on just one controller / spindle means that either Data or Log is hosed, but not both. If Data is hosed you would normally expect to get 100% data recovery with no loss by restoring from latest full backup, and then all the TLog backups since (having taken a TAIL backup before starting to restore). That won't be any good if they are all on one drive and either inaccessible or suffered the same WRITE fault on the (single) controller. |
 |
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2011-09-19 : 15:39:37
|
| Hello OP,It is difficult to say whether it will be a performance improvement over any previous configurations (not knowing what they were/are). But in general the more spindles, the better performance. RAID 5 is performant for read only or mostly read DB's, but it is performance penalizing on DB's where frequent writes occur. RAID 10 is preferred over RAID 5.As Kristen mentions, Logs are sequential read/write and, as such, the DB will benefit from Logs on their own seperate physical set (RAID 1 preferred) and best performing when each log is isolated on its own physical partition.Lots of factors go into storage performance and optimization and each will be based on reliability, cost and need. Here are a few links which looked like good information;http://technet.microsoft.com/en-us/library/cc966414.aspxhttp://blogs.technet.com/b/josebda/archive/2009/03/27/sql-server-2008-storage.aspxhttp://technet.microsoft.com/en-us/library/cc966534.aspxhttp://www.sql-server-performance.com/2005/hardware-performance-optimization/2/HTH. |
|
|
|
|
|
|
|
|
Data File Segregation on RAID 5