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).
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 might 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 invalidates plans in the plan cache, causing new plans to be compiled. For more information, see DBCC FREEPROCCACHE.
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 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
0
displays as2147483647
in thevalue_in_use
column.min server memory (MB) - The default configured value of
0
might display as8
on 32-bit systems, or16
on 64-bit systems, in thevalue_in_use
column. In some cases, if thevalue_in_use
shows as0
, the truevalue_in_use
is8
(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 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 SQL Server to apply the configuration change, run the RECONFIGURE
command to ensure all configuration changes will take effect when SQL Server next restarts.
Note
SQL Server 2014 (12.x) was the last version available on a 32-bit operating system.
Configuration options
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
to1
.RR = Options requiring a restart of the Database Engine.
RP = Options that require a restart of the PolyBase Engine.
SC = Self-configuring options.
Note
SQL Server 2014 (12.x) was the last version available on a 32-bit operating system.
Configuration option | Possible values | SQL Server | Azure SQL Managed Instance |
---|---|---|---|
access check cache bucket count (A) | Minimum: 0 Maximum: 16384 Default: 0 |
Yes | Yes |
access check cache quota (A) | Minimum: 0 Maximum: 2147483647 Default: 0 |
Yes | Yes |
Ad Hoc Distributed Queries (A) | Minimum: 0 Maximum: 1 Default: 0 |
Yes | Yes |
ADR cleaner retry timeout (min) (A) | Minimum: 0 Maximum: 32767 Default: 120 |
SQL Server 2019 (15.x) and later versions | Yes |
ADR Cleaner Thread Count (A) | Minimum: 1 Maximum: 32767 Default: 1 |
SQL Server 2019 (15.x) and later versions | Yes |
ADR Preallocation Factor (A) | Minimum: 0 Maximum: 32767 Default: 4 |
SQL Server 2019 (15.x) and later versions | Yes |
affinity I/O mask (A, RR) | Minimum: -2147483648 Maximum: 2147483647 Default: 0 |
Yes (64-bit only) | No |
affinity mask (A) | Minimum: -2147483648 Maximum: 2147483647 Default: 0 |
Yes (64-bit only) | Yes |
affinity64 I/O mask (A, RR) | Minimum: -2147483648 Maximum: 2147483647 Default: 0 |
Yes (64-bit only) | Yes |
affinity64 mask (A) | Minimum: -2147483648 Maximum: 2147483647 Default: 0 |
Yes (64-bit only) | No |
Agent XPs (A) 1 | Minimum: 0 Maximum: 1 Default: 0 |
Yes | No |
allow polybase export | Minimum: 0 Maximum: 1 Default: 0 |
SQL Server 2016 (13.x) and later versions | No |
allow updates Warning: Obsolete. Don't use. Causes an error during reconfigure. |
Minimum: 0 Maximum: 1 Default: 0 |
Yes | No |
automatic soft-NUMA disabled (A, RR) | Minimum: 0 Maximum: 1 Default: 0 |
Yes | Yes |
backup checksum default | Minimum: 0 Maximum: 1 Default: 0 |
Yes | Yes |
backup compression algorithm | Minimum: 0 Maximum: 1 Default: 0 |
SQL Server 2022 (16.x) and later versions | Yes |
backup compression default | Minimum: 0 Maximum: 1 (prior to SQL Server 2022 (16.x)), or 2 (SQL Server 2022 (16.x) and later versions)Default: 0 |
Yes | Yes |
blocked process threshold (s) (A) | Minimum: 5 Maximum: 86400 Default: 0 |
Yes | Yes |
c2 audit mode (A, RR) | Minimum: 0 Maximum: 1 Default: 0 |
Yes | No |
clr enabled | Minimum: 0 Maximum: 1 Default: 0 |
Yes | Yes |
clr strict security (A) | Minimum: 0 Maximum: 1 Default: 0 |
SQL Server 2017 (14.x) and later versions | Yes |
column encryption enclave type (RR) | Minimum: 0 Maximum: 2 Default: 0 |
Yes | No |
common criteria compliance enabled (A, RR) | Minimum: 0 Maximum: 1 Default: 0 |
Yes | No |
contained database authentication | Minimum: 0 Maximum: 1 Default: 0 |
Yes | Yes |
cost threshold for parallelism (A) | Minimum: 0 Maximum: 32767 Default: 5 |
Yes | Yes |
cross db ownership chaining | Minimum: 0 Maximum: 1 Default: 0 |
Yes | Yes |
cursor threshold (A) | Minimum: -1 Maximum: 2147483647 Default: -1 |
Yes | Yes |
Data processed daily limit in TB | Minimum: 0 Maximum: 2147483647 Default: 2147483647 |
Yes | Yes |
Data processed monthly limit in TB | Minimum: 0 Maximum: 2147483647 Default: 2147483647 |
Yes | Yes |
Data processed weekly limit in TB | Minimum: 0 Maximum: 2147483647 Default: 2147483647 |
Yes | Yes |
Database Mail XPs (A) | Minimum: 0 Maximum: 1 Default: 0 |
Yes | Yes |
default full-text language (A) | Minimum: 0 Maximum: 2147483647 Default: 1033 |
Yes | Yes |
default language | Minimum: 0 Maximum: 9999 Default: 0 |
Yes | Yes |
default trace enabled (A) | Minimum: 0 Maximum: 1 Default: 1 |
Yes | Yes |
disallow results from triggers (A) | Minimum: 0 Maximum: 1 Default: 0 |
Yes | Yes |
EKM provider enabled (A) | Minimum: 0 Maximum: 1 Default: 0 |
Yes | Yes |
external scripts enabled (SC) | Minimum: 0 Maximum: 1 Default: 0 |
SQL Server 2016 (13.x) and later versions | Yes |
filestream access level | Minimum: 0 Maximum: 2 Default: 0 |
Yes | No |
fill factor (%) (A, RR) | Minimum: 0 Maximum: 100 Default: 0 |
Yes | No |
ft crawl bandwidth (max) (A) | Minimum: 0 Maximum: 32767 Default: 100 |
Yes | Yes |
ft crawl bandwidth (min) (A) | Minimum: 0 Maximum: 32767 Default: 0 |
Yes | Yes |
ft notify bandwidth (max) (A) | Minimum: 0 Maximum: 32767 Default: 100 |
Yes | Yes |
ft notify bandwidth (min) (A) | Minimum: 0 Maximum: 32767 Default: 0 |
Yes | Yes |
hadoop connectivity (RP) | Minimum: 0 Maximum: 7 Default: 0 |
SQL Server 2016 (13.x) and later versions | Yes |
hardware offload config (A, RR) | Minimum: 0 Maximum: 255 Default: 0 |
SQL Server 2022 (16.x) and later versions | Yes |
hardware offload enabled (A, RR) | Minimum: 0 Maximum: 1 Default: 0 |
SQL Server 2022 (16.x) and later versions | Yes |
hardware offload mode (A, RR) | Minimum: 0 Maximum: 255 Default: 0 |
SQL Server 2022 (16.x) and later versions | Yes |
in-doubt xact resolution (A) | Minimum: 0 Maximum: 2 Default: 0 |
Yes | Yes |
index create memory (KB) (A, SC) | Minimum: 704 Maximum: 2147483647 Default: 0 |
Yes | Yes |
lightweight pooling (A, RR) | Minimum: 0 Maximum: 1 Default: 0 |
Yes | No |
locks (A, RR, SC) | Minimum: 5000 Maximum: 2147483647 Default: 0 |
Yes | No |
max degree of parallelism (A) | Minimum: 0 Maximum: 32767 Default: 0 |
Yes | No |
max full-text crawl range (A) | Minimum: 0 Maximum: 256 Default: 4 |
Yes | Yes |
max RPC request params (KB) (A) | Minimum: 0 Maximum: 2147483647 Default: 0 |
SQL Server 2019 (15.x) CU 26 and later versions, and SQL Server 2022 (16.x) CU 13 and later versions | No |
max server memory (MB) (A, SC) | Minimum: 16 Maximum: 2147483647 Default: 2147483647 |
Yes | Yes |
max text repl size (B) | Minimum: 0 Maximum: 2147483647 Default: 65536 |
Yes | Yes |
max worker threads (A) 2 | Minimum: 128 Maximum: 32767 Default: 0 2048 is the recommended maximum for 64-bit SQL Server (1024 for 32-bit) |
Yes | Yes |
media retention (A) | Minimum: 0 Maximum: 365 Default: 0 |
Yes | No |
min memory per query (KB) (A) | Minimum: 512 Maximum: 2147483647 Default: 1024 |
Yes | No |
min server memory (MB) (A, SC) | Minimum: 0 Maximum: 2147483647 Default: 0 |
Yes | No |
nested triggers | Minimum: 0 Maximum: 1 Default: 1 |
Yes | Yes |
network packet size (B) (A) | Minimum: 512 Maximum: 32767 Default: 4096 |
Yes | Yes |
Ole Automation Procedures (A) | Minimum: 0 Maximum: 1 Default: 0 |
Yes | Yes |
open objects (A, RR) Warning: Obsolete. Don't use. |
Minimum: 0 Maximum: 2147483647 Default: 0 |
Yes | No |
optimize for ad hoc workloads (A) | Minimum: 0 Maximum: 1 Default: 0 |
Yes | Yes |
PH timeout (A) | Minimum: 1 Maximum: 3600 Default: 60 |
Yes | Yes |
polybase enabled | Minimum: 0 Maximum: 1 Default: 0 |
SQL Server 2019 (15.x) and later versions | No |
polybase network encryption | Minimum: 0 Maximum: 1 Default: 1 |
Yes | Yes |
precompute rank (A) | Minimum: 0 Maximum: 1 Default: 0 |
Yes | Yes |
priority boost (A, RR) | Minimum: 0 Maximum: 1 Default: 0 |
Yes | No |
query governor cost limit (A) | Minimum: 0 Maximum: 2147483647 Default: 0 |
Yes | Yes |
query wait (s) (A) | Minimum: -1 Maximum: 2147483647 Default: -1 |
Yes | Yes |
recovery interval (min) (A, SC) | Minimum: 0 Maximum: 32767 Default: 0 |
Yes | Yes |
remote access (RR) | Minimum: 0 Maximum: 1 Default: 1 |
Yes | No |
remote admin connections | Minimum: 0 Maximum: 1 Default: 0 |
Yes | Yes |
remote data archive | Minimum: 0 Maximum: 1 Default: 0 |
Yes | No |
remote login timeout (s) | Minimum: 0 Maximum: 2147483647 Default: 10 |
Yes | Yes |
remote proc trans | Minimum: 0 Maximum: 1 Default: 0 |
Yes | Yes |
remote query timeout (s) | Minimum: 0 Maximum: 2147483647 Default: 600 |
Yes | Yes |
Replication XPs (A) | Minimum: 0 Maximum: 1 Default: 0 |
Yes | Yes |
scan for startup procs (A, RR) | Minimum: 0 Maximum: 1 Default: 0 |
Yes | No |
server trigger recursion | Minimum: 0 Maximum: 1 Default: 1 |
Yes | Yes |
set working set size (A, RR) Warning: Obsolete. Don't use. |
Minimum: 0 Maximum: 1 Default: 0 |
Yes | No |
show advanced options | Minimum: 0 Maximum: 1 Default: 0 |
Yes | Yes |
SMO and DMO XPs (A) | Minimum: 0 Maximum: 1 Default: 1 |
Yes | Yes |
suppress recovery model errors (A) | Minimum: 0 Maximum: 1 Default: 0 |
No | Yes |
tempdb metadata memory-optimized (A, RR) | Minimum: 0 Maximum: 1 Default: 0 |
SQL Server 2019 (15.x) and later versions | No |
transform noise words (A) | Minimum: 0 Maximum: 1 Default: 0 |
Yes | Yes |
two digit year cutoff (A) | Minimum: 1753 Maximum: 9999 Default: 2049 |
Yes | Yes |
user connections (A, RR, SC) | Minimum: 0 Maximum: 32767 Default: 0 |
Yes | No |
user options | Minimum: 0 Maximum: 32767 Default: 0 |
Yes | Yes |
version high part of SQL Server (A) | Minimum: -2147483648 Maximum: 2147483647 Default: 0 |
Yes | Yes |
version low part of SQL Server (A) | Minimum: -2147483648 Maximum: 2147483647 Default: 0 |
Yes | Yes |
xp_cmdshell (A) | Minimum: 0 Maximum: 1 Default: 0 |
Yes | Yes |
1 Changes to 1
when SQL Server Agent is started. Default value is 0
if SQL Server Agent is set to automatic start during Setup.
2 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.