Is “NTFS data file encryption and compression” turned off?
Does your server have the latest service pack?
Does your server have the most current, Microsoft-certified hardware drivers?
Is the Windows 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?
Enter your results in the table above.
Configuring Your Windows Server is Easy, But Critical
The focus of this section of our performance audit is the base operating system, 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 Windows’ performance. And every time we help boost the performance of Windows Server, we are at the same time boosting the performance of SQL Server.
Selecting the Best Performing OS?
While SQL Server can run under Windows NT 4.0 Server, Windows 2000 and Windows 2003, the focus here is on Windows 2003, as it is the most current version of the operating system. If you are still using NT 4.0, see this URL for guidance. And if you are still using Windows 2000, see this URL for guidance.
If you want the best performance out of SQL Server, you will want to run it under Windows 2003 server, as it offers many performance improvements over Windows 2000 and NT 4.0. Some of these include:
The ability to better take advantage of Intel hyper-threading CPUs.
Up to 32 CPUs and 64GB of RAM are now supported using Intel chips, and up to 64 CPUs and 512GB of RAM are supported using Itanium chips.
I/O path and disk I/O performance have been substantially boosted, while at the same time reducing the amount of CPU resources needed to service I/O requests.
If you have not upgraded your SQL Servers to Windows 2003 yet, do so as soon as you can. It will provide a quick and easy boost to your SQL Server’s performance.
Are the Disk Partitions Formatted Using NTFS 5.0?
If your server is new and Windows 2000 or Windows 2003 has been recently installed, then any drives that have been formatted with it have been formatted using NTFS 5.0. But, if the server is older, and previously ran Windows NT 4.0 Server, and the drives have not been reformatted since upgrading to Windows 2000 or 2003, the disks most likely were formatted using NTFS 4.0.
While there is not a lot of difference between NTFS 4.0 and 5.0, there is enough to make the upgrade worth your while. NTFS 5.0 includes some new performance enhancements, which mean fewer disk accesses to find files, and generally overall faster disk reads. Before Windows 2000 and 2003, some DBAs formatted the drives or disk arrays dedicated to log files as FAT because it had a small performance benefit over NTFS 4.0. This is no longer true under NTFS 5.0, so all disks for all SQL Server should be formatted using NTFS 5.0 for best performance.
If you currently have a production SQL Server that is using NTFS 4.0 formatted partitions under Windows 2000, it may be difficult for you to convert them to NTFS 5.0. If this is the case, I would recommend that you not worry about this, as the performance hit is not huge. But if you are upgrading from Windows NT 4.0 server to Windows 2000, you will definitely want to reformat your drives using NTFS 5.0 to take advantage of every little performance benefit you can eek out of your server.
Is “NTFS Data File Encryption and Compression” Turned Off?
NTFS 5.0 under Windows 2000 supports both file encryption and compression, and by default, these two features are turned off on a newly installed Windows 2000 or 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 is very I/O intensive, and anything that increases disk I/O hurts SQL Server’s performance. Both file encryption and compressions significantly increase disk I/O 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 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 2000 and 2003. 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 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.
Is the Windows 2000 Server Configured as a Stand-Alone Server?
A Windows 2000 or 2003 server can be configured as either a stand-alone server or as a domain controller. For best performance, SQL Server should only run on a stand-alone server. This is because a domain controller has a lot of overhead that takes away server resources from SQL Server, hurting performance.
Is the “Application Response” Setting, Set to “Optimize Performance” for “Background Services?”
In Windows 2000, under the “Advanced” tab of the “System” icon in “Control Panel”, click on “Performance Options,” and you can configure what is called the “Application Response” setting. You can choose to optimize performance for either “Applications” or “Background Services”. You should choose “Background services” for best SQL Server performance, as this tells the OS that you want to favor background applications, such as SQL Server, over foreground applications.
In Windows 2003, under the “Advanced” tab of the “System” icon in “Control Panel,” click on the “Setting” button under “Performance,” the click on the “Advanced” tab. Here, you can change the performance to favor either “Programs” or “Background services.” You should choose “Background services” for best SQL Server performance, as this tells the OS that you want to favor background applications, such as SQL Serve, over foreground applications.
Also, here, you can change the memory allocation to favor either “Programs” or the “System cache.” For best SQL Server performance, select “Programs.” This tells the OS to give more memory to applications, such as SQL Server, rather than the system cache.
After making these changes, you will most likely have to reboot your server.
Has Security Auditing Been Turned On?
Windows 2000 and 2003 has the ability to audit virtually any activity on a server. By default, most security auditing is turned off. For best performance, no additional auditing should be turned on, as this will increase I/O activity, competing with SQL Server for the same I/O. Of course, if you have to have auditing turned on (because some manager says so), try to limit it as much as possible in order to reduce its negative effect on performance.
How Large is the Server’s PAGEFILE.SYS Swap File?
Microsoft recommends that the PAGEFILE.SYS file be set to 1.5 times the amount of physical RAM. The exact amount you need depends on what additional SQL Services you may be running. For example, if you are running Full-Text Search service, Microsoft recommends that your PAGEFILE.SYS file be three times physical RAM.
Microsoft’s recommendations are a good starting point, but the best way to size the PAGEFILE.SYS is to monitor how much of it is used during production using the Performance Monitor Page File Object: % Usage counter, and then resize the PAGEFILE.SYS with a minimum size just slightly larger than the amount that is actually being used (based on the Performance Monitor counter), and with a maximum size of 50MB larger than the minimum size.
The PAGEFILE.SYS setting can be viewed and changed in Windows 2000 by right clicking on “My Computer”, choosing “Properties”, clicking on the “Advanced” tab, clicking on “Performance Options”, and clicking on the “Change” button under “Virtual Memory”. If you change the virtual memory settings, you will have to reboot your server for the new settings to go into affect.
In Windows 2003, the PAGEFILE.SYS setting can be viewed and changed under the “Advanced” tab of the “System” icon in “Control Panel,” click on the “Setting” button under “Performance,” the click on the “Advanced” tab, and then by clicking on the “Change” button under “Virtual memory.”
Have Unnecessary Services Been Turned Off?
For best performance, turn off any Windows 2000 or 2003 system services that aren’t needed. This conserves both RAM and CPU cycles, helping to boost the overall performance of SQL Server.
Below are some of the operating system services (not a complete list) that are generally considered non-essential and can be turned off, if they are not used. Some of these services may not be installed on your server, and others will already be set to “Disabled” or “Manual,” depending on how the server was installed and configured. Some of the services set to “Manual” are designed to only started when needed, and then to turn themselves off when no longer needed.
Alerter
Application Management
Clipbook
Distributed Link Tracking Server
Fax Service
File Replication
FTP Service
Indexing Service
Internet Connection Sharing
Intersite Messaging
Kerberos Key Distribution Center
License Logging Service
Logical Disk Manager Administrative Service
Messenger
Microsoft Search
NetMeeting Remote Desktop Sharing
Network DDE
Network DDE DSDM
Print Spooler Service (if you won’t be printing from this server)
QoS RSVP
Remote Access Auto Connection Manager
Remote Procedure Call (RPC) Locator
Routing and Remote Access
RunAsService
Smart Card
Smart Card Helper
SMTP Service
Telnet
Utility Manager
Windows Installer
World Wide Web Service
Generally, I turn off these services (assuming they are currently on) and ensure that their “Startup Type” setting is set to “Manual.” Of course, if you have a need for any of these services, you don’t have to turn them off.
Have All Unnecessary Network Protocols Been Turned Off?
Generally, the only network protocol you need is TCP/IP if you are running SQL Server on it. Removing unnecessary network protocols on your SQL Servers helps by reducing the load on the server and by reducing unnecessary network traffic.
Is Antivirus Software Being Used?
•Real-time antivirus software creates a big resource hog for SQL Server, and is not recommended on production SQL Servers, especially clusters.
If you are worried about viruses, you can do remote scanning against your SQL Servers on a daily basis, preferably during off hours.
Now What?
Your goal should be to perform this part of the performance audit, described on this page, for each of your SQL Servers, and then use this information to make changes, if you can.
Once you have completed this part of the performance audit, you are now ready to audit your SQL Server’s configuration.