I am running an application that query the database a lot, around 500 queuries (insert/update/select) each second. I want to buy a dedicated server to run that application, there are mainly 3 tables (out of 20) that gets most of the queries (like 99% of them).

I am not really a hardware guy, so I was wondering if I can share with you the server I am interested in, and you'll tell me if it should be enough, if you can of course, by your estimates.

Must of the information goes into/out of the db database is int/decimals.

One of the tables should contain around 50,000 records, another table should contain around 500,000 records, and the biggest table should contain more then a million.

I know these numbers are not THAT big, but I don't really know what to expect to, I am running my application on a smaller server now and it gets high CPU cause of all the transactions. so my new server is:

  • Server: Dell PowerEdge R710
  • CPU: 2x Intel Quad Core E5504
  • Harddisk: 2x500GB SATA2
  • RAM: 16GB DDR3
  • Software: Microsoft SQL Server 2008 Web Edition (2 Processor)
  • OS: Windows Server 2008 R2 Web (2 CPU)

Do you think it will be enough?

migrated from stackoverflow.com Jan 28 '12 at 20:13

This question came from our site for professional and enthusiast programmers.

  • 2
    Buy more RAM. A laptop has 16 GB. – Remus Rusanu Jan 28 '12 at 19:45
  • 1
    I agree with Remus Rusanu (love your Service Broker posts!) buy more RAM. You really should be measuring why your current resources are being consumed. Usually on throughput, you will be constrained by netowrk, disk and then ram. Is SQL Server using using all your CPU? For what? Sorting? With SQL Server performance, measure, measure, measure! – Phil Bolduc Jan 28 '12 at 19:55
  • I am interested how many ram should he buy?Another 16GB or what? – Joro Jan 28 '12 at 19:59
  • I agree with "buy more ram advice". This simply depend of your database size. Just have ram size to store the whole database into it as cache (yes, Windows is very dummy when it comes to cache, but you choose it). Say your database files are 10 Gb big, then 16 Gb or okay. – Gregory MOUSSAT Jan 28 '12 at 20:23
  • How much RAM? As much as it fits in the MB, using as large memory modules as you can afford. – Remus Rusanu Jan 29 '12 at 1:39

500 operations per second might be a lot or might be nothing. It really depends on the complexity of your data and the complexity of the data's processing.

Performancewise, it is a huge difference if a query on a million-row-table is able to use indexes meaningfully or if it will be a full table scan. Just as it is a difference if the table fits into memory or has to be read off disk. Just as it is a difference if you are regularly creating index splits with your INSERT statements. Just as it is ...

I could go on like this for a couple of more lines, but to make a long story short: build a test system, feed with data, do benchmarks with real load, tune, re-benchmark and estimate the needed scale-up through extrapolation and over-engineering by your personal comfort factor.

  • In addition to that, I'd check out why it is taking up so much CPU, before I go and buy a new server, that might not be needed at all. Find the bottleneck before you do anything, just using high CPU doesn't mean the server is too small. As syneticon-dj said, especially not being able to use indexes can be a hard on the performance of any database server. – lsmooth Jan 29 '12 at 0:11

If I could fit my DB in ram I would buy the ram. I believe SQL Server does a good job of managing its cache when running the 64 bit version.
In addition to having enough ram I suggest DO NOT use two drives. Think about the action when SQL will harden the log file to the disk drive. If the disk drive is busy with a OS operation SQL will have to wait.
Consider buying two drives to mirror for the os and at lease 2X drives for a morror or raid 10 for SQL.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy