SQL Server Configuration Settings

3 4

SQL Server 2000 is virtually self-tuning, but you can still alter the way your system operates and performs by modifying the settings of certain configuration parameters. In this section, you'll learn how to configure these options and how they affect the operation of your system. You will most likely not need to change these parameters, but knowing what they are and what they do gives you the opportunity to decide whether to modify them. You can configure these parameters by using either Enterprise Manager or the T-SQL sp_configure stored procedure.

To use Enterprise Manager, right-click the name of the server you want to configure and choose Properties from the shortcut menu to display the SQL Server Properties window. This window contains nine tabs, and each tab contains options you can configure. These tabs and their options are explained in the following sections.

When using sp_configure to configure these options, you must remember that certain options are classified as advanced options. (The following sections indicate which options are advanced.) You must set the show advanced options option to 1 (enabled) to change an advanced option by using sp_configure. The option is set to 0 (disabled) by default. (You don't need to worry about this option when you use Enterprise Manager to set advanced options.) To enable show advanced options, use the following statement:

 sp_configure "show advanced options", 1 GO 

In general, to set any option by using sp_configure, use the following syntax:

 sp_configure "option name", value 

The affinity mask Option

The affinity mask option is used to specify which CPUs SQL Server threads can run on in a multiple-processor environment. The default value of 0 specifies that the Windows 2000 scheduling algorithms determine the thread affinity. A nonzero value sets a bitmap defining the CPUs that SQL Server can run on. A decimal value of 1 (or a binary bit mask value of 00000001) indicates that only CPU 1 can be used, 2 (or 00000010) indicates that only CPU 2 can be used, 3 (or 00000011) indicates that CPU 1 and CPU 2 can be used, and so on.

This option is an advanced option, meaning that you must set show advanced options to 1 to configure the option by using sp_configure. You can configure affinity mask by using Enterprise Manager as well. To do so, click the Processor tab in the SQL Server Properties window, and, in the Processor Control area, select the check box next to each CPU that you want SQL Server to use. Click Apply and then click OK to save the change. You must also stop and restart SQL Server for this change to take effect.

On a dedicated SQL Server system, you should set the affinity mask option to allow SQL Server to use all of the CPUs. On a system that is not dedicated to SQL Server (and thus contains other processes that need CPU time), you might want to try setting affinity mask so that SQL Server uses all but one of the CPUs.

The lightweight pooling Option

The lightweight pooling option is used to configure SQL Server to use lightweight threads, or fibers. The use of fibers can reduce context switches by allowing SQL Server (rather than the Windows NT or Windows 2000 scheduler) to handle scheduling. If your application is running on a multiple-processor system and you are seeing a large number of context switches, you might want to try setting the lightweight pooling parameter to 1, which enables lightweight pooling, and then monitoring the number of context switches again to verify that they have been reduced. The default value is 0, which disables the use of fibers.

The lightweight pooling option is an advanced option that you can set by using sp_configure when show advanced options is set to 1. You can also configure lightweight pooling by using Enterprise Manager. Click the Processor tab in the SQL Server Properties window, and, in the Processor Control area, select the Use Windows NT Fibers check box to enable the option, or clear the box to disable the option. Click Apply, click OK, and then stop and restart SQL Server to allow the option to take effect.

The max server memory Option

SQL Server dynamically allocates memory. To specify the maximum amount of memory, in megabytes, that SQL Server will allocate to the buffer pool, you can set the max server memory option. Because SQL Server will take some time to release memory, if you have other applications that periodically need memory, the max server memory option can be set so that SQL Server leaves some memory free for the other applications. The default value is 2147483647, which means that SQL Server will acquire as much memory as it can from the system while dynamically deallocating and allocating memory as other applications need it and release it. This is the recommended setting for a dedicated SQL Server system. If you are going to change this setting, calculate the maximum memory you can give to SQL Server by subtracting the memory needed for Windows 2000 plus the memory needed for any non_SQL Server uses from the total physical memory.

This is an advanced option—you must set show advanced options to 1 to configure this option by using sp_configure. To set the option by using Enterprise Manager, click the Memory tab in the SQL Server Properties window, and adjust the Maximum (MB) slider. Next click Dynamically Configure SQL Server Memory. This option takes effect immediately, without requiring you to stop and restart SQL Server. (If you click Use A Fixed Memory Size, you can set a fixed amount of memory. SQL Server will allocate memory up to that amount and not release memory after the fixed amount has been allocated.)

The min server memory Option

The min server memory option is used to specify the minimum amount of memory, in megabytes, that is to be allocated to the SQL Server buffer pool. Setting this parameter is useful in systems in which SQL Server might reserve too much memory for other applications. For example, in an environment in which the server is used for print and file services as well as for database services, SQL Server might relinquish too much memory to these other applications. This slows user response times.

The default value of min server memory is 0, which allows SQL Server to dynamically allocate and deallocate memory. This is the recommended setting, but you might need to change it if your server is not dedicated to SQL Server.

This option is an advanced optionyou must set show advanced options to 1 to configure this option by using sp_configure. You can also configure this option by using Enterprise Manager. Click the Memory tab in the SQL Server Properties window, adjust the Minimum (MB) slider, and click Dynamically Configure SQL Server Memory. This option takes effect immediately, without requiring you to stop and restart SQL Server.

The recovery interval Option

You can use the recovery interval option to define the maximum amount of time, in minutes, it will take for the system to recover in the event of a failure. SQL Server uses this setting and a special built-in algorithm to determine how often to perform automatic checkpoints so that recovery will take only the specified number of minutes. SQL Server determines how long the interval between checkpoints should be according to how much work is happening in the system. If a lot of work is being performed, checkpoints will be issued more frequently than if the system is not doing much work. The less work being performed, the less time SQL Server needs to recover from a crash. Also, the longer the recovery interval, the longer the interval between checkpoints will be.

Increasing the recovery interval will improve performance by reducing the number of checkpoints. (Checkpoints cause a large number of writes to disk, which might slow down user transactions for a few seconds.) However, it will also increase the amount of time SQL Server requires for recovery. The default value is 0, which specifies that SQL Server will determine the interval for you—about a 1-minute recovery time. Increase the recovery interval option at your own risk. A value of 5 to 15 (minutes) is not unusual, but depends entirely on whether you can risk waiting 5 to 15 minutes for your database to recover in case of a system crash. In general, you might want to increase recovery interval to reduce the frequency of checkpoints and their high number of writes, thereby allowing users more freedom to perform the I/O for their transactions without interruption.

This is an advanced optionyou must set show advanced options to 1 to configure this option by using sp_configure. To set this option by using Enterprise Manager, click the Database Settings tab in the SQL Server Properties window, and enter a value in the Recovery Interval (Min) spin box. A change to this option takes effect immediately, without requiring you to stop and restart SQL Server.



Microsoft SQL Server 2000 Administrator's Companion
Microsoft SQL Server 2000 Administrators Companion
ISBN: B001HC0RPI
EAN: N/A
Year: 2005
Pages: 264

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