Download at Wow! eBook in Visual Basic .NET

Print UPC Symbol in Visual Basic .NET Download at Wow! eBook

1

You can con gure the machine that SQL Server runs on, as well as the Database Engine itself, in several ways and through a variety of interfaces. We ll rst look at some operating system level settings that can affect the behavior of SQL Server. Next, we ll see some SQL Server options that can affect behavior that aren t especially considered to be con guration options. Finally, we ll examine the con guration options for controlling the behavior of SQL Server 2008, which are set primarily using a stored procedure interface called sp_con gure.

For your SQL Server to run well, it must be running on a tuned operating system, on a machine that has been properly con gured to run SQL Server. Although it is beyond the scope of this book to discuss operating system and hardware con guration and tuning, there are a few issues that are very straightforward but can have a major impact on the performance of SQL Server, and we will describe them here.

As you saw in the rst part of this chapter, the operating system schedules all threads in the system for execution. Each thread of every process has a priority, and Windows executes the next available thread with the highest priority. By default, the operating system gives active applications a higher priority, but this priority setting may not be appropriate for a server application running in the background, such as SQL Server 2008. To remedy this situation, the SQL Server installation program modi es the priority setting to eliminate the favoring of foreground applications. It s not a bad idea to double-check this priority setting periodically in case someone has set it back. You ll need to open the Advanced tab of the Performance Options dialog box. If you re using Windows XP or Windows Server 2003, click the Start menu, right-click My Computer, and choose Properties. The System Properties dialog box opens. On the Advanced tab, click the Settings button in the Performance area. Again, select the Advanced tab. If you re using Windows Server 2008, click the Start menu, right-click Computer, and choose Properties. The System information screen opens. Select Advanced System Settings from the list on the left to open the System Properties dialog box. Just as for Windows XP and Windows Server 2003, on the Advanced tab, click the Settings button in the Performance area. Again, select the Advanced tab. You should see the Performance Options dialog box, shown in Figure 1-6.

The rst set of options is for specifying how to allocate processor resources, and you can adjust for the best performance of either programs or background services. Select Background Services so that all programs (both background and foreground) receive equal processor resources. If you plan to connect to SQL Server 2008 from a local client (that is, a client running on the same computer as the server), you can improve processing time by using this setting.

If possible, you should place the operating system paging le on a different drive than the les used by SQL Server. This is vital if your system will be paging. However, a better approach is to add memory or change the SQL Server memory con guration to effectively eliminate paging. In general, SQL Server is designed to minimize paging, so if your memory con guration values are appropriate for the amount of physical memory on the system, such a small amount of page- le activity will occur that the le s location is irrelevant.

1

You should disable any services that you don t need. In Windows Server 2003, you can right-click My Computer and choose Manage. Expand the Services And Applications node in the Computer Management tool, and click Services. In the right-hand pane, you see a list of all the services available on the operating system. You can change a service s startup property by right-clicking its name and choosing Properties. Unnecessary services add overhead to the system and use resources that could otherwise go to SQL Server. No unnecessary services should be marked for automatic startup. Avoid using a server that s running SQL Server as a domain controller, the group s le or print server, the Web server, or the Dynamic Host Con guration Protocol (DHCP) server. You should also consider disabling the Alerter, ClipBook, Computer Browser, Messenger, Network Dynamic Data Exchange (DDE), and Task Scheduler services, which are enabled by default but are not needed by SQL Server.