Setting Server Configuration Options

  • Article

You can manage and optimize SQL Server resources through configuration options by using SQL Server Management Studio or the sp_configure system stored procedure. Some of these options can be configured using the SQL Server Surface Area Configuration tool. The most commonly used server configuration options are available through SQL Server Management Studio; 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 an experienced database 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. However, RECONFIGURE WITH OVERRIDE works for all configuration options, and you can use it in place of RECONFIGURE.

Note

RECONFIGURE executes within a transaction. If any of the reconfigure operations fail, none of the reconfigure operations will take effect.

The value for each option can be determined with the following statement.

SELECT * FROM sys.configurations
ORDER BY name ;
GO

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

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'fill factor', 100;
GO
RECONFIGURE;
GO

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.
    -or-
  • After performing the above actions and restarting the 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;
GO
RECONFIGURE;
GO
sp_configure 'cursor threshold', 0;
GO
RECONFIGURE;
GO

In the previous example, reconfiguring the cursor threshold option takes place immediately. The new value for cursor threshold appears in the configuration options value_in_use column and the value column.

Options that require a restart of the instance of SQL Server will initially show the changed value only in the value column. After restart, the new value will appear in both the value column and the value_in_use column.

Some options require a server restart before the new configuration value takes effect. If you set the new value and run sp_configure before restarting the server, the new value appears in the configuration options value column, but not in the value_in_use column. After restarting the server, the new value appears in the value_in_use column.

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. Configuration options are marked with letter codes as follows:

See Also

Concepts

Using Options in SQL Server

Other Resources

sp_configure (Transact-SQL)
RECONFIGURE (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

12 December 2006
New content:
  • Added the common criteria compliance enabled option to the configuration options table.

14 April 2006
New content:
  • Added the note about using the RECONFIGURE option.
Changed content:
  • Noted that the allow updates option can cause errors.

5 December 2005
New content:
  • Added 64-bit information for max worker threads.
Changed content
  • Indicated Agent XPs may be set to 1 by Setup.
  • Corrected the default user instance time-out.