Performance Tuning SQL Server Hardware

SQL Server can take great advantage of multiple CPUs. For example, with multiple CPUs, SQL Server can take a single query (if it meets certain criteria) and split it between multiple CPUs, often speeding performance. In most cases, you will probably want to purchase your SQL Server with at least room for two CPUs, even if you only need one at the moment.

It is less expensive in the long run to purchase a slightly larger server than you need now, and upgrade it later, than buying a completely new server later. Of course, if your proposed application requires it, then purchase a server with a many CPUs as it requires for optimum performance. [7.0, 2000, 2005] Updated 5-18-2004

*****

When selecting your CPU for your server, select one with a large L2 cache. This is especially important if you have multiple-processor servers. The greater the size of the L2 cache, the greater the server’s CPU performance because it reduces the amount of wait time experienced by the CPU when reading and writing data to main memory.  [6.5, 7.0, 2000, 2005]  Updated 5-18-2004

*****

Simple, single table queries and updates, along with query joins on small tables, take minimal CPU processing power. On the other hand, large joins, aggregations, and sorting of large rowsets use a high level of CPU processing power. Keep this in mind when specing the CPU requirements for your application. Be sure you fully understand your application and how it works before purchasing hardware for it. [6.5, 7.0, 2000, 2005] Updated 5-18-2004

*****

Keep in mind that there are two ways to boost a server’s processing power. First, you can increase the speed of the CPU(s), or two, you can add more CPUs. But which option is best?

As a general rule of thumb, when you are currently experiencing high % of CPU usage time and a low Processor Queue Length (less than 2), it is beneficial to increase the speed of the individual processors. This is because the server appears to not be getting behind in its work (which a long Process Queue Length would indicate), but it just doesn’t have enough CPU cycles to efficiently do the work it is tasked with.

If you are currently faced with the problem of both high % of CPU usage and a high Processor Queue Length, the you should consider adding more CPUs. This is because a long Processor Queue Length indicates that the server can’t keep up with current requests, and more CPUs are needed to keep up with the load.

Of course, if you are faced with an upgrade of your server because of a CPU bottleneck, I would get the fastest CPUs you can afford, with the largest L2 cache available, and as many CPUs as necessary for current and projected future processing needs. [6.5, 7.0, 2000, 2005]  Updated 5-18-2004

*****

If your budget is tight, and you have a choice to devote your scarce dollars to either more RAM or to a faster disk subsystem, buy the extra RAM. Dollar per dollar, RAM gives you more performance than the same number of dollars spent on a faster disk subsystem. [6.5, 7.0, 2000, 2005] Updated 5-18-2004

*****

If your budget doesn’t allow you to fully populate the RAM in your server when you first purchase it, be sure you leave room for adding more RAM so if you later find you need it, you can easily expand it. Some people (e.g. managers) will want you to purchase less expensive RAM chips that will use every available RAM slot, instead of more expensive RAM chips that will take up fewer slots and leave room for later expansion.

Invariably, if you try to save a few dollars now by purchasing less expensive RAM chips, you will often find out that your server needs more RAM sooner than later. And when this time comes, instead of just plugging the new chips in, you will have to toss out the old chips and buy all new chips in order to get the total amount of RAM you need. In the end, this is much more expensive than buying the more expensive chips in the first place. [6.5, 7.0, 2000, 2005] Updated 5-18-2004

*****

If you intend to take advantage of SQL Server’s ability to run parallel queries, plan on investing on more RAM. Parallel queries use much more RAM than non-parallel queries.  Keep this in mind when specing your servers. [7.0, 2000, 2005] Updated 5-18-2004

*****

Microsoft recommends that the minimum amount of RAM your server should have if it is running SQL Server 2000 and NT Server 4.0 is 128MB, and if it is running SQL Server 2000/2003 and Windows 2000, the minimum recommendation is 256MB. And as you know, Microsoft always underestimates the amount of RAM you need to run its software. Because of this, you may want to consider doubling these figures as a starting point to determining how much RAM your SQL Server should have. [2000] Updated 5-18-2004

Continues…

Leave a comment

Your email address will not be published.