Performance Tuning SQL Server Hardware

Generally, it is better to run fewer SQL Server databases on more smaller servers than it is to run many databases on one larger server. Here are some reasons why:

* Purchasing several smaller SQL Servers may be less expensive than buying one huge SQL Server, although this may not seem logical to many people not familiar with server hardware. Generally, smaller servers can be purchased at commodity prices, while very large servers are special order and have a cost premium associated with them.

* In many cases, your current, older SQL Server may not be upgradeable, or if it is, it may not be cost-effective to upgrade it as compared to purchasing new physical servers.

* As physical servers get larger (more CPUs), there is more and more CPU overhead generated, which in effect reduces the overall performance of the server. Each additional CPU adds a decreasing amount of additional CPU power. Two 4-CPU servers are more efficient from a CPU perspective than a single 8-CPU server.

* Some databases need to be tuned differently than other databases. If all of your databases are located on a single server, then you can't take advantage of SQL Server-wide performance tuning techniques to tune each separate database as each database will have to share all SQL Server-wide performance tuning settings.

* If the single, large server goes down, then all the databases go down. If the databases are on separate servers, then fewer databases go down.

* Most SQL Server-based applications grow over time. If each database is located on its own server, then it is easier to add incremental hardware (such as RAM) to the servers that need it. On a large server, you may find that you can't expand beyond a certain point.

[6.5, 7.0, 2000, 2005]