Performance Tuning Windows 2000 When Running SQL Server

Both NT Server 4.0 and Windows Server 2002 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 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 12-20-2000

*****

In Windows 2000, the Performance Monitor Physical Disk object and counters are turned on by default, unlike they are under NT 4.0. But, the Logical Disk object and counters are not turned on. To turn them on, you must run the command

diskperf -yv

at the command prompt, and then rebooting. From personal experience, I don't find the Logical Disk object and counters too useful, and I don't bother turning them on. In addition, not turning them on can reduce overhead on the server by a tiny amount. [2000, 2005] Added 2-27-2001

*****

Since the PAGEFILE.SYS is not used much by SQL Server, and only barely used by Windows 2000 (on a dedicated SQL Server), you don't have to have a huge PAGEFILE.SYS file. Microsoft recommends that the PAGEFILE.SYS file be set to 1.5 times the amount of physical RAM. The exact amount you need depends on what additional SQL Services you may be running. For example, if you are running Full-Text Search service, Microsoft recommends that your PAGEFILE.SYS file be 3 times physical RAM.

Microsoft's recommendations are a good starting point, but the best way to size the PAGEFILE.SYS is to monitor how much of it is used during production using the Performance Monitor Page File Object: % Usage counter, and then resize the PAGEFILE.SYS with a minimum size just slightly larger than the amount that is actually being used (based on the Performance Monitor counter), and with a maximum size of 50MB larger than the minimum size. 

The PAGEFILE.SYS setting can be viewed and changed by right clicking on "My Computer", choosing "Properties", clicking on the "Advanced" tab, clicking on "Performance Options", and clicking on the "Change" button under "Virtual Memory". If you change the virtual memory settings, you will have to reboot your server for the new settings to go into affect. [7.0, 2000, 2005] Added 4-18-2001

*****

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 NT Server 4.0 and Windows 2000 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 NT or 2000 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 12-11-2001 

*****

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 2000 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 2000. 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 12-11-2001