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.
Is Write Back Cache in Disk Controller On or Off?
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 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. Ten megabit cards just don’t offer the bandwidth you need. 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.
Now What?
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, 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.
]]>