SQL Server 2005 Performance Audit : SQL Server Hardware Configuration Checklist
Location of the tempdb Database
If your tempdb database is heavily used, consider moving it to an array of its own, either RAID 1 or RAID 10, to boost disk I/O performance. Avoid RAID 5 arrays as they can be slow when writing data, a common side-effect of using tempdb. This will help to reduce overall I/O contention and boost performance.
If your application uses the tempdb database a lot, and causes it to grow larger than its default size, you may want to permanently increase the default size of the tempdb file to a size closer to what is actually used by your application on a day-to-day basis. This is because every time the SQL Server service (mssqlserver) is restarted, the tempdb file is recreated to the default size. While the tempdb file can grow as needed, it does take some resources to perform this task. By having the tempdb file at the correct size when SQL Server is restarted, you don’t have to worry about the overhead of it growing during production.
In addition, heavy activity in the tempdb database can drag down your application’s performance. This is especially true if you create one or more large temp tables and then are querying or joining them. To help speed these queries, be sure the AUTOSTATS database option is turned on for tempdb, and then create one or more indexes on these temp tables that can be used by your query. In many cases, you will find that this can substantially speed up your application. But like many performance tips, be sure you test this one to see if it actually helps in your particular situation.
Location of System Databases
The system databases (master, msdb, model) don’t experience a lot of read and write activity, so locating them on the same array as your SQL Server data files is generally not a performance issue. The only exception might be for very large databases with hundreds or thousands of users. In this case, putting them on their own array can help boost overall I/O performance somewhat.
Location of User Databases
For best performance, user database files (MDBs) should be located on their own array (RAID 1, 5, or 10), separate from all other data files, including log files. If you have multiple large databases on the same SQL Server, consider locating each separate database file(s) on its own array for less I/O contention.
Location of Log Files
Ideally, each log file should reside on its own separate array (RAID 1 or 10, RAID 5 will slow down transaction log writes more than you would like). The reason for this is because most of the time, transaction logs experience sequential writes, and if the array can write the data sequentially (not having to interrupt itself to perform other reads and writes), then sequential writes are very fast. But if the array can’t write sequentially because it has to random perform other reads and writes, sequential writes can’t be performed, and performance suffers.
Of course, having a separate array for each log file is expensive, and often can’t be cost justified. At the very least though, locate all log files on an array (RAID 1 or RAID 10) other than the array used for database files. While sequential write performance won’t be as good as if each log file had its own array, it is still much better than trying to contend for disk I/O with data files.
Location of Backup Files
All production databases need to be backed up. Your options include backing up directly to tape, to a local disk array, or to a remote storage device. Of these three options, generally the best performance is gained by backing up to a local disk array (or SAN). In addition, for best performance, the local drive array you use to store backups on should be separate than where your user databases are stored, and should be RAID 1 or RAID 5. This is because database backups are a highly write-intensive procedure. Once your disk backups are made, you then have the option of moving them to a different location for higher fault tolerance.
Number of Disk Controllers in Server
A single disk controller, whether is it is SCSI or fiber channel, has a maximum limit on its throughput. Because of this, you will want to match the number of disk controllers to the amount of data throughput you expect. As each controller is do different, I can’t recommend specific solutions, other than to say that at a very minimum, you will want two disk controllers. One controller should be used for non-hard disk devices, such as the CD-ROM, backup devices, and so on. And the other controller would be used for hard disk. The goal is not to attach both slow and fast devices on the same controller.
Quite often, you see the following scenario, which is a good one. One controller is for non-hard disk devices, one controller is used for a RAID 1 local hard disk, and a third (and sometimes more) is used for arrays that hold SQL Server database files and logs. Be sure you don’t attach more drives to a controller than it can handle.
Type of Disk Controllers in Server
Always purchase the fastest disk controller you can afford, assuming you want the best SQL Server performance. As you may know, different disk controllers have different performance characteristics. For example, there are different types of SCSI, such as Wide SCSI, Narrow SCSI, Ultra SCSI, and so on. The same is true, although to a less degree, of fiber channel connections.
Because of the wide variety of controllers, I can’t recommend any specific ones. Generally, a hardware vendor will offer several models to choose from. Ask about the performance benefits of each one, and get the one that offers the best throughput.
Size of Cache in Disk Controllers in Server
Also, when you purchase a disk controller, consider how much disk cache it has. Some disk controllers allow you to add extra disk cache. Generally, you will want to purchase as much disk cache as your controller can hold. SQL Server is very I/O intensive, and anything we can do to boost I/O performance, like employing a large disk cache, will help out a lot. SANs often have huge disk caches.
Is Write Back Cache in Disk Controller On or Off?
The disk cache in your disk controller offers two ways to speed access. One is for reads and the other for writes. Of these, the most important use for it is for reads, as this is where most disk I/O time is spent in most SQL Server databases. A write back cache, on the other hand, is used to speed up writes, which usually occur less often, relatively speaking. Unfortunately, SQL Server, in most cases, assumes that write back cache is not on, and because of this, write back caching should be turned off on most controllers. If you don’t, it is possible, under certain circumstances, to get corrupted data after SQL Server writes data (once it writes data, it assumes it was written correctly), but for some reason (such as a loss of power) the write back cache does not write the data to disk.
While there are some controllers that offer battery backup to help prevent such issues, they don’t always work as expected. Personally, I prefer non-corrupt data (written more slowly) than corrupt data (that was written much faster). In other words, I recommend turning write back catching off on your disk controller, even though you might suffer a very small write performance hit by doing so.
Speed of Disk Drives
The disk drives that come in your arrays can often be purchased with different speeds. As you might expect, for best performance, always purchase the fastest disks you can. Generally, this is 15,000 RPM or faster. In addition, don’t mix and match drives of different speeds in the same array. If you do, performance will suffer.
How Many Network Cards Are in Your Server?
Fortunately, network traffic to and from a SQL Server is generally not a bottleneck, and a single network card is often more than adequate. But if you find that network traffic is a problem (you have hundreds or thousands of users) then moving to multiple network cards is justified, and can boost performance. In addition, two or more network cards can add to redundancy, helping to reduce downtime.
What is the Speed of the Network Cards in Server?
At the very minimum, your server should have 100Mbs network cards. If one or more 100MBs cards don’t offer enough throughput, then consider gigabit cards. If fact, you might want to skip 100MBs cards altogether and only use gigabit cards instead. Using a faster network card doesn’t speed up network traffic, it only allows more traffic to get through, which in turn allows your server to work at its optimum performance.
Are the Network Cards Hard-Coded for Speed/Duplex?
If you have a dual 10/100 or 10/100/1000 card in a SQL Server that is supposed to auto-sense the network’s speed and set itself accordingly, don’t accept that it has worked correctly. It is fairly common for a network card to auto-sense incorrectly, setting a less than optimum speed or duplex setting, which can significantly hurt network performance. What you need to do is to manually set the card’s speed and duplex setting, this way you know for sure that it has been set correctly.
Are the Network Cards Attached to a Switch?
This may be obvious in a large data center, but for smaller organizations, a hub may still being used to connect server. If so, seriously consider replacing the hub with an appropriate switch, and configure the switch to communicate at its highest possible performance, such as 100MBs and full duplex. Moving from a hub to a switch can make dramatic difference in network performance.
Are All the Hardware Drivers Up-to-Date?
Admittedly, this is a boring topic, but it is more important than you might think. One of the biggest performance hogs (not to leave out causes of strange and unusual problems) are buggy drivers, whether they are found in disk controllers, network, cards, or elsewhere. By using the latest drivers, the odds are that you will be getting a better, faster performing driver, allowing SQL Server to perform at its best.
Regularly, you should be checking to see if newer drivers are available for your hardware, and installing them when you have downtime. I have personally seen radical performance differences by changing from an old, buggy driver to a new one that has been thoroughly debugged and tuned.
Is this Physical Server Dedicated to SQL Server?
I have alluded to this before, but I can’t say it too often. SQL Server should run on a dedicated physical server, not shared with other application software. When you share SQL Server with other software, you force SQL Server to fight over physical resources, and you make it much more difficult to tune your server for optimum SQL Server performance. Time and time again, when I get questions about poor SQL Server performance, I find out that the culprit responsible is another application running on the same server. You just have to learn to say NO.
This has been a long journey so far, but we still have a long way to go. When I first evaluate a SQL Server for performance, and perform a performance audit, I take detailed notes about all of the topics discussed above. I then compare how the server is configured to the ideal configuration, and then look for easy ways to move closer to the ideal configuration. Sometimes this is easy (obvious, easy to correct mistakes have been made), and other times, there is not too much you can do. But you won’t know this if you don’t perform the audit.
Your goal should be to perform the part of the performance audit, as described on this page, for each of your SQL Servers, and then use this information to make corrections, if you can. If you can’t, then you can use this information as ammunition for getting new and better hardware.
Once you have completed this part of the performance audit, you are now ready to audit the operating system for potential performance improvements.