Although heavy-duty hardware can help SQL Server’s performance, application and database design can play a greater part in overall performance than hardware. Keep this in mind, as throwing good money after bad on server hardware does not always fix SQL Server performance problems. [6.5, 7.0, 2000, 2005] Updated 8-5-2005
*****
Many people assume that the server hardware they work with is installed and configured correctly. This can be a costly misjudgment. Server hardware (and their drivers) are often installed incorrectly or misconfigured. While the hardware may continue to run in spite of problems, performance problems may lurk beneath the surface.
Always verify that the hardware and drivers are current and properly configured. This includes ensuring that the server’s BIOS and other proprietary on-board system files are up-to-date, and are kept up-to-date over time. Many vendors change their BIOS’s often to fix bugs, so you should periodically check for new updates.
I have seen cases where SCSI cards have been misconfigured, causing data corruption. I have also seen outdated, buggy RAID drivers cause data corruption. And I have seen wrong network drivers used that significantly reduce network speed.
If your server does not appear to be running at full speed, then suspect a hardware or driver misconfiguration and have the server checked out by an expert (someone other than the person who built and configured the original server). [6.5, 7.0, 2000, 2005] Updated 8-5-2005
*****
Ensure that the hardware you select for your SQL Server is on Microsoft’s Hardware Compatibility List. Doing so helps to ensure against performance problems and the blue screen of death. [6.5, 7.0, 2000, 2005] More info from Microsoft Updated 8-5-2005
*****
Don’t run any applications on your server other than SQL Server, with the exception of necessary utilities. It is hard to tune and troubleshoot many server-related problems if you have more than one major application on your server, besides the performance hit SQL Server takes if it has to share hardware resources. [6.5, 7.0, 2000, 2005] Updated 8-5-2005
*****
Don’t forget your user’s hardware. Like server hardware, it can directly affect the performance of SQL Server-related activity. At a minimum, workstations should have at least one 1+GHz CPU and 256MB RAM. For power users, consider purchasing them 3+ GHz CPU workstations with 512MB or more of RAM. [6.5, 7.0, 2000, 2005] Updated 8-5-2005
*****
Think carefully about your hardware decisions before you make them. For example, say you have a CPU-related bottleneck problem on your server. Generally, there would be two ways to deal with such a problem. One would be to reduce the CPU load on the server (e.g. fine-tune queries to reduce the load). Another way would be to add additional CPU cycles (in the form of faster or additional CPUs). Which option should you follow? The wrong decision would be to not evaluate your options and just choose one or the other based on little evidence. The proper decision would be to carefully evaluate the potential options, and then decide which one produces the biggest payoff.
For example, after investigating both options, you find out that it would only cost $1,000 to add an additional CPU to the server, but to tune the queries, it might take 3 weeks for a developer who makes $6,000 a month (or a cost of about $4,500). In this particular example, purchasing the new CPU would produce the biggest bank for the buck. In another example, it might take $2,000 to add two new CPUs but only take a developer 1 day (at a cost of about $300) to tune the queries.
Sure, many performance issues are not so clear cut, but this doesn’t mean you shouldn’t carefully evaluate your options in order to determine what the cost will be for each available option. Only then can you make the best decision. [6.5, 7.0, 2000, 2005] Updated 8-5-2005
*****
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 is 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] Updated 8-5-2005