Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jun 3, 2002

Operation System Optimization Tips

By Alexander Chigrik


Here are thirteen little known tips that you can use to optimize your Windows servers to perform in the most efficient manner possible.


1. Set a reasonable size for 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.


2. Create another pagefile.sys files on every separate physical drive (With the exception of the drive containing 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.


3. 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.


4. 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.


5. You can increase performance by disabling the last access update file's date.

Run regedit and choose:
HKLM\SYSTEM\CurrentControlSet\Control\FileSystem\NtfsDisableLastAccessUpdate

Add key NtfsDisableLastAccessUpdate as REG_DWORD and set it to "1".


6. Use a minimum of 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.


7. Use minimum services on the server box.

Try to remove from startup your 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 then start these services manually when you need them.


8. 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.


9. 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.


10. 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 wise idea to not use them on a server box.


11. 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 not to use "File and Objects Access" and "Process Tracking" Audit Policy events, as they are the most resource intensive in comparison with other Audit Policy events.


12. 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".


13. You can increase the I/O Page Lock Limit to increase the performance of the read and write operations.

Run regedit and choose:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management

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?


» See All Articles by Columnist Alexander Chigrik




MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM