SQL Server Hardware Configuration Checklist:
Number of CPUs (and or Cores)
32-Bit vs. 64-Bit
CPU MHz
CPU L2 Cache Size
Physical RAM Amount
SAN vs. Local Storage
Total Amount of Available Drive Space on Server
Total Number of Physical Drives in Each Array
RAID Level of Array Used for SQL Server Databases
Hardware vs. Software RAID
Disk Fragmentation Level
Location of Operating System
Location of SQL Server Executables
Location of Swap File
Location of tempdb Database
Location of System Databases
Location of User Databases
Location of Log Files
Location of Backup Files
Number of Disk Controllers in Server
Type of Disk Controllers in Server
Size of Cache in Disk Controllers in Server
Is Write Back Cache in Disk Controller On or Off?
Speed of Disk Drives
How Many Network Cards Are in Server?
What is the Speed of the Network Cards in Server?
Are the Network Cards Hard-Coded for Speed/Duplex?
Are the Network Cards Attached to a Switch?
Are All the Hardware Drivers Up-to-Date?
Is this Physical Server Dedicated to SQL Server?
Auditing SQL Server Hardware Is an Important Early Step
We have to start somewhere and server hardware is as good a place to start as any other audit area. In addition, when many DBAs, or database users, think about poor database performance, often the first thing that pops in their minds is whether the hardware and/or hardware configuration is up to meeting the demands of their SQL Server databases. While the above statement is often cited, what you may not know is that, generally speaking, server hardware is one of the least common causes of SQL Server performance problems. Now, this is not to say that hardware is not important, it is. But oftentimes, it is some other issue, such as poor database design, poorly designed queries, or poor indexing that are causing performance problems, not the hardware.
But again, that is why we are conducting this performance audit. Our goal is to identify what potential performance issues your server may have, and hardware just might be the problem. If you have tuned your database to perfection, and are still having performance issues, then hardware may be your bottleneck to better performance.
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. It is always a good idea to “inventory” your server’s hardware so you know exactly what you are working with.
CPU
Number of CPUs
This first point is obvious, the more CPUs your SQL Server has, the faster it can potentially perform. I say potentially, because if your SQL Server does not need extra CPU resources, adding more CPU power won’t affect performance much, if at all.
CPUs currently come in one core, two core, and four core models. Currently, the most cost-effective option is to go with four core CPUs, both in regards to hardware costs and licensing costs, assuming you need the power of four cores.
The Standard Edition of SQL Server 2005 can support up to four CPUs. The Enterprise Edition can support as many CPUs as the OS it is using supports.
Keep in mind that adding additional CPUs does not provide linear performance benefits. In other word, a two CPU server is not twice as powerful as a single CPU server, and an eight CPU server is not twice as powerful as a four CPU server. The addition of each additional CPU offers diminishing returns.
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 for 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 ten users at a time, and you don’t expect this to change in the next couple of years. If this is the case, a two core 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 a four core CPU now. In addition, purchasing a server with an empty socket offers you even more flexibility for future growth.
• 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 100 users. In this case, you might want to consider a server with four CPUs, but with the ability to expand to four more 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 250 – 300 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. You will probably be looking at least eight CPUs as a starting point.
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.
32-Bit vs. 64-Bit
When you are deciding between purchasing 32-bit vs. 64-bit CPUs, what you are actually deciding is how much memory you want SQL Server to easily access, not CPU power. In fact, 64-bit CPUs can under perform 32-bit CPUs when it comes to raw processing power. You only generally only need to consider 64-bit CPUs if you expect your server will need 16 GB or more of RAM.
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 one or two CPU cores, 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 four or more CPU cores, 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.
Most two and four core CPUs come with 4MB to16MB of L2 cache.
Memory
While server memory is discussed here after first discussing the CPU, don’t think that it is not as important as your server’s CPU. In fact, memory is probably the most important hardware ingredient for any SQL Server, affecting SQL Server’s performance more than any other hardware.
When we are talking about memory, we are referring to physical RAM. Often, the word memory (in the Windows Server world) refers to physical RAM and virtual memory (swap file). This definition is not good for SQL Server because SQL Server is not designed to use virtual memory.
Instead of using the operating system’s combination of physical RAM and virtual memory, SQL Server prefers to keep data in physical RAM as much as it can. The reason for this is speed. Data in RAM is much faster to retrieve than data on disk.
When SQL Server can’t keep all of the data it manages in RAM (the SQL Server Buffer Cache), it accesses disk, similar to the way that the operating system manages virtual memory. But SQL Server’s “caching” mechanism is more sophisticated and faster than what the operating system virtual memory can provide.
The fastest way to find out if your SQL Server has an adequate amount of RAM is to check the SQL Server: Buffer Cache Hit Ratio counter using System Monitor. If this counter is 99% or higher, then most likely you have enough physical RAM in your SQL Server. If this counter is lower than 99%, and if you are happy with your SQL Server’s performance, then you probably have enough physical RAM in your SQL Server. But if you are not satisfied with your server’s performance, and the Buffer Cache Hit Ratio is lower than 99%, then adding more RAM should result in better performance.
On the other hand, if the Buffer Hit Cache Hit Ratio is above 99%, and you aren’t happy with your server’s performance, adding more RAM won’t help. Why? Because if the Buffer Hit Cache Ratio is that high, SQL Server is using all the RAM it needs to get its job done. If you add more RAM than it needs, SQL Server won’t use it.
In fact, I have seen many SQL Servers with lots of installed RAM, but little of it used because their particular database and application didn’t need the RAM to run without a memory bottleneck. For example, one SQL Server I saw has 14 GB installed on it, but SQL Server only uses a maximum of 140 MB of it, leaving the rest of the RAM to go unused. And at this level of memory use, its Buffer Cache Hit Ratio always exceeds 99%.
So what does all of this boil down to? If your buffer hit cache ratio is less than 99%, and performance is suffering, then seriously consider adding more RAM.
Disk Storage
After memory, disk storage is often the most important factor affecting SQL Server’s performance. It is also a complicated topic. In this section, I will focus on the “easiest” areas where disk storage performance can be bolstered.