SQL Server Performance Forum – Threads Archive
Sql server RAID levels
Guys, I am looking for some help. the DBA and the IT manager are locking horns over this issue and I am loking for an out….. We have a SQL Server database that is about 1TB and is very write intensive. We are looking for a recommendation for implementing on a server. The DBA says: 16 Disk RAID 1 (i.e. 8 logical drives that are physically separate) spliting data into multiple FileGroups C: OS and SQL server installationD: Main data table files
E: Hot table files
F: Hot table files
G: Hot table files
I: Index filegroups and backup files
T: tempDB
L: Transaction log
IT manager say: keep data to 1 filegroup, let threading be taken care of by the RAID controller
Can anyone help?
I would go with first option than leaving it to RAID controller. http://www.raid-advisory.com/ &http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops6.mspx fyi. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
thanks – would there be a performance gain if the tempdb (it is used heavily) was RAID 0?
RAID 0 is the fastest type of RAID, but unlike most RAID implementations, it doesn’t provide fault tolerance. If one of the drives in a RAID 0 configuration fails, all the data is lost. Don’t use RAID 0 if you value your data. One of the Technet article refers :
The best disk I/O performance is achieved with RAID 0 (disk striping with no fault tolerance protection). Because RAID 0 provides no fault tolerance protection, it should never be used in a production environment, and it is not recommended for development environments. RAID 0 is typically used only for benchmarking or testing. RAID 5 is not as good a choice for tempdb – RAID 1 or 0+1 offer better performance. Raid 0, even though it does not provide fault tolerance, can be considered for tempdb because tempdb is rebuilt every time the database server is restarted. From my experience, RAID 0 provides the best RAID performance for tempdb with the least number of physical drives, but the main concern about using RAID 0 for tempdb in a production environment is that SQL Server availability might be compromised if any physical drive failure were to occur, including the drive used for tempdb. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
What disk setup is your IT guy recommending? I’m assuming it’s not the RAID 1 arrangement? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
i am assuming he is planning on having 16 physical disks, made into 8 2×1 RAID 1 arrays, as seen by the OS.
hence you have 8 groups as shown above. 1st, the OS, page and SQL Server are noloads as far as disks are concerned, so no need to dedicate disks to that. take 2 drives in RAID 1, make a 10-20GB partition for the OS, page, & program files, use the remainder for something else. i don’t have an issue with using multiple file groups, but rather than putting each file group on a separate set of physical disks, slice each of the data arrays into 3-6 partitions.
spread the main data, hot data, index, temp and backup across each of the above partition. basically, each of the above will not generate uniformly intense disk activity, and probably not at the same time, so whenever one or more are active, it gets most of the disks of course, 1 pair of drives for logs, not shared however, i not recommend 1TB on 16 drives. what was he thinking? 16 x 146GB drives?
try 28 74GB drives, 10K drives are good for DW apps,
be sure to spread across multiple SCSI channels and controllers, 4 minimum, preferrably 6
]]>