Επεξεργασία

Κοινή χρήση μέσω

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 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 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 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.

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: 1		 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.

Related content