sp_configure (Transact-SQL)
Applies to:
SQL Server
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
Displays or changes global configuration settings for the current server.
Note
For database-level configuration options, see ALTER DATABASE SCOPED CONFIGURATION. To configure Soft-NUMA, see Soft-NUMA (SQL Server).
Transact-SQL syntax conventions
Syntax
Syntax for SQL Server.
sp_configure
[ [ @configname = ] 'configname' ]
[ , [ @configvalue = ] configvalue ]
[ ; ]
Syntax for Analytics Platform System (PDW).
sp_configure
[ ; ]
Arguments
[ @configname = ] 'configname'
The name of a configuration option. @configname is varchar(35), with a default of NULL
. The SQL Server Database Engine recognizes any unique string that is part of the configuration name. If not specified, the complete list of options is returned.
For information about the available configuration options and their settings, see Server configuration options.
[ @configvalue = ] configvalue
The new configuration setting. @configvalue is int, with a default of NULL
.
The maximum value depends on the individual option. To see the maximum value for each option, see the maximum
column of the sys.configurations
catalog view.
Return code values
0
(success) or 1
(failure).
Result set
When executed with no parameters, sp_configure
returns a result set with five columns and orders the options alphabetically in ascending order, as shown in the following table.
The values for config_value
and run_value
aren't automatically equivalent. After you update a configuration setting by using sp_configure
, you must also update the running configuration value, by using either RECONFIGURE
or RECONFIGURE WITH OVERRIDE
. For more information, see the Remarks section.
Column name | Data type | Description |
---|---|---|
name |
nvarchar(35) | Name of the configuration option. |
minimum |
int | Minimum value of the configuration option. |
maximum |
int | Maximum value of the configuration option. |
config_value |
int | Value to which the configuration option was set using sp_configure (value in sys.configurations.value ).For more information about these options, see Server configuration options and sys.configurations. |
run_value |
int | Currently running value of the configuration option (value in sys.configurations.value_in_use ).For more information, see sys.configurations. |
Remarks
Use sp_configure
to display or change server-level settings. To change database-level settings, use ALTER DATABASE
. To change settings that affect only the current user session, use the SET
statement.
Some server configuration options are only available through ALTER SERVER CONFIGURATION.
SQL Server Big Data Clusters
Certain operations, including configuring server (instance level) settings, or manually adding a database to an availability group, require a connection to the SQL Server instance. Operations like sp_configure
, RESTORE DATABASE
, or any DDL command in a database belonging to an availability group require a connection to the SQL Server instance. By default, a big data cluster does not include an endpoint that enables a connection to the instance. You must expose this endpoint manually.
For instructions, see Connect to databases on the primary replica.
Update the running configuration value
When you specify a new @configvalue for a @configname, the result set shows this value in the config_value
column. This value initially differs from the value in the run_value
column, which shows the currently running configuration value. To update the running configuration value in the run_value
column, the system administrator must run either RECONFIGURE
or RECONFIGURE WITH OVERRIDE
.
Both RECONFIGURE
and RECONFIGURE WITH OVERRIDE
work with every configuration option. However, the basic RECONFIGURE
statement rejects any option value that is outside a reasonable range or that might cause conflicts among options. For example, RECONFIGURE
generates an error if the recovery interval value is larger than 60 minutes or if the affinity mask value overlaps with the affinity I/O mask value. RECONFIGURE WITH OVERRIDE
, in contrast, accepts any option value with the correct data type and forces reconfiguration with the specified value.
Caution
An inappropriate option value can adversely affect the configuration of the server instance. Use RECONFIGURE WITH OVERRIDE
cautiously.
The RECONFIGURE
statement updates some options dynamically; other options require a server stop and restart. For example, the min server memory and max server memory server memory options are updated dynamically in the Database Engine. Therefore, you can change them without restarting the server. By contrast, reconfiguring the running value of the fill factor option requires restarting the Database Engine.
After running RECONFIGURE
on a configuration option, you can see whether the option was updated dynamically by executing sp_configure '<configname>'
. The values in the run_value
and config_value
columns should match for a dynamically updated option. You can also check to see which options are dynamic by looking at the is_dynamic
column of the sys.configurations
catalog view.
The change is also written to the SQL Server error log.
If a specified @configvalue is too high for an option, the run_value
column reflects the fact that the Database Engine defaults to dynamic memory, rather than use a setting that isn't valid.
For more information, see RECONFIGURE.
Advanced options
Some configuration options, such as affinity mask and recovery interval, are designated as advanced options. By default, these options aren't available for viewing and changing. To make them available, set the show advanced options configuration option to 1
.
Caution
When the option show advanced options is set to 1
, this setting applies to all users. It's recommended to only use this state temporarily and switch back to 0
when done with the task that required viewing the advanced options.
For more information about the configuration options and their settings, see Server configuration options.
Permissions
Execute permissions on sp_configure
with no parameters or with only the first parameter are granted to all users by default. To execute sp_configure
with both parameters to change a configuration option or to run the RECONFIGURE
statement, you must be granted the ALTER SETTINGS
server-level permission. The ALTER SETTINGS
permission is implicitly held by the sysadmin and serveradmin fixed server roles.
Examples
A. List the advanced configuration options
The following example shows how to set and list all configuration options. You can display advanced configuration options by first setting show advanced options
to 1
. After this option changes, you can display all configuration options by executing sp_configure
with no parameters.
USE master;
GO
EXEC sp_configure 'show advanced options', '1';
Here is the result set.
Configuration option 'show advanced options' changed from 0 to 1. Run the `RECONFIGURE` statement to install.
Run RECONFIGURE
and show all configuration options:
RECONFIGURE;
EXEC sp_configure;
B. Change a configuration option
The following example sets the system recovery interval
configuration option to 3
minutes.
USE master;
GO
EXEC sp_configure 'recovery interval', '3';
RECONFIGURE WITH OVERRIDE;
Examples: Analytics Platform System (PDW)
C. List all available configuration settings
The following example shows how to list all configuration options.
EXEC sp_configure;
The result returns the option name followed by the minimum and maximum values for the option. The config_value
is the value that Azure Synapse Analytics uses when reconfiguration is complete. The run_value
is the value that is currently being used. The config_value
and run_value
are usually the same unless the value is in the process of being changed.
D. List the configuration settings for one configuration name
EXEC sp_configure @configname = 'hadoop connectivity';
E. Set Hadoop connectivity
Setting Hadoop connectivity requires a few more steps in addition to running sp_configure
. For the full procedure, see CREATE EXTERNAL DATA SOURCE.
sp_configure [ @configname = ] 'hadoop connectivity',
[ @configvalue = ] { 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 }
[ ; ]
RECONFIGURE;
[ ; ]
Related content
- ALTER SERVER CONFIGURATION (Transact-SQL)
- RECONFIGURE (Transact-SQL)
- SET Statements (Transact-SQL)
- Server configuration options
- ALTER DATABASE (Transact-SQL)
- System stored procedures (Transact-SQL)
- sys.configurations (Transact-SQL)
- ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)
- Soft-NUMA (SQL Server)