SQL Server Performance Forum – Threads Archive
RAM or HD?
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.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
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.
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.
]]>