Tuning SQL Server 2005 on Windows Integrity Servers

Page 6 of 13
Windows Server 2003
Add the following new keys to the Windows Server 2003 OS installation using the Registry Editor.
Disable process-level I/O counters
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\I/O System]
"CountOperations"=dword:00000000
(Note: This is not required with Windows Server 2003 with SP1)
Increase available memory by reducing system cache size and NonPagedPool
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory
Management]
"LargeSystemCache"=dword:00000000
Disable power saving mode
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory
Management]
"IdleFrom0Delay"=dword:00000000
Do not start Windows with the /debug boot option. While this is useful for troubleshooting, the
/debug option affects performance.
Disable all unnecessary services. Many of the services started by default are unnecessary. To disable
these services:
Right Click My Computer->Manage->Services and Applications->Services and disable services such
as Active Directory that are not used.
If you are not sure of a service, err on the side of caution and leave it enabled. You may need to
experiment to ensure optimum functionality.
SQL Server 2005
The following performance tuning guidelines are for advanced administrators; consider them carefully
before changing the settings.
Use startup options to lock pages in memory
When SQL Server is installed, a set of default options are written to the registry. The default startup
options can be overridden temporarily using startup options.
With SQL Server 2005, locking pages for the buffers is the default behavior.
To enable the Lock Page in Memory option:
1. Click Start > Run, and then in the Open box, type gpedit.msc.
2. From the Group Policy console, expand Computer Configuration and then Windows Settings.