SQL Server Performance

RAM or HD?

Discussion in 'SQL Server 2005 Performance Tuning for Hardware' started by markmin, Apr 12, 2006.

  1. markmin New Member

    I'm looking for optimal performance on a 30GB DB. If I install 32GB RAM on a 4 processor DL585 with SQL Server 2005, can I avoid spending big bucks for a disk sub-system? I'm hoping that the database will be cached so that disk access becomes unimportant.
  2. joechang New Member

    you don't have to spend big bucks for good disk performance, especially if you avoid taking advice from some one trying to sell storage (more so for a certain type of storage product with 90%+ gross margins)

    caching is good but,
    even if all data was in memory, you still have to checkpoint dirty pages, backup the database (which forces a disk read even it is in memory), backup the transaction log.
    plus you will also need memory for other things, not just data cache.

    see my article on storage config
    http://www.sql-server-performance.com/jc_system_storage_configuration.asp

  3. markmin New Member

    Thanks for the response. You pointed out the need for write performance, which to me is more difficult to factor than read performance. I'm not sure what happens when writes are delayed.

    Here's my plan at this point...

    - OS placed on internal drives (4x36GB 10K)
    - 1 Dual MSA30 attached to dual U320 Controller (log files on first channel with RAID 10 / DB on second channel with RAID5). All drives 36GB 15K.
    - Then, for back-up-to-disk, 1 Single channel MSA30 attached to external SCSI port on DL585 with 10 x 146GB Drives, RAID5

    Read performance is most important. I'm hoping that my wimpy disk read I/0 is made mostly irrelevant by the 32GB of internal memory, maybe going to 48GB.

    Also, I really enjoyed the article. Without this type of analysis, poor saps like me spend lots of money without knowing what we're getting in terms for performance.



  4. joechang New Member

    be sure to fully read the discussion with agw on the DL585 & storage

    my very strong preference is to spread data, temp & backup across a common pool of drives, ie, no dedicated drives for backup, only the heavy use log files get separate disks.

    (when SAS system that can accomodate big 3.5" SATA drive, i will revise this)

    so for a DL585, my preference is to have at minimum two dual channel MSA30, two dual channel raid controllers on separate PCI-X busses (two 100MHz PCI-X slots can be on one bus)
    even better is 4 dual channel MSA30,

    if you want a mix of small capacity 15K and bigger 10K drives, that fine,
    make two logical drives on the 15K, one for data, second for temp
    make 3 logical drives on the 10K, for data, temp, & backup

    you will have somewhat uneven backup performance, but i think that is tolerable

    final item: 36G 10K drives should nolonger be sold, so 72GB is min.
    i also noticed that HP pricing on 10K drives favors the 146GB model, so if you have 4 x 146GB 10K internal,
    you can have the OS, and either backup or logs on it.

Share This Page