Setting the Configuration Options

Configuration options are displayed and managed by using either SQL Enterprise Manager or the sp_configure system procedure. For information about using sp_configure, see the Microsoft SQL Server Transact-SQL Reference. For information about using SQL Enterprise Manager, see the instructions that follow.

Note There are two types of configuration options: standard and advanced. Advanced options should be changed only under very special and usually rare circumstances. Advanced options are displayed and can be managed only when show advanced option has been set to 1.

    To display or set the configuration options
  1. From the Microsoft SQL Server 6.0 program group, start SQL Enterprise Manager.
  2. From the Server Manager window, open a server group and select a server.
  3. From the Server menu, choose Configurations.
  4. From the Server Configuration dialog box, choose the Configuration tab.

    The Configuration window displays a list of the configuration options.

  5. To display information about a listed option, select the option.

    The current setting of the option appears in the Running column. An explanation of the option appears in the Description box.

  6. To change an option, select the Current box for that option, and then type the new value. Repeat as necessary for each option to be changed.

    For a list of the available configuration options, their minimum and maximum acceptable values, and their default settings, see Configuration Options and Values, later in this chapter. For detailed information describing each configuration option and discussing the effects of changing its value, see the sp_configure System Stored Procedure in the Microsoft SQL Server Transact-SQL Reference.

  7. To apply a change immediately, choose the Apply Now button.
  8. When you finish setting the options, choose the OK button.

    Note that some options take effect immediately, whereas others require the server to be restarted. For more information, see the Microsoft SQL Server Transact-SQL Reference.

For more information about each of the standard and advanced configuration options, see sp_configure System Stored Procedure in the Microsoft SQL Server Transact-SQL Reference.

For more information about setting the memory and tempdb in RAM options, see Setting the Memory Option and When to Use Tempdb in RAM, later in this chapter.