SQL Server 2005 Performance Audit : Operating System Configuration Performance Checklist

Is the Server Configured as a Stand-Alone Server?

A Windows 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 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.

In many cases, these settings will probably be correct. But if they are not, they should be changed for optimum SQL Server performance. After making these changes, you will most likely have to reboot your server.

Has Security Auditing Been Turned On?

Windows Server 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 (other than the default) should be turned on, as this will increase CPU and I/O activity, competing with SQL Server for these same resources. Of course, if you have to have auditing turned on (because some manager says so), try to limit the amount of resources audited 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 in the server. 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 500 MB larger than the minimum size.

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 Server 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, assuming they are not used for a specific purpose. 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 be started when needed, and then turned 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.