SQL Server Hardware Performance Checklist

SQL Server Hardware Performance Checklist: "SQL Server Hardware Performance Checklist

Brad M. McGehee | Contributing Writer | 2004-12-10


Auditing SQL Server Hardware Is An Important Early Step

From this previous article, on using Performance Monitor, you may have identified some potential hardware bottlenecks that are negatively affecting your SQL Server's performance. In this section, we will take a look at each of the major components of a SQL Server's hardware, and examine what can be done to help maximize the performance of your hardware.

This portion of the audit will be divided into these major sections:

# CPU

# Memory

# Disk Storage

# Network Connectivity

# Misc.


As part of this audit, you will want to complete the above checklist. As you do, you may find out things about server you were not aware of.

CPU

Number of CPUs

This first point is obvious, the more CPUs your SQL Server has, the faster it can perform. The standard edition of SQL Server 2000 can support up to 4 CPUs. The Enterprise version can support up to 32 CPUs, depending on the OS used. Multiple CPUs can be effectively used by SQL Server to boost overall performance.

It is very difficult to estimate the number of CPUs any specific SQL Server-based application will need. This is because each application works differently and is used differently. Experienced DBAs often have a feel for what kind of CPU power an application might need, although until you really test your server's configuration under realistic conditions, it is hard to really know what is needed.

Because of the difficulty of selecting the appropriate numbers of CPUs to purchase of a SQL Server, you might want to consider the following rules of thumb:

# Purchase a server with as many CPUs as you can afford.


# If you can't do the above, then at least purchase a server that has room to expand its total number of CPUs. Almost all SQL Server"