Performance Tuning Windows 2003 When Running SQL Server

If you ignore the advice give on this website about dedicating a single physical server to each of your SQL Servers, and run multiple applications on your server, disk paging may become an issue. Once way to help optimize the performance of a paging file is to ensure that it is contiguous, not fragmented.

Third-party defrag tools, such as Diskeeper, can be used to defragment a swap file, but the built-in defragger that comes Windows 2003 is not able to defrag a swap file. You don’t have to purchase a third-party tool to defrag a paging file if you don’t want to. You can choose to defrag a paging file manually.

Here’s how. First, set the minimum and maximum size of your server’s paging file to 0MB. This will produce an error message, but ignore it, then reboot your server. Next, run the build-in defrag tool that comes with Windows 2003. Once you are done, then reset the size of the server’s paging file to an appropriate amount (probably back to it’s original settings) and then reboot your server. [6.5, 7.0, 2000, 2005] Added 5-7-2003


For best performance of SQL Server running under Windows 2003, turn off any operating 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 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 set 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. [6.5, 7.0, 2000, 2005] Added 5-7-2003


Remove any unnecessary network protocols from your server. 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. [6.5, 7.0, 2000, 2005] Added 5-7-2003


Windows 2003 supports both Basic and Dynamic disks types. From a performance perspective, one type of disk is not faster than the other. Because of this, you should decide on the type of disk type you choose based on what your I/O needs are. For example, if you want to cluster SQL Server, you have no choice but to use the Basic disk type on the shared disk array. [6.5, 7.0, 2000, 2005] Added 5-7-2003


Pages: 1 2 3


No comments yet... Be the first to leave a reply!