SQL Tips - Operation System Optimization Tips

*****

Set a reasonable size of your PAGEFILE.SYS file(s).
Microsoft recommends that the Windows NT PAGEFILE.SYS file(s) be set to physical RAM + 12 MB for the initial size and physical RAM + half of physical RAM for the maximum size. Microsoft recommends that the Windows 2000 PAGEFILE.SYS file(s) be set to 1.5 times the amount of physical RAM.
If you used additional SQL services such as Full-Text Search service, the size of PAGEFILE.SYS file(s) should be increased.
To increase the size of PAGEFILE.SYS file(s), you can do the following:
1. Double-click the Control Panel System applet and select the Performance tab.
2. Click the "Virtual Memory" button.
3. Set appropriate size of the PAGEFILE.SYS file(s).
4. Restart the computer.

*****

Create another pagefile.sys files on every separate physical drives (Except drive contains the Windows NT system directory).
Spreading paging files across multiple disk drives and controllers improves performance on most disk systems because multiple disks can process input/output requests concurrently.

*****

If you have a lot of RAM, you can configure your Windows NT server to never page out drivers and system code to the pagefile that are in the pageable memory area.
Run regedit and choose:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management
Set DisablePagingExecutive to 1 and reboot the server box.

*****

Set the "Maximize Throughput for Network Applications" option.
This can increase SQL Server performance, because Windows NT will allocate more RAM to SQL Server than to its file cache.
To set this option, you can do the following:
1. Double-click the Network icon in Control Panel.
2. Click the Services tab.
3. Click Server to select it, and then click the Properties button.
4. Click Maximize Throughput for Network Applications, and then click OK.
5. Restart the computer.

*****

You can increase performance by disable last access update files date.
Run regedit and choose:
HKLM\SYSTEM\CurrentControlSet\Control\FileSystem\NtfsDisableLastAccessUpdate
Add key NtfsDisableLastAccessUpdate as REG_DWORD and set it to "1".

*****

Use minimum protocols on the server box (only TCP/IP, for example).
Because each protocol uses RAM and CPU, you can remove unused protocols to release resources for SQL Server using.

*****

Use minimum services on the server box.
Try to remove from startup IIS service, FTP server service, Gopher, SMTP, WINS, DHCP, Directory Replicator and so on, if you do not need to use these services. You can start these services manually when you will need them.

*****

When multiple transport protocols are installed, set the most frequently used protocol to the first place in the binding list.
If you installed several protocols, Windows NT negotiates network connections in the order that the protocols are prioritized in the network services binding list. So, the first protocol in the binding list will be used before the other installed protocols. You can improve the overall performance by setting the most frequently used protocol to the first place in the binding list.

*****

Use as few counters in Performance Monitor, as possible.
Because each Performance Monitor counter uses some server resources, it is a great idea to use as few counters in Performance Monitor, as possible.

*****

Do not use Open GL screen savers on your server box.
Because Open GL screen savers use a lot of system resources, it is a great idea to not use them on a server box.

*****

Use as few types of Audit Policy events, as possible.
Because each type of Audit Policy events uses some server resources, it is a great idea to use as few types of Audit Policy events, as possible. Try to not use "File and Objects Access" and "Process Tracking" Audit Policy events, because they most resource expensive in comparison with other Audit Policy events.

*****

Set the performance boost for the foreground applications to "None".
This ensures that background applications (SQL Server, for example) will get higher priority than foreground applications.
To set the performance boost for the foreground applications to "None", you can do the following:
1. Double-click the Control Panel System applet and select the Performance tab.
2. On the Application Performance box drag the arrow to set the boost to "None".

*****

You can increase the I/O Page Lock Limit to increase the performance of the reads and writes operations.
Run regedit and choose:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory&nbspManagement
Set IoPageLockLimit to the maximum bytes you can lock for I/O operations.
See this link for more details: How can I improve I/O performance?