SQL Server Performance

New 4x quad core setup, need Raid suggestions

Discussion in 'SQL Server 2005 Performance Tuning for Hardware' started by Tom Leykis, Dec 10, 2007.

  1. Tom Leykis New Member


    I have no experience with Sql 2005 and am finally jumping into from Sql2k.
    Setting up a brand new install for client of mine and would love to hear some recommendations for the correct set up.

    Server:
    Dell PowerEdge R900 4x Quad Core X7350 Xeon, 2.93GHz, 8M Cache
    32GB Memory
    2x 36G 15k SCSI
    Drive Shelf:
    Dell PowerVault MD3000
    2x 36G 15k SAS
    2x 73G 15k SAS
    11x 300G 15k SAS
    OS/DB:
    Win 2003 Ent.
    SQL 2005 Ent.
    Data is: 90% eCommerce & statistical data (35% reads, 65% writes)
    10% cross-selling data stored separately for faster reads (90% read, 10% write)

    The eCommerce non-text inventory & sales data is expected to grow 6M rows/10Gb per month (each) in 5 separate tables.
    The text columns will grow 25G per month. There is one table with varchar(75) and one with a text column, each will have its own Full Text Index.
    The cross-selling data has no more than 500k rows at any one time which can be lost and quickly created at any time, so Raid 5 is not needed.

    I'm thinking that the correct Raid config would be as follows:

    Server:
    OS 2x 36G Raid 1

    Drive Shelf:
    tempdb 2x 36G Raid 1
    translogs 2x 73G Raid 1
    eCommerce data 5x 300G Raid 5
    Cross-selling data 2x 300G Raid 1
    Statistical data 2x 300G Raid 1
    Hot Spare 2x 300G

    Another question I had was can I/should I do Raid 6 instead of 5?
    I'm thinking that I will eventually create a table(s) partition.

    Any help on this set up would be greatly appreciated.
  2. bertcord New Member

    Your system seems to be a bit mismatched between processing power and storage. I have yet to order an R900 yet but our current systems we order are2950s (dual socket Quad core) and attach 2-4 MD1000’s.I think you will find that you will be underpowered on storage.
    A couple of comments
    1. I think the R900 has 8 internal drives you should be utilizing those as well. (but then again you are using an MD300 so perhaps you are clusting?)
    2.You say trans logs…the benefit of transaction log on dedicated spindles comes when only a single transaction log is on the dedicated drives. This is because the transaction log is accessed sequentially. As soon as you add more than one TLOG the disk access becomes random
    3.Only use RAID5 for data that is predominately read only. You mention that you eCommerce data is 65% writes yet it is on RAID5.
    4.Do not set up dedicated spindle for parts of the DB unless you really understand your application and can prove that you will see benefits.
    5.No need for 2 hot spares, that is two wasted spindlesI would recommend getting 4 more 300G SAS drives for your array. I would create a single 14 drive RAID10 on the MD3000 (leaves you one for a hot spare as well) and store tempdb and database files here. I would create TLOG drive or drives on internal drives (use your 73 and 36 drives for this)
    Bert

Share This Page