Server configuration options

Applies to: SQL Server Azure SQL Managed Instance

You can manage and optimize SQL Server and Azure SQL Managed Instance resources through configuration options by using SQL Server Management Studio or the sp_configure system stored procedure. The most commonly used server configuration options are available through SQL Server Management Studio; all configuration options are accessible through sp_configure. Consider the effects on your system carefully before setting these options. For more information, see View or change server properties (SQL Server).

Configuration option changes take effect only after issuing the RECONFIGURE (or in some cases, RECONFIGURE WITH OVERRIDE) statement. If you don't see the effect of a configuration change, check to see that the run value of the configuration option has changed.

You can use the sys.configurations catalog view to determine the config value (the value column) and the run value (the value_in_use column), and whether the configuration option requires a Database Engine restart (the is_dynamic column).

If the Database Engine needs to restart, options show the changed value only in the value column. After restart, the new value appears in both the value column and the value_in_use column.

Some options require a server restart before the new configuration value takes effect. If you set the new value and run sp_configure before restarting the server, the new value appears in the value column of the sys.configurations catalog view, but not in the value_in_use column. When you restart the server, the new value appears in the value_in_use column.

Reconfiguring certain options invalidates plans in the plan cache, causing new plans to be compiled. For more information, see DBCC FREEPROCCACHE.

Self-configuring options are options that SQL Server adjusts according to the needs of the system. In most cases, this eliminates the need for setting the values manually. Examples include the max worker threads option and the user connections option.

The following query can be used to determine if any configured values have been configured but aren't in effect:

SELECT *
FROM sys.configurations
WHERE [value] <> [value_in_use];

If the value is the change for the configuration option you made but the value_in_use isn't the same, either the RECONFIGURE command wasn't run or has failed, or the Database Engine must be restarted.

There are two configuration options where the value and value_in_use might not be the same, which is the expected behavior:

• max server memory (MB) - The default configured value of 0 displays as 2147483647 in the value_in_use column.

• min server memory (MB) - The default configured value of 0 might display as 8 on 32-bit systems, or 16 on 64-bit systems, in the value_in_use column. In some cases, if the value_in_use shows as 0, the true value_in_use is 8 (32-bit) or 16 (64-bit).

The is_dynamic column can be used to determine if the configuration option requires a restart. A value of 1 in the is_dynamic column means that, when the RECONFIGURE command is run, the new value takes effect immediately. In some cases, the Database Engine might not evaluate the new value immediately, but does so in the normal course of its execution. A value of 0 in the is_dynamic column means that the changed configuration value doesn't take effect until the Database Engine is restarted, even though the RECONFIGURE command was run.

For a configuration option that isn't dynamic there's no way to tell if the RECONFIGURE command has been run to apply the configuration change. Before you restart the Database Engine to apply the configuration change, run the RECONFIGURE command to ensure all configuration changes take effect when the Database Engine restarts.

The following table lists all available configuration options, the range of possible settings, the default values, and the supported product (SQL Server or Azure SQL Managed Instance). Configuration options are marked with letter codes as follows:

• A = Advanced options, which should be changed only by an experienced database administrator or a certified SQL Server professional, and which require setting show advanced options to 1.

• RR = Options requiring a restart of the Database Engine.

• RP = Options that require a restart of the PolyBase Engine.

• SC = Self-configuring options.

¹ Changes to 1 when SQL Server Agent is started. Default value is 0 if SQL Server Agent is set to automatic start during Setup.

² Zero (0) autoconfigures the number of max worker threads depending on the number of logical processors. For more information, see the automatically configured number of max worker threads.

• sp_configure (Transact-SQL)
• RECONFIGURE (Transact-SQL)
• DBCC FREEPROCCACHE (Transact-SQL)