Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Practice Test
Tip of the Day : Example Uses of the CHOOSE Logical Function
SQL Server 2012
Home > SQL Server 2012 > SQL Server 2012 Server Configuration Options
SQL Server 2012 Server Configuration Options

SQL Server 2012 Server Configuration Options

The following table compares all available configuraion options for both SQL Server 2012 and SQL Server 2008 R2, showing 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 technician, and which require setting show advanced options to 1.
  • RR = Options requiring a restart of the Database Engine.
  • SC = Self-configuring options.
  SQL Server 2012 SQL Server 2008 R2
Configuration Option Minimum Value Maximum Value Default Minimum Value Maximum Value Default
access check cache bucket count (A) 0 16384 0 0 16384 0
access check cache quota (A) 0 2147483647 0 0 2147483647 0
ad hoc distributed queries (A) 0 1 0 0 1 0
affinity I/O mask (A, RR) -2147483648 2147483647 0 -2147483648 2147483647 0
affinity64 I/O mask (A, only available on 64-bit version of SQL Server) -2147483648 2147483647 0 -2147483648 2147483647 0
affinity mask (A) -2147483648 2147483647 0 -2147483648 2147483647 0
affinity64 mask (A, RR), only available on 64-bit version of SQL Server -2147483648 2147483647 0 -2147483648 2147483647 0
Agent XPs (A) 0 1 0 0 1 0
allow updates (Obsolete. Do not use. Will cause an error during reconfigure.) 0 1 0 0 1 0
awe enabled (A, RR) 0 1 0 0 1 0
backup compression default 0 1 0 0 1 0
blocked process threshold (A) 0 86400 0 0 86400 0
c2 audit mode (A, RR) 0 1 0 0 1 0
clr enabled 0 1 0 0 1 0
common criteria compliance enabled (A, RR) 0 1 0 0 1 0
contained database authentication 0 0 Not Available
cost threshold for parallelism (A) 0 32767 5 0 32767 5
cross db ownership chaining 0 1 0 0 1 0
cursor threshold (A) -1 2147483647 -1 -1 2147483647 -1
Database Mail XPs (A) 0 1 0 0 1 0
default full-text language (A) 0 2147483647 1033 0 2147483647 1033
default language 0 9999 0 0 9999 0
default trace enabled (A) 0 1 1 0 1 1
disallow results from triggers (A) 0 1 0 0 1 0
EKM provider enabled 0 1 0 0 1 0
filestream_access_level 0 2 0 0 2 0
fill factor (A, RR) 0 100 0 0 100 0
ft crawl bandwidth (max), see ft crawl bandwidth (A) 0 32767 100 0 32767 100
ft crawl bandwidth (min), see ft crawl bandwidth (A) 0 32767 0 0 32767 0
ft notify bandwidth (max), see ft notify bandwidth (A) 0 32767 100 0 32767 100
ft notify bandwidth (min), see ft notify bandwidth (A) 0 32767 0 0 32767 0
index create memory (A, SC) 704 2147483647 0 704 2147483647 0
  SQL Server 2012 SQL Server 2008 R2
Configuration Option Minimum Value Maximum Value Default Minimum Value Maximum Value Default
in-doubt xact resolution (A) 0 2 0 0 2 0
lightweight pooling (A, RR) 0 1 0 0 1 0
locks (A, RR, SC) 5000 2147483647 0 5000 2147483647 0
max degree of parallelism (A) 0 64 0 0 64 0
max full-text crawl range (A) 0 64 0 0 64 0
max server memory (A, SC) 16 2147483647 2147483647 16 2147483647 2147483647
max text repl size 0 2147483647 65536 0 2147483647 65536
max worker threads (A) 128 32767 0 128 32767 0
media retention (A, RR) 0 365 0 0 365 0
min memory per query (A) 512 2147483647 1024 512 2147483647 1024
min server memory (A, SC) 0 2147483647 0 0 2147483647 0
nested triggers 0 1 1 0 1 1
network packet size (A) 512 32767 4096 512 32767 4096
Ole Automation Procedures (A) 0 1 0 0 1 0
open objects (A, RR, obsolete) 0 2147483647 0 0 2147483647 0
optimize for ad hoc workloads (A) 0 1 0 0 1 0
PH_timeout (A) 1 3600 60 1 3600 60
precompute rank (A) 0 1 0 0 1 0
priority boost (A, RR) 0 1 0 0 1 0
query governor cost limit (A) 0 2147483647 0 0 2147483647 0
query wait (A) -1 2147483647 -1 -1 2147483647 -1
recovery internal (A, SC) 0 32767 0 0 32767 0
remote access (RR) 0 1 1 0 1 1
remote admin connections 0 1 0 0 1 0
remote login timeout 0 2147483647 10 0 2147483647 10
remote proc trans 0 1 0 0 1 0
remote query timeout 0 2147483647 600 0 2147483647 600
Replication XPs Option (A) 0 1 0 0 1 0
scan for startup procs (A, RR) 0 1 0 0 1 0
server trigger recursion 0 1 1 0 1 1
set working set size (A, RR, obsolete) 0 1 0 0 1 0
show advanced options 0 1 0 0 1 0
SMO and DMO XPs (A) 0 1 1 0 1 1
SQL Mail XPs (A) Not Available 0 1 0
transform noise words (A) 0 1 0 0 1 0
two digit year cutoff (A) 1753 9999 2049 1753 9999 2049
user connections (A, RR, SC) 0 32767 0 0 32767 0
User Instance Timeout (A) Not Available 5 65535 60
user instances enabled (A) Not Available 0 1 0
user options 0 32767 0 0 32767 0
xp_cmdshell (A) 0 1 0 0 1 0
Related Articles :