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 |
|
tfountain
Constraint Violating Yak Guru
491 Posts |
 Posted - 2008-09-22 : 12:59:11
|
| What are viable options for allocating disk resources for my database? We currently have a setup where our data storage is on an external storage device. This device has several RAID-5 arrays (to store data) and several RAID-1 arrays (to store logs). However we have 1 data file and 1 log file per database (currently 14 databases). However, we do not have 14 RAID-5 arrays. Nor do we have 14 RAID-1 arrays. Several of our databases share the same RAID array. Lately I this has been causing some IO contention with multiple databases being hit at the same time during peak periods.I have mapped out a structure putting these data files on their own RAID-5 arrays but this would exceed the number of physical drives the 3 shelves gives us (and 3 shelves is the cap). Lets say we have 10 RAID-5 devices. Is there any benefit to making the primary file group comprise of 10 physical files, with one file on each RAID-5 array? This would be across all of our databases. Any cons? |
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2008-09-24 : 19:53:28
|
| No opinions? |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-09-24 : 22:24:36
|
quote:
Originally posted by tfountain
What are viable options for allocating disk resources for my database? We currently have a setup where our data storage is on an external storage device. This device has several RAID-5 arrays (to store data) and several RAID-1 arrays (to store logs). However we have 1 data file and 1 log file per database (currently 14 databases). However, we do not have 14 RAID-5 arrays. Nor do we have 14 RAID-1 arrays. Several of our databases share the same RAID array. Lately I this has been causing some IO contention with multiple databases being hit at the same time during peak periods.I have mapped out a structure putting these data files on their own RAID-5 arrays but this would exceed the number of physical drives the 3 shelves gives us (and 3 shelves is the cap). Lets say we have 10 RAID-5 devices. Is there any benefit to making the primary file group comprise of 10 physical files, with one file on each RAID-5 array? This would be across all of our databases. Any cons?
Rather it would be better to make Secondary FG and spread files accross RAID-5 to reduce I/O contention for this capacity planning as Primary FG also stores system objects by default.Also are you putting Log files and TEMPDB in separate Disk. |
|
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2008-09-26 : 10:26:59
|
| Currently, log files are on a separate disk (RAID 1). The tempdb however is not. But the proposal I am working up actually will include splitting the tempdb datafile into as many files as physical cores the server has, and spreading those across different disks. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-09-26 : 10:28:44
|
| That is good idea to reduce I/O contention on TEMPDB. |
|
|
|
|
|
|
Pros and cons of disk resource options