SQL Server 2005 Operating System Configuration Checklist:
Which OS version are you running?
Are the disk partitions formatted using the most recent version of NTFS?
Is “NTFS data file encryption and compression” turned off?
Do you have at least 20% free space on each of your logical disk drives?
Does your server OS have the latest service pack?
Does your server have the most current, Microsoft-certified hardware drivers?
Is the server configured as a stand-alone server?
Is the “Application Response” setting, set to “Optimize Performance” for “Background Services?”
Has security auditing been turned on?
How large is the server’s PAGEFILE.SYS swap file?
Have unnecessary services been turned off?
Have all unnecessary network protocols been turned off?
Is antivirus software being used?
Which OS Version are You Running?
The focus of this section of our performance audit is the base operating system (OS), and how to optimize it in order to get the best performance out of SQL Server.
Like SQL Server, Windows Server is mostly self-tuning. But like SQL Server, there are things we can do to help optimize Window Server’s performance. Every time we help boost the performance of Windows Server, we are at the same time boosting the performance of SQL Server.
In some organizations, as a DBA, you may not have a lot of control over the operating system you are running SQL Server under. If this is the case, you still want to audit these settings, and then discuss them with the person responsible for the OS, working out any differences so that the OS is properly tuned to get the most out of SQL Server 2005.
While many of the suggestions in this section may seem trivial, most aren’t. You would be surprised how many server resources would be wasted if were to set each of these options to the opposite of the recommendations.
Selecting the Best Performing OS
As you may be aware, every new version of Windows Server comes with new features that help boost the stability, fault tolerance, and performance of SQL Server. With this in mind, it is always a good idea to run SQL Server 2005 on the most recent version of the OS that is currently available. When Windows Server 2008 becomes available, you will want to seriously consider upgrading to it in order to take advantage of the new performance features it offers.
Besides selecting the most current version of the OS to run SQL Server on, you have two other considerations to make. First, you must decide whether or not you want to use the 32-bit or 64-bit version of the OS. Of course, if you want to use the 64-bit version of SQL Server 2005, then this is an easy decision. On the other hand, if you have not decided on which version of SQL Server 2005 to use, selecting the appropriate version of the OS is harder. One thing to keep in mind when selecting 32-bit over 64-bit of either SQL Server or Windows Server is that one version is not always better than the other. Just because you are using a 64-bit solution does not mean than it will be faster or better meet your needs. It depends on your application. So don’t automatically opt for a 64-bit solution until you have carefully weighted the pros and cons of your options.
The second OS consideration you have to make is which edition of the OS to go with: Standard, Enterprise, and Data Center. Each has their pros and cons, and you need to select the best version to match your SQL Server 2005 needs. To make the decision, first decide on which version of SQL Server 2005 you need to best meet your application’s needs, and then select the least expensive version of the OS that supports the SQL Server 2005 edition you have chosen. There is no benefit in purchasing a more powerful, and more expensive version of the OS than SQL Server 2005 can take advantage of.
Are the Disk Partitions Formatted Using the Most Recent Version of NTFS?
Many DBAs aren’t aware that the NTFS file format used by the various editions of Windows Server can be different, with each offering different performance benefits. As you may assume, the more recent version of the OS you use, the newer the version of NTFS you are using, and the better it will perform under SQL Server.
Normally, you don’t have to give much though to the NTFS version as the most recent one is automatically used when you install the latest version of Windows Server. But there is something that might surprise you. If your physical server has been upgraded in place from an older version of the OS to a newer version of the OS, that the NTFS version is not automatically upgraded. It is only upgraded as a separate step, assuming the IT professional remembered to do this.
So what doe this mean to you, as the DBA? Essentially, if your physical SQL Server has had an OS upgrade, it would be wise for you to find out of the NTFS format was also upgraded, and if not, have it upgraded. While there is not a lot of difference between NTFS versions, there is enough performance benefit to make the upgrade worth your while. The newest versions of NTFS include some new performance enhancements, which mean fewer disk accesses to find files, and generally overall faster disk reads.
Is “NTFS Data File Encryption and Compression” Turned Off?
Windows supports both file encryption and compression, and by default, these two features are turned off on a newly installed Windows 2003 server. While these features do provide some benefits under limited circumstances, they do not provide any benefits for SQL Server. In fact, using one or both of these features can greatly hurt performance.
As you know, SQL Server can be very resource intensive, and anything else running on a SQL Server can hurt its performance. Both file encryption and compression significantly increase disk I/O and CPU utilization, as data files have to be manipulated on the fly as they are used. So if either file encryption or compression is used on SQL Server files, performance will greatly suffer.
If you become the DBA of a currently existing SQL Server, and are not familiar with it, check to see if anyone mistakenly have turned on either of these functions. If so, and you turn them off, you will become a performance hero to all of the server’s users.
Does Your Server Have the Latest OS Service Pack?
Every service pack I have ever seen has one or more performance enhancements. These could be because of tuning done by Microsoft, or because some previous bug has been fixed that boosts performance.
While you may not want to rush right out and install a new service pack the day it is released from Microsoft, once it has been tested positively in the real world, you should install the service pack.
Does Your Server Have the Most Current, Microsoft-Certified Hardware Drivers?
On more than one occasion, I have seen older, buggy hardware drivers cause performance problems with Windows Server. Most commonly, these are disk- or network-related drivers.
Periodically, you should check to see that your server has the most recent, Microsoft-certified hardware drivers. You can do this by going to the hardware vendor’s website, or, to a more limited degree, by using Microsoft’s Update service. In some cases, you may find a new driver that is available from the vendor, but has yet to be certified by Microsoft. I recommend that you be patient and wait (assuming this is practical) for the Microsoft-certified version. While increased performance is important, software stability is even more important.