Displays configuration parameters by group, their current values, their non-default value settings, the value to which they have most recently been set, and the amount of memory used by this setting. Displays only the parameters whose display level is the same as or below that of the user.
sp_configure [configname [, configvalue] | group_name | non_unique_parameter_fragment] 'drop instance' [, instance_name] [display_nondefault_settings]
sp_configure "configuration file", 0, {"write" | "read" | "verify" | "restore"}
"file_name"
displays the current value, default value, most recently changed value, and amount of memory used by the setting for all parameters matching parameter.
resets configname to configvalue and displays the current value, default value, configured value, and amount of memory used by configname.
sp_configure configname, 0, “default” resets configname to its default value and displays current value, default value, configured value, and amount of memory used by configname.
displays all configuration parameters in group_name, their current values, their default values, the value (if applicable) to which they have most recently been set, and the amount of memory used by this setting.
displays all parameter names that match non_unique_parameter_fragment, their current values, default values, configured values, and the amount of memory used.
allows you to drop an instance-specific configuration setting
in cluster environments – indicates the instance for which you are setting the instance-specific options.
displays configuration options for which the configuration or run value is different from the default value.
creates file_name from the current configuration. If file_name already exists, a message is written to the error log and the existing file is renamed using the convention file_name.001, file_name.002, and so on. If you have changed a static parameter but have not restarted your server, “write” gives you the currently running value for that parameter.
performs validation checking on values contained in file_name and reads those values that pass validation into the server. If any parameters are missing from file_name, the current running values for those parameters are used.
performs validation checking on the values in file_name.
creates file_name with the values in sysconfigures. This is useful if all copies of the configuration file have been lost and you need to generate a new copy.
is the name of the file you want to use sp_configure on.
Displays all configuration parameters by group, their current values, their default values, the value (if applicable) to which they have most recently been set, and the amount of memory used by this setting:
sp_configure
Displays all configuration parameters that include the word “identity”:
sp_configure "identity"
Configuration option is not unique. Parameter Name Default Memory Used Config Value Run Value Unit Type -------------- ------- ----------- ------------ --------- ------ ---- identity burning set 1 0 1 1 id static identity grab size 0 0 0 0 id dyna size of auto identit 10 0 10 10 bytes dyna . . .
Sets the system recovery interval in minutes to 3 minutes:
sp_configure "recovery interval in minutes", 3
Parameter Name Default Memory Used Config Value Run Value Unit Type -------------- ------- ----------- ------------ --------- ------ ---- recovery interval 5 0 3 3 min dyn Configuration option changed. The SQL Server need not be rebooted since the option is dynamic.
Resets the value for number of devices to the Adaptive Server default:
sp_configure "number of device", 0, "default"
Configures four databases to be recovered concurrently, enter:
sp_configure "max concurrently recovered db", 4
Starts four checkpoint tasks, enter:
sp_configure "number of checkpoint tasks", 4
Captures Query Processing metrics (qp metrics) at the server level:
sp_configure "enable metrics capture", 1
Performs validation checking on the values in the file srv.config and reads the parameters that pass validation into the server. Current run values are substituted for values that do not pass validation checking:
sp_configure "configuration file", 0, "read", "srv.config"
Runs validation checking on the values in the file restore.config:
sp_configure "configuration file", 0, "restore", "generic.config"
Creates the file my_server.config and writes the current configuration values the server is using to that file:
sp_configure "configuration file", 0, "write", "my_server.config"
Performs a validation check on the values in $SYBASE/backup_config.cfg:
sp_configure "configuration file", 0, "verify", "$SYBASE/backup_config.cfg"
Any user can execute sp_configure to display information about parameters and their current values, but not to modify parameters. System administrators can execute sp_configure to change the values of most configuration parameters. Only system security officers can execute certain parameters. These are listed under “Permissions” in this section.
sp_configure allows you to specify the value for configuration paramters in unit specifiers. The unit specifiers are p or P for pages, m or M for megabytes, g or G for gigabytes, and t or T for terabytes. If you do not specify a unit, and you are configuring a parameter that controls memory, Adaptive Server uses the logical page size for the basic unit.
When you execute sp_configure to modify a dynamic parameter:
The configuration and run values are updated.
The configuration file is updated.
The change takes effect immediately.
When you execute sp_configure to modify a static parameter:
The configuration value is updated.
The configuration file is updated.
The change takes effect only when you restart Adaptive Server.
When issued with no parameters, sp_configure displays a report of all configuration parameters by group, their current values, their default values, the value (if applicable) to which they have most recently been set, and the amount of memory used by this setting:
The default column in the report displays the value Adaptive Server is shipped with. If you do not explicitly reconfigure a parameter, it retains its default value.
The memory used column displays the amount of memory used by the parameter at its current value in kilobytes. Some related parameters draw from the same memory pool. For instance, the memory used for stack size and stack guard size is already accounted for in the memory used for number of user connections. If you added the memory used by each of these parameters separately, it would total more than the amount actually used. In the memory used column, parameters that “share” memory with other parameters are marked with a hash mark (#).
The config_value column displays the most recent value to which the configuration parameter has been set with sp_configure.
The run_value column displays the value being used by Adaptive Server. It changes after you modify a parameter’s value with sp_configure and, for static parameters, after you restart Adaptive Server. This is the value stored in syscurconfigs.value.
If the server uses a case-insensitive sort
order, sp_configure with no parameters
returns a list of all configuration parameters and groups in alphabetical
order with no grouping displayed.
Each configuration parameter has an associated display level. There are three display levels:
The “basic” level – displays only the most basic parameters. It is appropriate for very general server tuning.
The “intermediate” level – displays parameters that are somewhat more complex, as well as showing you all the “basic” parameters. This level is appropriate for a moderately complex level of server tuning.
The “comprehensive” level – default display level. Displays all parameters, including the most complex ones. This level is appropriate for users who do highly detailed server tuning.
Setting one of the other display levels lets you work with a subset of the configuration parameter, shortening the amount of information displayed by sp_configure.
The syntax for showing your current display level is:
sp_displaylevel
sp_configure can run in sessions using chained transaction mode if there are no open transactions.
For information on the individual configuration parameters, see the System Administration Guide.
If no configuration option or instance name is specified, the information displayed depends on the system_view setting.
If no configuration option is specified, and the instance name is specified, Adaptive Server displays all instance-specific configuration settings for the specified instance.
If the configuration option is specified, but the configuration value and instance name are not specified, Adaptive Server displays the current settings for the specified option for all instances under the “cluster” view. If the instance name is specified, Adaptive Server displays configuration information for the specified instance.
If the configuration option and value are specified, but no instance is specified, Adaptive Server configures the cluster-wide setting for the option. If, however, the instance name is specified, Adaptive Server sets the configuration value for the instance only. The syntax is:
sp_configure configuration_name, config_value, NULL, instance_name
You cannot set configuration options from inside a local temporary database.
If an instance already has instance-specific setting for a configuration parameter set, you can reconfigure this parameter for a cluster-wide setting.
A user can reconfigure only those instances to which they are connected.
This parameter determines the degree of parallelism during database recovery:
When Adaptive Server is not in recovery, this configuration parameter takes effect statically. However, when Adaptive Server is in recovery, a system administrator can force serial recovery dynamically.
The effectiveness of max concurrently recovered db is dependent on the database layout and the performance of underlying I/O subsystem.
This parameter configures parallel checkpoints:
Parallel checkpoints depend on the layout of the databases and performance of underlying I/O sybsystems. Tune this parameter depending on the number of active databases and the ability of the I/O subsystem to handle writes.
This configuration parameter is dynamic. When the value for this parameter is reduced, checkpoint tasks drain out, and when the value is increased, additional tasks are created.
Any user can execute sp_configure to display information about parameters and their current values.
Only system administrators can execute sp_configure to modify values for:
enable logins during recovery
enable semantic partitioning
max concurrently recovered db
number of checkpoint tasks
Only system administrators and system security officers can execute sp_configure to modify configuration parameters.
Only system security officers can execute sp_configure to modify values for:
allow procedure grouping allow select on syscomments.text allow updates auditing current audit table |
enable encrypted columns remote access restricted decrypt permission suspend auditing when full systemwide password expiration |
System administrators can modify all other parameters.
Values in event and extrainfo columns from the sysaudits table are:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
|---|---|---|---|
38 |
exec_procedure |
Execution of a procedure |
|
73 |
Automatically audited event nto controlled by an option. |
Turning the auditing parameter on with sp_configure |
|
74 |
Automatically audited event nto controlled by an option. |
Turning the auditing parameter off with sp_configure |
|
82 |
security |
sp_configure |
|
For more information on max concurrently recovered db and number of checkpoint tasks, see Chapter 27, “Backing up and Restoring User Databases,” in the System Administration Guide.
Commands set
System procedures sp_dboption, sp_displaylevel, sp_helpconfig, sp_monitorconfig, set
