9.3 Changing the server global configuration

9.3 Changing the server global configuration

9.3.1 using sp_configure to execute queries

sp_configure can display or change global configuration settings for the current server.

If executed without parameters, the result set returned by sp_configure has 5 columns and 17 rows, sorted alphabetically by the 1th column.

9.3.2 Configuring Basic parameters

sp_configure can take up to 1 or 2 parameters to modify configuration options. The 1th parameter is the name of the configuration option, and the 2nd parameter is the value assigned to the configuration option.

The following example sets system backup compression default to True.

sp_configure ' backup compression default ', 1

After successful execution, the following message is displayed.

Configuration options ' backup compression default ' has changed from 0 to 1. Please run the RECONFIGURE statement to install.

The RECONFIGURE statement can dynamically update certain options, while updates to other options need to stop the server and restart for implementation. For example, in the database engine, the min server memories and max server memory options are dynamically updated, so you can change both options without restarting the server. Conversely, when you reconfigure the run value of the Fill factor option, you need to restart the database engine.

After you run RECONFIGURE on a configuration option, you can see whether the option has been dynamically updated by executing sp_configure ' option_name '. For dynamically updated options, the values of the Run_value column and the config_value column should match.

Tips:

You can determine which options are dynamic by looking at the is_dynamic column of the Sys.configurations catalog view.

RECONFIGURE with OVERRIDE forces a reconfiguration with the specified value and does not check that the specified value is valid. For example, you can configure the min server memory configuration option by using the value specified in the max server memory configuration option. Even if this is considered to be wrong, specifying RECONFIGURE with OVERRIDE will not check whether the value of the configuration option is valid.

9.3.3 Configuring Advanced Parameters

Some configuration options, such as affinity mask and recovery interval, are specified as advanced options. By default, these options cannot be viewed and changed. To make these options available, you will need to set the Show Advanced option configuration option to 1 to display the premium configuration options. When this option is changed, execution with sp_configure without parameters will display all configuration options.

EXEC sp_configure ' show advanced option ', ' 1 ';

Run RECONFIGURE again, and then run sp_configure with no parameters to display all configuration options, SQL Server 2014 returns 70 rows.

RECONFIGURE; EXEC sp_configure;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6C/16/wKiom1U_SbSBzElFAAQ7MEZ3lbI760.jpg "title=" sp_ Configure.png "alt=" Wkiom1u_sbsbzelfaaq7mez3lbi760.jpg "/>

This article from "SQLServer2014 series" blog, declined reprint!

9.3 Changing the server global configuration