Hardware Performance Optimization

Auditing SQL Server Hardware Is An Important Early Step

From the previous section, 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 Servers need more power as time passes and workloads increase.

Here’s some potential scenarios:

• SQL Server will be used to run a specialized accounting application that will only be used by no more than 5 users at a time, and you don’t expect this to change in the next couple of years. If this is the case, a single CPU will most likely be adequate. If you expect that the number of users may increase fairly soon, then you would want to consider purchasing with a single CPU now, but with room to expand to a second one should the need arise.
• SQL Server will be used to run a specialty application written in-house. The application will not only involve OLTP, but need to support fairly heavy reporting needs. It is expected that concurrent usage will not exceed 25 users. In this case, you might want to consider a server with two CPUs, but with the ability to expand to 4 if necessary. It is hard to predict what “fairly heavy reporting needs” really mean. I have seen some fairly simple, but poorly written reports, peg out all of a server’s CPUs.
• SQL Server will run an ERP package that will support between 100 – 150 concurrent users. For “heavy-duty” applications like this, ask the vendor for their hardware recommendations, as they should already have a good idea of their product’s CPU needs.

I could provide many other examples, but the gist of what I am trying to get across is that it is very hard to predict exactly how many CPUs a particular SQL Server-based application will need, and that you should generally purchase a system bigger than you think you will need, because in many cases, usage demands on an application are often underestimated. It is less expensive in the long run to purchase a larger server now (with more CPUs), than to have to replace your entire server in 6-12 months because of poor estimates.

CPU Speed

Like the number of CPUs, the needed speed of the CPUs you purchase is hard to estimate. Generally speaking, as with the number of CPUs your SQL Server has, purchase the fastest CPUs you can afford. It is better to purchase too large a system than too small a system.

CPU L2 Cache

One of the most common questions I get is “should you purchase a less expensive CPU with a smaller L2 cache, or a more expensive XEON CPU with a larger L2 cache?” What complicates this decision is the fact that you can purchase faster chips with smaller L2 caches than you can of chips that have a large L2 cache. Here’s my rule of thumb:

• If you will only be running 1 or 2 CPUs, go with the fastest CPU you can get, with L2 cache as a secondary consideration. If you have a choice of L2 cache size, always get the largest you can.
• But, if you will be running 4 or more CPUs, then you want to go with the CPUs with the largest L2 cache, even though their speed may not be as high. The reason for this is in order for SQL Server to run optimally on servers with four or more CPUs, the L2 cache has to be much larger, otherwise you will be wasting much of the power of the additional CPUs.

CPU Audit Checklist

Since this article is about an audit of your current SQL Server’s CPU capability, your focus now should be on whether or not your current servers are experiencing any CPU bottlenecks. As was discussed in the Performance Monitor section of this article, you can use the Performance Monitor to help you identify hardware bottlenecks.

If you are not experiencing currently CPU bottlenecks, then you can skip to the next section on memory. But if your current server is experiencing a CPU bottleneck, and it is bad enough to cause major performance problems, then these are your options to resolving this bottleneck:

• Reduce the load on your server. This can be accomplished by reducing the number of users, by tuning queries, by tuning indexes, and by eliminating any unnecessary applications running on the server. One option is to move reporting needs from your production server to a SQL Server devoted to reporting only.
• Adding more memory, assuming that the CPU bottleneck is caused by a lack of memory in the server, which is a common problem.
• Adding additional CPUs if you have room in the current server.
• Upgrading to faster CPUs in your server, if this option is available.
• Purchasing a new server with more, and faster CPUs.

Unfortunately, none of these options to deal with CPU bottlenecks are extremely easy to implement, unless of course your company has unlimited money to spend. As a DBA in charge of a SQL Server with a CPU bottleneck, you have many difficult decisions to make, and lots of work ahead of you, especially if your only option, due to a lack of money, is to “reduce the load on your server.”