Friday, January 21, 2011

sql server 2008 scalability vs budget

I need to buy new server dedicated for Navision 2009 database.
It's 50GB large and growing around 10GB a year.
16 user connected all day
System: Windows 2003 R2 Standard 64-bit
SQL Server 2008

Because of budget I must choose between:

  1. Quad Core Xeon 3,2 GHz and 2 GB RAM
  2. Quad Core Xeon 2 GHz and 4 or 6 GB of RAM

Which should I choose?
Which will give me better performance: more RAM or more CPU speed?

  • More RAM in SQL should give you an increased performance as long as it is allocated to the SQL server in question, SQL Server should "seize" a lot of that RAM on the startup of the service.

    Unless you are doing a lot of computations on the recordsets returned then having excess CPU will not increase SQL Server's performance. I would recommend the second option with 6GB of RAM. If you use parameterised queries this should allow a lot more of the queries that are executed more often to have their result sets stored in RAM rather than having to be "extracted" from disk.

    Of course, RAM or CPU is not the only bottleneck in SQL server, look at your disk layout and RAID levels, please see http://serverfault.com/questions/118767/standard-database-backup-procedures/119123#119123 for another post on here about how I have configured my servers, this is by no means expert advice but I have found it to be performant in my environment.

    Make sure to do regular backups and remember it's not a valid backup until you've tested your restore!

    jl : Dan makesa good point regarding the disk subsystem. Too many people focus on the front end and ignore the disk subsystem. SQLIO and SQLIOSim can help give some measure of behavior. The first is to put more of a pure load on it, the second simulates SQL Server IO patterns.
    Dan : +1 for actually mentioning tools to test this with!
    SeeR : My Data and Log Disks are "Express IBM 146 GB 2.5in SFF Slim-HS 10K 6Gbps SAS HDD" so I hope they are pretty good. Your backups are similiar to mine - full once a day and log every hour. Thanks for clearing my dilemma about RAM vs CPU. Once on twitter I saw that @codinghorror wrote about how he was suprised when he saw how speed grows when he is changing processors on stackoverflow servers. This is why I asked this question.
    From Dan

0 comments:

Post a Comment