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 really designed to use virtual memory, although it can if it has too.
Instead of using the operating system’s combination of physical RAM and virtual memory, SQL Server prefers to stay 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 that was discussed in the previous page. If this counter is 99% or higher, then most likely you have enough physical RAM in your SQL Server. If this counter is between 90% and 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, then more RAM should be added.
If this counter is less than 90%, the odds are that your SQL Server’s performance is unacceptable (if you are running OLAP, then less than 90% is generally OK), and you will want to add more RAM to your server.
Ideally, the amount of physical RAM in a SQL Server should exceed the size of the largest database on the server. This is not always possible, as many databases are very large. If you are sizing a new SQL Server, and assuming your budget is large enough, try to order your SQL Server with enough RAM to hold the entire size of the projected database. Assuming that your database is 4GB or less, then this isn’t generally too much of a problem. But if your database is larger than (or is expected to grow larger than 4GB) then you may be unable to easily or affordably get more than 4GB of RAM. While SQL Server 2000 Enterprise Edition will support up to 64GB of RAM, there aren’t too many affordable servers that support this much RAM.
Even if your entire database cannot fit into SQL Server buffer cache, SQL Server can still be very fast when it comes time to retrieve data. With a 99% buffer cache hit ratio, this means that 99% of the time the data SQL Server needs is already in cache, and performance will be very fast. For example, I manage one database that is 30GB, but the server only has 4GB of RAM. The buffer cache hit ratio for this server is always over 99.6%. What this means is that in most cases, users don’t access all the data in a database at the same time–only a fraction of it–and that SQL Server has the ability to keep the most used data in cache all the time, so 99% of all requests are met quickly in this particular instance, even though the server has much less physical RAM than the size of the data in the database.
So what does all of this boil down to? If your buffer hit cache ratio is less than 90%, 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.
Total Amount of Available Drive Space on Server
While the performance effect isn’t huge, it is important that all of your disk arrays have at least 20% of free space. This is because NTFS (which is the disk format I assume you are using) needs extra space to work efficiently. If the space is not available, then NTFS is not able to function at its full capacity and performance can degrade. It also leads to more disk fragmentation, which causes the server to work harder to read and write data.
Take a look at each of the physical disks in your SQL Server, checking to see if there is at least 20% or more of free space. If there isn’t, then consider trying:
- Removing any unnecessary data from the disks (empty the recycle bin, remove temp files, remove setup files, etc.)
- Moving some of the data to disks with more space
- Adding more disk space
Total Number of Physical Drives in Each Array
A disk array generally refers to two or more physical disk drives working together as a single unit. For example, a RAID 5 array might have 4 physical drives in it. So why is it important to know how many physical drives are in the one or more arrays in your SQL Server?
With the exception of mirrored arrays (which are two physical drives working together), the more physical drives that are in an array, the faster reads and writes are for that array.
For example, let’s say that I want to purchase a new SQL Server with a RAID 5 array and that I need at least 100MB of available space. Let’s also assume that the vendor has proposed two different array configurations:
- 4 – 36GB drives (108GB available)
- 7 – 18GB drives (108GB available)
Both of these options meet our criteria of providing at least 100MB of RAID 5 disk space. But which array will provide better read and write performance? The answer is the second choice, the 7 18GB drives. Why?
Generally speaking, the more disks that are in an array, the more disk heads there are available to read and write data. SCSI drives, for example, have the ability to read and write data simultaneously. So the more physical drives that there are in an array, the faster data is read or written to the array. Each drive in the array shares part of the workload, and the more, the better. There are some limits to this, depending on the disk controller, but generally, more is better.
So what does this mean to you? After you take a look at the number of arrays you have in your SQL Server, and the number of drives in each array, is it feasible to reconfigure your current arrays to take better advantage of the principal of more is better?
For example, let’s say that your current server has two disk arrays used to store user databases. Each is a RAID 5 array with 3 18GB drives each. In this case, it might be beneficial to reconfigure these two arrays into a single array of 6 18GB drives. Not only would this provide faster I/O, but it would also recover 18GB of hard disk space.
Take a careful look at your current configuration. You may, or may not be able to do much. But if you can, you will be able to see the benefits of your change as soon as you make them.
RAID Level of Array Used for SQL Server Databases
As you probably already know, there are various different types of disk array configurations, called RAID levels. Each has their pros and cons. Here is a brief summary of the most commonly used RAID levels, and how they can be best used in your SQL Server:
RAID 1
-
Ideally, the operating system and SQL Server executables, including the operating system’s swap file, should be located on a RAID 1 array. Some people locate the swap file on its own RAID 1 array, but I doubt that this really offers much of a performance boost because paging, on a well-configured server, is not much of an issue.
-
If your SQL Server database(s) are very small, and all the databases can fit on a single disk drive, consider RAID 1 for the storing of all your SQL Server data files.
-
Ideally, each separate transaction log should be located on its own RAID 1 array. This is because transactions logs are written to and read from sequentially, and by isolating them to their own array, sequential disk I/O won’t be mixed with slower random disk I/O, and performance is boosted.
RAID 5
-
Although this is the most popular type of RAID storage, it is also not the best option for optimum SQL Server I/O performance. If a database experiences more than 10% writes, and most OLTP databases do, write performance will suffer, hurting the overall I/O performance of SQL Server. RAID 5 is best used for read-only or mostly read-only databases. Testing at Microsoft has found that RAID 5 can be as much as 50% slower than using RAID 10.
RAID 10
-
RAID 10 offers the best performance for SQL Server databases, although it is the most expensive RAID option. The more write intensive the database, the more important it is to use RAID 10.
-
RAID 10 arrays are also a good option for transaction logs, assuming they are dedicated to a single transaction log.
Most likely, your current SQL Server configuration does not match the recommendations above. In some cases, you may be able to modify your current array configuration to come closer to what is recommended above, but in most cases, you will probably have to live with what you have until you get a new budget for a new server and array.
If you can only do one of the above recommendations, I would recommend that you move to RAID 10 over the other options. This option, above all others listed above, will give you the greatest overall boost in SQL Server I/O performance.
Hardware vs. Software RAID
RAID can be implemented through hardware or software (via the operating system). There is no debate on this topic, don’t ever user software RAID, it is very slow. Always use hardware RAID.