SQL Server Performance Forum – Threads Archive
New Server: Price Performance Considerations
As a new member, I offer thanks for all of the existing discussions; they are very interesting and helpful. I have about 5 years of moderate SQL Server development and administration experience. Along with a few friends, I am undertaking a development project somewhat equivalent to ‘a garage-based start-up.’ We will be utilizing Windows 2003 Server / SQL Server 2000 for some heavy analytical work based on complex stored procedures; as you probably would guess, the procedures involve a lot of reads and writes. The data volumes will not be very large, but the processing will be intense. No outside users will be hitting the box. We are getting ready to purchase a system for the project, and I’d like some feedback on my plans. We are doing this project ‘on the cheap,’ and our key decision factors are 1) low hardware cost and 2) high performance. High availability is desired but far less important than cost and performance. I’m thinking of building a system with dual processors (maybe 2.8 GHz), around 2GB of RAM, and SATA RAID (Adaptec 1210SA and two 36GB WD Raptor 10K hard drives). Does this approach sound reasonable? Should I consider having a vendor build the indicated system? Am I missing any low hanging fruit? Are there other important considerations? Thanks in advance for any and all feedback.Relevance Over Precision
If performance is one of the keys, I suggest 15K disk drives.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Quick Quip: Have the vendor build the system:
– for the sake of quality (and a reasonable warranty period).
– in case you need to point a finger at somebody.
Nathan H.O.
Moderator
SQL-Server-Performance.com
I think, you must at least consider another 1 GB, of RAM, and, 256Mb in the controller of the adaptec, configured for write.
If you need high availability then you must spend $$$ and with low cost you may not get accurate high-availability solutions in hardware & software. 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.
For that configuration, buy 4 GB of RAM if you can afford it. RAM is cheap. That will give 2 to the OS and 2 to SQL Server. I’d be really wary about only having 2 drives also. You’re going to get hit really hard on the performance with only two. If you can afford four, go with that. A little 2 processor 1U box with four drives isn’t that expensive. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
I would also look at warranty and support. This is very important, servers break down, what are you going to do if yours does? Unfortunately it’s not at all cheap, but you’ve got to factor it in too if you are after high availability. You might buy all the redundant disks you like, but your main board might go and you’re going to need another one fast. Trouble is, good support can cost as much as the server. You may decide it’s worth taking the risk. But I must warn you, things frequently go wrong with our servers, which come from a range of suppliers. Tom Pullen
DBA, Oxfam GB
For SATA RAID you might find this interesting:
ftp://ftp.research.microsoft.com/pub/tr/tr-2003-70.pdf —
–Frank
http://www.insidesql.de
is hard to tell where your system will be bound (CPU, RAM, I/O) without knowing the nature of processing you run , however
a) RAM is relatively cheap and rarely hurts. 4 Gb of ram (3 for SQL Server ,1 for OS) is a good start
b) I/O can be bottleneck , get as many disks as you system can support and arrange them accordingly in RAID level suitable for you.
c) Depending on how much downtime can you tolerate, decide on whether or not to invest in support/warranty. as for hardware, we had good luck with DL servers from HP, relatively inexpensive, easy to manage and perform well. simas
keep in mind most SATA drives are rated for 2 year life at 20% duty cycle,
most SCSI/FC disks are rated for 5 years at 100%.
i would budget for (desktop) disk replacement once or twice per year, server drives should last the full 5 years, but replacement at the 3-4 year point might be helpful
Since you’re not able to tell what the exact load will be, remeber to buy a machine that you will be able to scale up easily. Leave some empty slots (hope this is a right word), so you can add a CPU or some RAM, if you need them. — Marek Grzenkowicz
Rereading the specs you give: I propose this: Get another machine ( a desktop, for Backup purposes) (and make your backup there, if its posible
use simple recover model) Raid-0, (the 2 disks, the more the better(ideally 4),
you must have the performance counters on, to get an idea), forget about raid-5,1,0+1, i think you can stop and recover from a disk crash (its a true guess ?) Get at least 256MB, of cache in the Adaptec, configured for write, the read will be managed by the Sql Server cache. if you can define the stripe size, put it at 8k. Stop all innecesary services, in this server, dont oversize your files, defragment once a week,
get the RIGHT drivers, for the disk and adaptec, motherboard, network, etc, and try to tweak the "disk queue", normally windows put a "limited" secure queue, in new disks a queue of 16 is not a sin, but must review the model documentation. To get this processors busy, i think you will need the 4GB RAM, and please, review your SQL’s.
quote:Originally posted by FrankKalis
For SATA RAID you might find this interesting:
ftp://ftp.research.microsoft.com/pub/tr/tr-2003-70.pdf
I read this article while I was on the train this morning and one thing really amazed me.
Please go to this topic and tell me what you think.
—
Marek Grzenkowiczftp://ftp.research.microsoft.com/pub/tr/tr-2003-70.pdf
]]>