SQL Server Performance

Sql server RAID levels

Discussion in 'Performance Tuning for Hardware Configurations' started by gordonmd, Nov 2, 2005.

  1. gordonmd New Member

    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 installation
    D: 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?






  2. satya Moderator

  3. gordonmd New Member

    thanks - would there be a performance gain if the tempdb (it is used heavily) was RAID 0?
  4. satya Moderator

    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.
  5. derrickleggett New Member

    What disk setup is your IT guy recommending? I'm assuming it's not the RAID 1 arrangement?

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  6. joechang New Member

    i am assuming he is planning on having 16 physical disks, made into 8 2x1 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

Share This Page