When running Windows 2000, you will need to ensure that all the hardware you run it on, and their related drivers, have been tested for use with Windows 2000. Using an outdated or buggy driver can wreck havoc with performance. [7.0, 2000, 2005] Updated 11-28-2003
*****
If you want to upgrade a current server running NT Server 4.0, you will be best off if you install Windows 2000 from scratch, instead of using the upgrade procedure included with Windows 2000. 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 being upgraded, fragmented drives not being defragmented, and so on. [7.0, 2000, 2005]
*****
Install Windows 2000 as a stand-alone server, not as a domain controller, for use by SQL Server. Domain controllers have extra overhead and perform functions not required by SQL Server. Along the same lines, don’t install any unnecessary server components, such as DNS, DHCP, etc, on your SQL Server box. The goal is to dedicate all of the server’s power to SQL Server. [6.5, 7.0, 2000, 2005] Updated 11-28-2003
*****
Windows 2000 supports a larger MTU (maximum transmission unit) window size than Windows NT 4.0, ranging from 1.5KB to 9KB. The larger the MTU window size, the fewer packets that have to be sent over the network, reducing both server and network overhead. To implement this new Windows 2000 feature, you will have to use a network card that supports the new larger, 9KB MTU window size, and configure this setting at the network card. [7.0, 2000, 2005]
*****
If your server has plenty of RAM (and SQL Server is not currently experiencing any memory bottlenecks), consider telling the operating system that you don’t want pageable drivers and system code located in the Windows Executive to be paged to disk. By doing this, you reduce some paging on your server and speed up some internal operating system processes. This works for all versions of Windows NT Server 4.0 and Windows 2000.
To do this, you must manually modify the registry. What you need to do is to change the DisablePagingExecutive entry from 0 to 1 in this registry subkey: HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSession
ManagerMemory Management While Microsoft’s instructions on how to do this don’t mention whether or not you should reboot your server after making this change, it is probably a good idea. If your SQL Server does not have “excess” memory, then don’t use this technique. [6.5, 7.0, 2000, 2005] Updated 11-28-2003
*****
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 2000. This fixes disk fragmentation and boosts disk I/O. [7.0, 2000, 2005]
*****
Set the “Application Response” setting to “Optimize Performance for Background Services”. This ensures that all applications running on Windows 2000 (foreground and background) get an equal shot at the CPU. Set this option by going to the “System” icon in the “Control Panel”, then select the “Advanced” tab, and then select the “Performance Options” button. [7.0, 2000, 2005] Updated 11-28-2003
*****
Format all the disk arrays on your server using NTFS 5.0, the new NTFS file system format included (but not required) for Windows 2000. The new format includes some new performance enhancements, which mean fewer disk accesses to find files, and generally overall faster disk reads. Before Windows 2000, some DBAs formatted the drives or disk arrays dedicated to log files as FAT because it had a small performance benefit over NTFS 4.0. This is no longer true under NTFS 5.0. [7.0, 2000, 2005] Updated 12-28-2001
*****
Avoid using NTFS data file encryption 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 11-28-2003
*****
This is not a performance tip, but it resolves an irritating warning message that appears in the Windows NT Server 4.0 and Windows 2000 Event Viewer logs. Have you ever seen this message: “Error 2013 – Disk Is At Or Near Capacity“. This message appears when the free space on a logical disk drive gets below 10%. If your logical disk drive is over 100GB, like many of mine are, you get this message whenever free space gets below 10GB. In most cases, this error message is not really helpful, especially if you have large logical drives as I do. Fortunately, you can modify the operation system to prevent these annoying warning messages. The following works for all versions of both Windows NT Server 4.0 and Windows 2000.
To make this modification, you have to manually modify the operating system’s registry, using these steps:
· Go to this registry subkey:
HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesLanmanServer
Parameters
· From the “Edit” menu, select “Add Value”.
· Enter “DiskSpaceThreshold” in the “Value Name” field. Don’t enter the quote marks.
· Change the “Data Type” to “REG_DWORD”. Don’t enter the quote marks.
· In the “Data” field, enter the percentage value you want to use instead of the default 10%. For example, if you want to be notified (receive the warning message) when your drive has less than 5% of free space, enter a “5”. Don’t enter the quote marks.
Save your changes and reboot the server, and you are all done. [6.5, 7.0, 2000, 2005] Updated 11-28-2003