SQL Server Performance

SAN SQL Configuration

Discussion in 'Performance Tuning for Hardware Configurations' started by tomjr93, Jul 13, 2006.

  1. tomjr93 New Member

    I 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
  2. joechang New Member

    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
  3. Luis Martin Moderator

    Even if you see members with high posts, do what Joe said.

    Luis Martin

    All in Love is Fair
    Stevie Wonder

    All postings are provided “AS IS” with no warranties for accuracy.

  4. tomjr93 New Member

    Thanks guys. I can use this info when I talk with EMC next week about configuration.

  5. joechang New Member

  6. SQLBod New Member

    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.


    Ian (SQLBod)

    "Chance favours the prepared mind" Louis Pastuer

Share This Page