Operating System 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, query tuning, and other such activities.

Task Management

As you saw in Chapter 3, the operating system schedules all threads in the system for execution. Each thread of every process has a priority, and Windows NT and Windows 2000 executes the next available 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 and Windows 2000'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 Control Panel in Windows NT or Windows 2000, double-click on the System icon to open the System Properties dialog box. In Windows 2000, you can select the Advanced tab and then click the Performance Options button. Under Optimize Performance For, be sure that the button for Background Services is selected, and not the one for Applications. In Windows NT, you can select the Performance tab, and 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 or Windows 2000 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. In Windows 2000, you can change the properties for file and print services by right-clicking on My Network Places and choosing Properties. Then right-click on Local Area Connection and again choose Properties. Select File And Printer Sharing For Microsoft Networks and click the Properties button. In Windows NT, 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 in Control Panel and click on the Services tab. The Server dialog box appears, as shown in Figure 17-1. This dialog box is almost identical to the one you get in Windows 2000.

click to view at full size.

Figure 17-1. Configuring the server file and print 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 operating system paging 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 I 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 in most cases for reasons of security and robustness, not performance.

Nonessential Services

You should disable any services you don't need. In Windows 2000, you can right-click on My Computer and choose Manage, and in Windows NT, you can use the Services icon in Control Panel. Unnecessary services add overhead to the system and use resources that could otherwise go to SQL Server. If possible, don't use the server that's running SQL Server as a domain controller (DC), the group's file or print server, the Web server, the DHCP server, and so on. You should also consider disabling the Alerter, ClipBook, Computer Browser, Messenger, Network DDE, and Task Scheduler services that are enabled by default but are not needed by SQL Server.

Network Protocols

You should run only the network protocols you actually need for connectivity. Installing multiple network protocols (such as 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.



Inside Microsoft SQL Server 2000
Inside Microsoft SQL Server 2000
ISBN: 0735609985
EAN: 2147483647
Year: 2005
Pages: 179
Authors: Kalen Delaney

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net