| title | Server Configuration Options (SQL Server) | Microsoft Docs | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ms.custom | |||||||||||||||||
| ms.date | 04/13/2017 | ||||||||||||||||
| ms.prod | sql | ||||||||||||||||
| ms.prod_service | high-availability | ||||||||||||||||
| ms.reviewer | |||||||||||||||||
| ms.technology | configuration | ||||||||||||||||
| ms.topic | conceptual | ||||||||||||||||
| keywords |
|
||||||||||||||||
| helpviewer_keywords |
|
||||||||||||||||
| ms.assetid | 9f38eba6-39b1-4f1d-ba24-ee4f7e2bc969 | ||||||||||||||||
| author | MikeRayMSFT | ||||||||||||||||
| ms.author | mikeray | ||||||||||||||||
| manager | craigg |
[!INCLUDEappliesto-ss-xxxx-xxxx-xxx-md]
You can manage and optimize [!INCLUDEssNoVersion] resources through configuration options by using [!INCLUDEssManStudioFull] or the sp_configure system stored procedure. The most commonly used server configuration options are available through [!INCLUDEssManStudioFull]; 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 [!INCLUDEssNoVersion] technician.
Configuration options take effect either:
-
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).
-or-
-
After performing the above actions and restarting the instance of [!INCLUDEssNoVersion].
Options that require [!INCLUDEssNoVersion] to restart 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 configuration options value column, but not in the value_in_use column. After restarting the server, the new value appears in the value_in_use column.
Self-configuring options are those that [!INCLUDEssNoVersion] adjusts according to the needs of the system. In most cases, this eliminates the need for setting the values manually. Examples include the min server memory and max server memory options and the user connections option.
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 [!INCLUDEssNoVersion] professional, and which require setting show advanced options to 1.
-
RR = Options requiring a restart of the [!INCLUDEssDE].
-
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 affinity I/O mask (A, RR) -2147483648 2147483647 0 affinity64 I/O mask (A, only available on 64-bit version of [!INCLUDEssNoVersion]) -2147483648 2147483647 0 affinity mask (A) -2147483648 2147483647 0 affinity64 mask (A, RR), only available on 64-bit version of [!INCLUDEssNoVersion] -2147483648 2147483647 0 Agent XPs (A) 0 1 0
(Changes to 1 when [!INCLUDEssNoVersion] Agent is started. Default value is 0 if [!INCLUDEssNoVersion] Agent is set to automatic start during Setup.)allow updates (Obsolete. Do not 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 0 blocked process threshold (A) 0 86400 0 c2 audit mode (A, RR) 0 1 0 clr enabled 0 1 0 clr strict security (A)
Applies to: [!INCLUDEssNoVersion] ([!INCLUDEsssqlv14-md] through [!INCLUDEssCurrent]).0 1 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 (RR)
Applies to: [!INCLUDEssNoVersion] ([!INCLUDEssSQL15] through [!INCLUDEssCurrent]).0 1 0 filestream_access_level 0 2 0 fill factor (A, RR) 0 100 0 ft crawl bandwidth (max), see ft crawl bandwidth(A) 0 32767 100 ft crawl bandwidth (min), see ft crawl bandwidth(A) 0 32767 0 ft notify bandwidth (max), see ft notify bandwidth(A) 0 32767 100 ft notify bandwidth (min), see ft notify bandwidth(A) 0 32767 0 index create memory (A, SC) 704 2147483647 0 in-doubt xact resolution (A) 0 2 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 [!INCLUDEssNoVersion], and 2048 for 64-bit [!INCLUDEssNoVersion]. Note: [!INCLUDEssSQL14] was the last version available on 32-bit operating system.0
Zero auto-configures the number of max worker threads depending on the number of processors, using the formula (256 + (<processors> -4) * 8) for 32-bit [!INCLUDEssNoVersion] and (512 + (<processors> - 4) * 8) for 64-bit [!INCLUDEssNoVersion]. Note: [!INCLUDEssSQL14] was the last version available on 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 Hadoop and Azure blob storage (RP)
Applies to: [!INCLUDEssNoVersion] ([!INCLUDEssSQL15] through [!INCLUDEssCurrent]).0 7 0 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 (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 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
sp_configure (Transact-SQL)
RECONFIGURE (Transact-SQL)
DBCC FREEPROCCACHE (Transact-SQL)