Performance Tuning Windows 2003 When Running SQL Server

Should you run SQL Server on NT Server 4.0, Windows 2000, or Windows 2003 for the best performance? Each new version of the Windows operating system offers new performance benefits. While the leap from NT Server 4.0 to Windows 2000 was not large, the leap in performance from Windows 2000 to Windows 2003 is very large. If you are looking for a performance boost in SQL Server, seriously consider upgrading to Windows 2003.

Some of the new performance-enhancing features of Windows 2000 include:

• The ability to take better advantage of Intel hyper-threading CPUs.
• Up to 32 CPUs and 64GB of RAM are now supported using Intel chips, and up to 64 CPUs and 512GB of RAM are supported using Itanium chips.
• Up to eight servers can now be included in a cluster.
• I/O path and disk I/O performance have been substantially boosted, while at the same time reducing the amount of CPU resources needed to service I/O requests.
• The TCP/IP protocol has been enhanced and is up to 25% faster than Windows 2000.
• Included with 2003 is a new tool called "Performance Options." It consolidates processor, memory, and virtual memory settings to make it easier to configure your server for optimum performance.
• Includes with 2003 are new command-line performance-related tools to better monitor performance. They include logman, relog, tracerpt, and typeperf.
• System Monitor can now log performance data directly into a SQL Server database using an ODBC connection.
• System Monitor log files can be larger than 1GB and can have data appended to the log file.

[7.0, 2000, 2005] Updated 4-17-2006

*****

You will need to ensure that all the hardware you run 2003 on, and their related drivers, have been tested for use with Windows 2003. Using an outdated or buggy driver can wreck havoc with performance, and can potentially corrupt data. [7.0, 2000, 2003] Updated 4-17-2006

*****

If you want to upgrade a current server running NT Server 4.0 or Windows 2000 to Windows 2003, you will be best off if you install Windows 2003 from scratch, instead of using the upgrade procedure included with Windows 2003. Ideally, reformat all of the drives and start completely fresh. Upgrading can introduce hard to identify performance problems, such as bad or outdated drivers not upgraded, fragmented drives not being defragmented, and so on. [7.0, 2000, 2005] Updated 4-17-2006

*****

Install Windows 2003 as a stand-alone server, not as a domain controller. Domain controllers have extra overhead and perform functions not required by SQL Server. Along the same lines, do not install any unnecessary server components, such as DNS, DHCP, etc, on your SQL Server. The goal is to dedicate all of the server's power to SQL Server. [7.0, 2000, 2003] Updated 4-17-2006

*****

Defragment the drives or arrays regularly using the built-in Disk Defragmenter (part of the Computer Management Console), or using a third-party tool designed for Windows 2003. This fixes disk fragmentation and boosts disk I/O. [7.0, 2000, 2005] Updated 4-17-2006

*****

Format all the disk arrays on your server using NTFS, the NTFS file system format included (but not required) for Windows 2003. The new format includes some new performance enhancements, which mean fewer disk accesses to find files, and generally overall faster disk reads. [7.0, 2000, 2005] Updated 4-17-2006

*****

Avoid using NTFS data file encryption (EFS) and compression on SQL Server database and log files. While the performance hit is minimal on small, lightly used databases, these features will noticeable affect performance on larger, busy databases. [7.0, 2000, 2005] Updated 4-17-2006

*****

Windows 2003 allow you to audit specific events that occur on the server, and to write these events to the Event Viewer Security log. While this can be useful if you have a security problem and want to see what is happening on your server, using auditing indiscriminately can be a performance drain, especially if you are auditing process activity of file access.

Ideally, on your production SQL Servers, auditing should be turned off. If you need to perform a security audit, then turn it on, perform the audit, and then turn auditing off. If your security staff mandates the use of auditing, do your best to persuade them to minimize the amount of auditing they do. [6.5, 7.0, 2000, 2005] Added 5-7-2003

*****

If you don't follow the advice given elsewhere on this website about dedicating your SQL Server's to a physical server of their own, and you decide to run multiple applications on the same server (SQL Server plus others), you may be faced with more paging activity than you would normally experience on a dedicated SQL Sever (which has very minimal paging).

If this is the case, one way to help boost performance on your non-dedicated server is to spread the paging file over multiple disk or disk arrays. You can spread a paging file in Windows 2003 onto as many as 16 separate files. By spreading out the paging file among more than one physical drive, simultaneous I/O requests can occur, speeding up access to the paging file (similar to disk striping). If the paging file is located on a single drive (the default setting when 2003 is installed), along with the operating system and other applications, then all these processes have to compete for I/O, which can produce bottlenecks. The more physical devices that the paging file can be distributed, the less potential for I/O bottlenecks, and performance is boosted.

Keep in mind that you must distribute the paging file over physical drives, not drive partitions on a single drive, for this to work. In fact, if you were to distribute a paging file over multiple partitions on the same physical drive, you would in fact reduce the performance of the paging file even more than if you had left it on one partition.

If your SQL Server has its own dedicated physical server, you don't have to worry about spreading the paging file among multiple physical drives as there is not enough paging activity to make this effort worthwhile. [6.5, 7.0, 2000, 2005] Added 5-7-2003