Setting Configuration Options

Setting Configuration Options

SQL Server 2000

  New Information - SQL Server 2000 SP3.

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. Consider the effects on your system carefully before setting these options.

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

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. 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). However, RECONFIGURE WITH OVERRIDE works for all configuration options, and you can use it in place of RECONFIGURE.

The 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
Categories of Configuration Options

Configuration options take effect either:

  • 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 an instance of SQL Server.

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
sp_configure 'cursor threshold', 0

In this example, reconfiguring the cursor threshold 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 the server to be restarted 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 that requires the server to be restarted to take effect, SQL Server displays a dialog box asking if 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, self-configuring options eliminate the need to set 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 their default values. Letter codes signify that an option belongs to one or more of the following categories:

  • A: Advanced options, which should be changed only by a certified SQL Server technician and which require setting show advanced options to 1.

  • RR: Options that require a server restart before taking effect.

  • SC: Self-configuring options.
Configuration option Minimum Maximum Default
affinity mask (A, RR) 0 2147483647 0
allow updates 0 1 0
awe enabled (A, RR) 0 1 0
c2 audit mode (A, RR) 0 1 0
cost threshold for parallelism (A) 0 32767 5
Cross DB Ownership Chaining 0 1 0
cursor threshold (A) –1 2147483647 -1
default full-text language (A) 0 2147483647 1033
default language 0 9999 0
fill factor (A, RR) 0 100 0
index create memory (A, SC) 704 2147483647 0
lightweight pooling (A, RR) 0 1 0
locks (A, RR, SC) 5000 2147483647 0
max degree of parallelism (A) 0 32 0
max server memory (A, SC) 4 2147483647 2147483647
max text repl size 0 2147483647 65536
max worker threads (A, RR) 32 32767 255
media retention (A, RR) 0 365 0
min memory per query (A) 512 2147483647 1024
min server memory (A, SC) 0 2147483647 0
Using Nested Triggers 0 1 1
network packet size (A) 512 65536 4096
open objects (A, RR, SC) 0 2147483647 0
priority boost (A, RR) 0 1 0
query governor cost limit (A) 0 2147483647 0
query wait (A) -1 2147483647 -1
recovery interval (A, SC) 0 32767 0
remote access (RR) 0 1 1
remote login timeout 0 2147483647 20
remote proc trans 0 1 0
remote query timeout 0 2147483647 600
scan for startup procs (A, RR) 0 1 0
set working set size (A, RR) 0 1 0
show advanced options 0 1 0
two digit year cutoff 1753 9999 2049
user connections (A, RR, SC) 0 32767 0
user options 0 32767 0

See Also


Using Options in SQL Server

© 2016 Microsoft