Windows NT Configuration Settings

As mentioned above, a badly configured system can destroy performance. For example, a system with an incorrectly configured memory setting can break an application. SQL Server dynamically adjusts the most important configuration options for you; you should accept these defaults unless you have a good reason to change them. In certain cases, tweaking the settings rather than letting SQL Server dynamically adjust them might lead to a tiny performance improvement, but your time is probably better spent on application and database design, indexing, tuning queries, and other such activities.

Task Management

As we saw in Chapter 3, Windows NT schedules all threads in the system for execution. Each thread of every process has a priority, and Windows NT executes the next ready thread with the highest priority. By default, it gives active applications a higher priority. But this priority setting is not appropriate for a server application that's running in the background, such as SQL Server. To remedy this situation, SQL Server's installation program eliminates Windows NT's favoring of foreground applications by modifying the priority setting. (It's not a bad idea to periodically double-check this setting in case someone sets it back. From the Windows NT Control Panel, double-click on the System icon to open the System Properties dialog box. On the Performance tab, in the Application Performance section, slide the Select The Performance Boost For The Foreground Application control to the None setting.)

Resource Allocation

A computer running Windows NT Server for file and print services will want to use the most memory for file caching. But if it is also running SQL Server, it makes sense for more memory to be available for SQL Server's use. You can configure the server's resource allocation (and associated nonpaged memory pool usage) in the Server dialog box of the Network applet. Double-click on the Network icon on the Control Panel and click on the Services tab. The Server dialog box appears, as shown in Figure 15-1.

Figure 15-1. Configuring the Server service settings.

The Maximize Throughput For Network Applications setting is best for running SQL Server. You need high network throughput with minimal memory devoted to file caching so more memory is available to SQL Server. When this option is set, network applications such as SQL Server have priority over the file cache for access to memory. Although you might expect that the Minimize Memory Used option would help, it minimizes the memory available for some internal network settings that are needed to support a lot of SQL Server users.

PAGEFILE.SYS Location

If possible, you should place the Windows NT page file on a different drive than the files used by SQL Server. This is vital if your system will be paging. However, an even better approach is to add memory or change the SQL Server memory configuration to effectively eliminate paging. So little page file activity will occur that the file's location will be irrelevant.

File System Selection

As we discussed in Chapter 4, whether you use the FAT or the NTFS file system doesn't have much effect on system performance. You should use NTFS for most cases for reasons of security and robustness, not performance.

Nonessential Services

You should disable any services you don't need, using the Services icon on the Control Panel. These unnecessary services add overhead to the system and use resources that could otherwise go to SQL Server. If possible, don't use the Windows NT server that's running SQL Server as the primary domain controller or backup domain controller (PDC or BDC), the group 's file or print server, the Web server, the DHCP server, and so on. You should also consider disabling the Alerter, ClipBook Server, Computer Browser, Messenger, Network DDE, and Schedule services that are enabled by default but not needed by SQL Server.

Network Protocols

You should run only the network protocols you actually need for connectivity. Installing multiple network protocols (TCP/IP, NWLink, NetBEUI, DLC, and Appletalk) on your system is so easy that some systems run protocols that aren't even used. But this adds overhead. If you legitimately need to use multiple protocols, you should make sure that the highest binding is set for the most often used protocol. You set protocols and bind order via the Control Panel's Network icon.