SQL Server Performance

Windows 2003 Raid Setup

Discussion in 'SQL Server 2005 Performance Tuning for Hardware' started by rsalzmann, Sep 6, 2006.

  1. rsalzmann New Member

    First, thanks for all the helpful information at your site. I was looking over your articlehttp://www.sql-server-performance.com/q&a38.asp and see your recommendation if possible to “We locate the SQL Server binaries and swap file on its own 9GB RAID 1 array. We then locate the transaction log files on its own 18GB or larger RAID 1 array. And then we locate the data files on its own RAID 5 or RAID 10 array. This is an expensive way to allocate arrays, but we find that it offers both convenience and performance, along with a high degree of redundancy”

    Im hoping you can help me with a couple of questions? We have a new HP DL380 with 2 SCSI controllers and 6 15k drives. Dual processor with 4gb ram. We currently have 1 controller with 2 of the drives setup as Raid 1, with a C: for the OS and a D partition. The second SCSI controller has 4 drives setup as Raid10 setup as Drive E My thoughts are to leave only the OS on C, mainly in case we ever have an OS problem, we could reinstall to C; and have SQL Install and log files on D: partion. Then on E, I would put the SQL Database.

    1: What would you recommend as the optimal setup?
    2. Would it be better to make 3 Raid 1 setups using 2 disks each instead and if so put what where?
    3. Do you know if there is a performance hit if the scsi controller was setup with 2 or more arrays?

    Thanks in advance!
  2. satya Moderator

    I would go with your original thought to install OS on C and SQL binaries on D with SQL data on E.

    Before that I would like to ask you few questions:
    what is the database growth in next 3 to 5 years?
    is applciation configured for better optimization?
    can hardware cope up adding up more resources if required?
    how you managing data and is there any archiving in place?

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  3. bradmcgehee New Member

    If I understand this correctly, two of the drives will be mirrored for drive C, and four drives in a RAID 10 arrangement for drive E:. In this case I would recommend that the OS stay on c: and put both the data files and transaction logs on E. Each of the two arrays only need one logical drive, not two. Dividing database files and logs in different logical drives on the same array don't offer any performance benefit. A performance benefit from having data files and log files on separate drives is if they are different physical arrays, not logical drives.

    -----------------------------
    Brad M. McGehee, SQL Server MVP
  4. rsalzmann New Member

    quote:Originally posted by satya

    I would go with your original thought to install OS on C and SQL binaries on D with SQL data on E.

    Before that I would like to ask you few questions:
    what is the database growth in next 3 to 5 years?
    is applciation configured for better optimization?
    can hardware cope up adding up more resources if required?
    how you managing data and is there any archiving in place?

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.

    Thanks. Would installing Log files on D: then be appropriate or leave log files and database on the Raid 10 E?
    Database growth - not sure
    is applciation configured for better optimization? - Not sure what you mean?
    can hardware cope up adding up more resources if required? - Hardware is a HP DL380 Rack mount server.
    how you managing data and is there any archiving in place? - SQL is used for Navision database, no archiving currently done.

  5. rsalzmann New Member

    quote:Originally posted by bradmcgehee

    If I understand this correctly, two of the drives will be mirrored for drive C, and four drives in a RAID 10 arrangement for drive E:. In this case I would recommend that the OS stay on c: and put both the data files and transaction logs on E. Each of the two arrays only need one logical drive, not two. Dividing database files and logs in different logical drives on the same array don't offer any performance benefit. A performance benefit from having data files and log files on separate drives is if they are different physical arrays, not logical drives.

    -----------------------------
    Brad M. McGehee, SQL Server MVP

    Thanks, yes 2 of the drives are Raid1 on 1 scsi controller, which is one logical drive with a C and D partitions. The 4 other drives are Raid10 on a different SCSI controller with one logical drive and one partition E. So you statement "Dividing database files and logs in different logical drives on the same array don't offer any performance benefit. A performance benefit from having data files and log files on separate drives is if they are different physical arrays, not logical drives." OK, but i do have 2 different logical drives on 2 different array controllers. So, would you still have logs and database on E?


    Butif I did the 3 Raid 1 arrays, which would be 3 logicalB drives, one of the SCSI controllers would have to hold 2 of the logical drives. So, if I did this, and made those 2 logical Raid1 drives, C and D, and then the second controller one logical drive E, would that give me the best performance? I would put OS on C, SQL binaries and Database on D, and Log files on E??

Share This Page