Setting Configuration Options

You can manage and optimize Microsoft® SQL Server™ resources through configuration options by using SQL Server Enterprise Manager or the sp_configure system stored procedure. The most commonly used server configuration options are available through SQL Server Enterprise Manager; all configuration options are accessible through sp_configure.

Compared to earlier versions, SQL Server version 7.0 has more internal features for self-tuning and reconfiguring. These features reduce the need for setting server configuration options manually. You should consider the effects on your system carefully before setting these options.

Using the sp_configure System Stored Procedure

When using sp_configure, you must run either RECONFIGURE or RECONFIGURE WITH OVERRIDE after setting a configuration option. Although the RECONFIGURE WITH OVERRIDE statement is usually reserved for configuration options that should be used with extreme caution (for example, setting the allow updates option to 1 allows users to update fields in system tables), you can also use it in place of RECONFIGURE (that is, RECONFIGURE WITH OVERRIDE works for all configuration options).

Following is an example of a script you would use with sp_configure to change the fill factor option from its default setting to a value of 100:

sp_configure 'fill factor', 100

GO

RECONFIGURE

GO

Categories of Configuration Options

Configuration options either take effect:

• Immediately after setting the option and issuing the RECONFIGURE (or in some cases, RECONFIGURE WITH OVERRIDE) statement.
• After doing these actions and stopping and restarting SQL Server.

Advanced options are those that should be changed only by a knowledgeable system administrator or certified SQL Server technician.

To configure an advanced option with sp_configure, you must first run sp_configure with the show advanced options option set to 1, and then run RECONFIGURE.

sp_configure 'show advanced options', 1

GO

RECONFIGURE

GO

sp_configure 'resource timeout', 100

GO

RECONFIGURE

GO

In the earlier example, reconfiguring the resource timeout option to a new value takes place immediately. If you run sp_configure again, the new value for resource timeout appears in the configuration options run_value column.

Some options require a server stop and restart before the new configuration value takes effect. For example, you cannot configure the affinity mask option until you set show advanced options to 1, run RECONFIGURE, and stop and restart the server. If you set the new value and run sp_configure before stopping and restarting the server, the new value appears in the configuration options config_value column, but not in the run_value column. After stopping and restarting the server, the new value appears in the run_value column.

If you use SQL Server Enterprise Manager to change a configuration option, and the configuration option requires a server stop and restart to take effect, SQL Server displays a dialog box asking whether you want to stop and restart the server.

Self-configuring options are those that SQL Server adjusts according to the needs of the system. In most cases, this eliminates the need for setting the values manually. Examples include the min server memory and max server memory options, and the user connections option.

Configuration Options Table

The following table lists all available configuration options, the range of possible settings, and default values. Letter codes next to a configuration option indicate:

• Advanced options (those that should be changed only by a certified SQL Server technician, and require setting show advanced options to 1), marked with “A”.
• Options requiring a server restart to take effect, marked with “RR”.
• Self-configuring options (those that SQL Server self-configures, depending on the needs of the system), marked with “SC”.

See Also