Displays configuration parameters by group, their current values, their default values, 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][number of histogram steps, n]
sp_configure "configuration file", 0, {"write" | "read" | "verify" | "restore"}
"file_name"
sp_configure “max concurrently recovered db”, config_value
sp_configure “number of checkpoint tasks”, config_value
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.
is the name of the file you want to us sp_configure on.
used when creating an index or running update statistics where you can specify the number of steps when using create index or update statistics. The default is 20.
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.
sets the limit to the number of databases recovered concurrently.
The default value of config_value is 0, where Adaptive Server determines the number of databases recovered concurrently, based on a self-tuning approach. The number of engines and number of open databases limit the value of this configuration parameter. There is no absolute maximum.The value of 1 indicates serial recovery.
sets the limit to the number of checkpoint tasks in Adaptive Server.
The default value of config_value is 1, indicating serial checkpoints. The number of engines and number of open databases limit number of checkpoint tasks. It has an absolute ceiling of 8.
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"
To reset the default number of steps for create index and update statistics:
sp_configure 'number of histogram steps', 30
To configure 4 databases to be recovered concurrently, enter:
sp_configure "max concurrently recovered db", 4
To configure Adaptive Server for the self-tuning approach, enter:
sp_configure "max concurrently recovered db", 0
To configure Adaptive Server to have serial recovery, enter:
sp_configure "max concurrently recovered db", 1
To start four checkpoint tasks, enter:
sp_configure "number of checkpoint tasks", 4
To revert to serial checkpoints, enter:
sp_configure "number of checkpoint tasks", 1
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, and g or G for gigabytes. 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 displays all parameters, including the most complex ones. This level is appropriate for users who do highly detailed server tuning.
The default display level is “comprehensive”. 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
For information on the individual configuration parameters, see the System Administration Guide.
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:
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
audit queue size
auditing
current audit table
remote access
suspend auditing when full
systemwide password expiration
System Administrators can modify all other parameters.
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
