Sql-server – How should I estimate hardware requirements for SQL Server 2005 database

Architecturehardwareperformanceperformance-estimationsql server

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?

Best Answer

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

Related Solutions

Sql – How to return only the Date from a SQL Server DateTime datatype

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))

for example

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

gives me

2008-09-22 00:00:00.000

Pros:

No varchar<->datetime conversions required
No need to think about locale

Sql-server – How to check if a column exists in a SQL Server table

SQL Server 2005 onwards:

IF EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = N'columnName'
          AND Object_ID = Object_ID(N'schemaName.tableName'))
BEGIN
    -- Column Exists
END

Martin Smith's version is shorter:

IF COL_LENGTH('schemaName.tableName', 'columnName') IS NOT NULL
BEGIN
    -- Column Exists
END

Best Answer

Related Solutions

Sql – How to return only the Date from a SQL Server DateTime datatype

Sql-server – How to check if a column exists in a SQL Server table

Related Topic