SQL Server Performance Forum – Threads Archive
SAN SQL ConfigurationI am working on a plan to split out our current production database to our new SAN (EMC CX3-20). We are currently on an old HP SAN that is providing us a disk bottleneck. With this new SAN we are going to have the ability to break out our database files to different RAID groups. Our current production database is composed of one file at 280 GB in size with 205 GB being used. Our indexes take up about 65 GB of that space. Our production log is about 15 GB that gets about half used on average. Our tempdb is about 8 GB in size. I am have read through some of the posts and would like to get your thoughts on this proposed setup. tempdb on RAID 10 4 73 GB 15k drives
Prod log on RAID 10 4 73 GB 15k drives
Prod data on RAID 5 5 73 GB 15k drives
Prod index on RAID 10 4 73 GB 15k drives How can I tell if I would get enough I/O with this proposed config compared to our current one? Any help on this would be greatly appreciated. Thanks. Tom
try to get to 28-30 dedicated physical disks, other than that, try
4 disks for logs
all other disks shared for the other 3 functions.
this could be 1 or more arrays simply split the data into multiple files for each array if you want, for each array, create 3 logical partitions
1 for data, 1 for index, 1 for tempp the idea is that you cannot perfectly balance data,index & temp
so make all disks available when a surge load is required for any
Even if you see members with high posts, do what Joe said.
SQL-Server-Performance.com All in Love is Fair
All postings are provided â€œAS ISâ€ with no warranties for accuracy.
Thanks guys. I can use this info when I talk with EMC next week about configuration. Tom
see post below for scripts to test disk io
Hi Tom, You could try using the Microsoft tool, SQLIO http://www.sql-server-performance.com/gv_stress_test_lessons_3.asp). I’ve found this to be a useful tool to have in my armoury, especially when it comes to SAN setups. When it comes to setup of the subsystem, from experience of working with the EMC CX and DMX ranges, and a couple of Hitachi’s, if you’re needing high capacity IO, then it comes down to the number of physical disks available, imho. It will also mean that you have to watch how the LUNs (MetaLUNs) are set up. If you have multiple LUNs sharing the same physical disk array, then this will not perform as well as a single LUN with its own dedicated array set, but you may be restricted by what you are given so you may want to experiment if you have time. I could go on, but I think this is all summed up in an excellent webcast by Microsoft’s David G Brown http://support.microsoft.com/default.aspx?kbid=895614), which covers IO performance issues with SANs, and the methods and tools you can use to help you optimise the subsystem you have. Hope this is useful. Rgds Ian (SQLBod) ———
"Chance favours the prepared mind" Louis Pastuer