How should I estimate hardware requirements for SQL Server 2005 database?

Question

We're being asked to spec out production database hardware for an ASP.NET web application that hasn't been built yet.

The specs we need to determine are:

• Database CPU
• Database I/O
• Database RAM

Here are the metrics I'm currently looking at:

• Estimated number of future hits to website - based on current IIS logs.
• Estimated worst-case peak loads to website.
• Estimated number of DB queries per page, on average.
• Number of servers in web farm that will be hitting database.
• Cache polling traffic from database (using SqlCacheDependency).
• Estimated data cache misses.
• Estimated number of daily database transactions.
• Maximum acceptable page render time.

Any other metrics we should be taking into account?

Also, once we have all those metrics in place, how do they translate into hardware requirements?

Answer 1

What I am missing is a measure for the needed / required / defined level of reliability.

While you could probably spec out a big honking machine to handle all the load, depending on your reliabiltiy requirements, you might rather want to invest in smaller, but multiple machines, and into safer disk subsystems (RAID 5).

Marc

Answer 2

In my opinion, estimating hardware for an application that hasn't been built and designed yet is more of a political issue than a scientific issue. By the time you finish the project, current hardware capability and their price, functional requirements, expected number of concurrent users, external systems and all other things will change and this change is beyond your control. However this question comes up very often since you need to put numbers in a proposal or provide a report to your manager. If it is a proposal, what you are trying to accomplish is to come up with a spec that can support the proposed sofware system. The only trick is to propose a system that will not increase your cost for competiteveness while not puting yourself at the risk of a low performance system.

Answer 3

What I have been doing lately for server planning is using some free tools that HP provides, which are collectively referred to as the "server sizers". These are great tools because they figure out the optimal type of RAID to use, and the correct number of disk spindles to handle the load (very important when planning for a good DB server) and memory processor etc. I've provided the link below I hope this helps.

http://h71019.www7.hp.com/ActiveAnswers/cache/70729-0-0-225-121.html?jumpid=reg_R1002_USEN

Answer 4

If you can characterize your current workload in terms of hits to pages, then you can then: 1) calculate the typical type of query that will be done for each page 2) using the above 2 pieces of information, estimate the workload on the database server

You also need to determine your performance requirements - what is the max and average response time you want for your website?

Given the workload, and performance requirements, you can then calculate capacity. The best way to make this estimate is to use some existing hardware, run a simulated database workload on a database on that hardware, and then extrapolate your hardware requirements based on your data from the first steps.