Server configuration options (SQL Server)
Applies to: 
SQL Server
You can manage and optimize SQL Server 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).
Important
Advanced options should be changed only by an experienced database administrator or certified SQL Server technician.
Categories of configuration options
If you don't see the effect of a configuration change, it may not be installed. Check to see that the run_value of the configuration option has changed.
Configuration options take effect immediately after setting the option and issuing the RECONFIGURE (or in some cases, RECONFIGURE WITH OVERRIDE) statement. Reconfiguring certain options will invalidate plans in the plan cache, causing new plans to be compiled. For more information, see DBCC FREEPROCCACHE (Transact-SQL).
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 SQL Server needs to restart, options will initially show the changed value only in the value column. After restart, the new value will appear 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.
Note
The config_value in the result set of sp_configure is equivalent to the value column of the sys.configurations catalog view, and the run_value is equivalent to the value_in_use column.
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 haven't been installed:
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
0will display as2147483647in thevalue_in_usecolumn.min server memory (MB) - The default configured value of
0might display as8on 32-bit systems, or16on 64-bit systems, in thevalue_in_usecolumn. In some cases, if thevalue_in_useshows as0, the truevalue_in_useis8(32-bit) or16(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 will take effect immediately. In some cases, the Database Engine might not evaluate the new value immediately but will do so in the normal course of its execution. A value of 0 in the is_dynamic column means that the changed configuration value won'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 is no way to tell if the RECONFIGURE command has been run to apply the configuration change. Before you restart SQL Server to apply the configuration change, run the RECONFIGURE command to ensure all configuration changes will take effect when SQL Server next restarts.
Configuration options
The following table lists all available configuration options, the range of possible settings, and default values. 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 optionsto1.RR = Options requiring a restart of the Database Engine.
RP = Options that require a restart of the PolyBase Engine.
SC = Self-configuring options.
| Configuration option | Minimum value | Maximum value | Default |
|---|---|---|---|
| access check cache bucket count (A) | 0 | 16384 | 0 |
| access check cache quota (A) | 0 | 2147483647 | 0 |
| ad hoc distributed queries (A) | 0 | 1 | 0 |
| ADR cleaner retry timeout (min) Applies to: SQL Server 2019 (15.x) and later versions. |
0 | 32767 | 120 |
| ADR Preallocation Factor Applies to: SQL Server 2019 (15.x) and later versions. |
0 | 32767 | 4 |
| affinity I/O mask (A, RR) | -2147483648 | 2147483647 | 0 |
| affinity mask (A) | -2147483648 | 2147483647 | 0 |
| affinity64 I/O mask (A, only available on 64-bit version of SQL Server) | -2147483648 | 2147483647 | 0 |
| affinity64 mask (A, RR), only available on 64-bit version of SQL Server | -2147483648 | 2147483647 | 0 |
| Agent XPs (A) | 0 | 1 | 0 Changes to 1 when SQL Server Agent is started. Default value is 0 if SQL Server Agent is set to automatic start during Setup. |
| allow polybase export Applies to: SQL Server 2016 (13.x) and later versions. |
0 | 1 | 0 |
| allow updates (Obsolete. Don't use. Will cause an error during reconfigure.) | 0 | 1 | 0 |
| automatic soft-NUMA disabled | 0 | 1 | 0 |
| backup checksum default | 0 | 1 | 0 |
| backup compression default | 0 | 1 - versions prior to SQL Server 2022 (16.x) 2 - SQL Server 2022 (16.x) and later versions |
0 |
| backup compression algorithm (A) Applies to: SQL Server 2022 (16.x) and later versions. |
0 | 1 | 0 |
| blocked process threshold (A) | 5 | 86400 | 0 |
| c2 audit mode (A, RR) | 0 | 1 | 0 |
| clr enabled | 0 | 1 | 0 |
| clr strict security (A) Applies to: SQL Server 2017 (14.x) and later versions. |
0 | 1 | 0 |
| column encryption enclave type (A, RR) | 0 | 2 | 0 |
| common criteria compliance enabled (A, RR) | 0 | 1 | 0 |
| contained database authentication | 0 | 1 | 0 |
| cost threshold for parallelism (A) | 0 | 32767 | 5 |
| cross db ownership chaining | 0 | 1 | 0 |
| cursor threshold (A) | -1 | 2147483647 | -1 |
| Database Mail XPs (A) | 0 | 1 | 0 |
| default full-text language (A) | 0 | 2147483647 | 1033 |
| default language | 0 | 9999 | 0 |
| default trace enabled (A) | 0 | 1 | 1 |
| disallow results from triggers (A) | 0 | 1 | 0 |
| EKM provider enabled | 0 | 1 | 0 |
| external scripts enabled (SC) (RR) Applies to: SQL Server 2016 (13.x) and later versions. |
0 | 1 | 0 |
| filestream access level | 0 | 2 | 0 |
| fill factor (A, RR) | 0 | 100 | 0 |
| ft crawl bandwidth (max)(A) | 0 | 32767 | 100 |
| ft crawl bandwidth (min)(A) | 0 | 32767 | 0 |
| ft notify bandwidth (max)(A) | 0 | 32767 | 100 |
| ft notify bandwidth (min)(A) | 0 | 32767 | 0 |
| hardware offload enabled (A) Applies to: SQL Server 2022 (16.x) and later versions. |
0 | 1 | 0 |
| hadoop connectivity (RP) Applies to: SQL Server 2016 (13.x) and later versions. |
0 | 7 | 0 |
| in-doubt xact resolution (A) | 0 | 2 | 0 |
| index create memory (A, SC) | 704 | 2147483647 | 0 |
| lightweight pooling (A, RR) | 0 | 1 | 0 |
| locks (A, RR, SC) | 5000 | 2147483647 | 0 |
| max degree of parallelism (A) | 0 | 32767 | 0 |
| max full-text crawl range (A) | 0 | 256 | 4 |
| max server memory (A, SC) | 16 | 2147483647 | 2147483647 |
| max text repl size | 0 | 2147483647 | 65536 |
| max worker threads (A) | 128 | 32767 1024 is the maximum recommended for 32-bit SQL Server, and 2048 for 64-bit SQL Server. Note: SQL Server 2014 (12.x) was the last version available on a 32-bit operating system. |
0 Zero auto-configures the number of max worker threads depending on the number of logical processors, using the formula (256 + (<logical processors> - 4) * 8) for 32-bit SQL Server and (512 + (<logical processors> - 4) * 8) for 64-bit SQL Server. Note: SQL Server 2014 (12.x) was the last version available on a 32-bit operating system. |
| media retention (A, RR) | 0 | 365 | 0 |
| min memory per query (A) | 512 | 2147483647 | 1024 |
| min server memory (A, SC) | 0 | 2147483647 | 0 |
| nested triggers | 0 | 1 | 1 |
| network packet size (A) | 512 | 32767 | 4096 |
| Ole Automation Procedures (A) | 0 | 1 | 0 |
| open objects (A, RR, obsolete) | 0 | 2147483647 | 0 |
| optimize for ad hoc workloads (A) | 0 | 1 | 0 |
| PH_timeout (A) | 1 | 3600 | 60 |
| polybase enabled (RR) Applies to: SQL Server 2019 (15.x) and later versions. |
0 | 1 | 0 |
| polybase network encryption | 0 | 1 | 1 |
| precompute rank (A) | 0 | 1 | 0 |
| priority boost (A, RR) | 0 | 1 | 0 |
| query governor cost limit (A) | 0 | 2147483647 | 0 |
| query wait (A) | -1 | 2147483647 | -1 |
| recovery interval (min) (A, SC) | 0 | 32767 | 0 |
| remote access (RR) | 0 | 1 | 1 |
| remote admin connections | 0 | 1 | 0 |
| remote data archive | 0 | 1 | 0 |
| remote login timeout | 0 | 2147483647 | 10 |
| remote proc trans | 0 | 1 | 0 |
| remote query timeout | 0 | 2147483647 | 600 |
| Replication XPs Option (A) | 0 | 1 | 0 |
| scan for startup procs (A, RR) | 0 | 1 | 0 |
| server trigger recursion | 0 | 1 | 1 |
| set working set size (A, RR, obsolete) | 0 | 1 | 0 |
| show advanced options | 0 | 1 | 0 |
| SMO and DMO XPs (A) | 0 | 1 | 1 |
| suppress recovery model errors (A) Applies to: Azure SQL Managed Instance. |
0 | 1 | 0 |
| tempdb metadata memory-optimized (A) Applies to: SQL Server 2019 (15.x) and later versions. |
0 | 1 | 0 |
| transform noise words (A) | 0 | 1 | 0 |
| two digit year cutoff (A) | 1753 | 9999 | 2049 |
| user connections (A, RR, SC) | 0 | 32767 | 0 |
| user options | 0 | 32767 | 0 |
| xp_cmdshell (A) | 0 | 1 | 0 |
See also
Feedback
Submit and view feedback for