Below are the SQL Server Default configuration values for 2008R2/2012/2014
SQL 2008R2
|
SQL 2012
|
SQL2014
|
||||
Configuration Option
|
Minimum Value
|
Maximum Value
|
Minimum Value
|
Maximum Value
|
Minimum Value
|
Maximum Value
|
access check cache bucket
count
|
0
|
65536
|
0
|
65536
|
0
|
65536
|
access check cache quota
|
0
|
2147483647
|
0
|
2147483647
|
0
|
2147483647
|
Ad Hoc Distributed
Queries
|
0
|
1
|
0
|
1
|
0
|
1
|
affinity I/O mask
|
-2147483648
|
2147483647
|
-2147483648
|
2147483647
|
-2147483648
|
2147483647
|
affinity mask
|
-2147483648
|
2147483647
|
-2147483648
|
2147483647
|
-2147483648
|
2147483647
|
affinity64 I/O mask
|
-2147483648
|
2147483647
|
-2147483648
|
2147483647
|
-2147483648
|
2147483647
|
affinity64 mask
|
-2147483648
|
2147483647
|
-2147483648
|
2147483647
|
-2147483648
|
2147483647
|
Agent XPs
|
0
|
1
|
0
|
1
|
0
|
1
|
allow updates
|
0
|
1
|
0
|
1
|
0
|
1
|
awe enabled
|
0
|
1
|
||||
backup checksum default
|
0
|
1
|
||||
backup compression
default
|
0
|
1
|
0
|
1
|
0
|
1
|
blocked process threshold
(s)
|
0
|
86400
|
0
|
86400
|
0
|
86400
|
c2 audit mode
|
0
|
1
|
0
|
1
|
0
|
1
|
clr enabled
|
0
|
1
|
0
|
1
|
0
|
1
|
common criteria
compliance enabled
|
0
|
1
|
0
|
1
|
0
|
1
|
contained database
authentication
|
0
|
1
|
0
|
1
|
||
cost threshold for
parallelism
|
0
|
32767
|
0
|
32767
|
0
|
32767
|
cross db ownership
chaining
|
0
|
1
|
0
|
1
|
0
|
1
|
cursor threshold
|
-1
|
2147483647
|
-1
|
2147483647
|
-1
|
2147483647
|
Database Mail XPs
|
0
|
1
|
0
|
1
|
0
|
1
|
default full-text
language
|
0
|
2147483647
|
0
|
2147483647
|
0
|
2147483647
|
default language
|
0
|
9999
|
0
|
9999
|
0
|
9999
|
default trace enabled
|
0
|
1
|
0
|
1
|
0
|
1
|
disallow results from
triggers
|
0
|
1
|
0
|
1
|
0
|
1
|
EKM provider enabled
|
0
|
1
|
0
|
1
|
0
|
1
|
filestream access level
|
0
|
2
|
0
|
2
|
0
|
2
|
fill factor (%)
|
0
|
100
|
0
|
100
|
0
|
100
|
ft crawl bandwidth (max)
|
0
|
32767
|
0
|
32767
|
0
|
32767
|
ft crawl bandwidth (min)
|
0
|
32767
|
0
|
32767
|
0
|
32767
|
ft notify bandwidth (max)
|
0
|
32767
|
0
|
32767
|
0
|
32767
|
ft notify bandwidth (min)
|
0
|
32767
|
0
|
32767
|
0
|
32767
|
index create memory (KB)
|
704
|
2147483647
|
704
|
2147483647
|
704
|
2147483647
|
in-doubt xact resolution
|
0
|
2
|
0
|
2
|
0
|
2
|
lightweight pooling
|
0
|
1
|
0
|
1
|
0
|
1
|
locks
|
5000
|
2147483647
|
5000
|
2147483647
|
5000
|
2147483647
|
max degree of parallelism
|
0
|
1024
|
0
|
32767
|
0
|
32767
|
max full-text crawl range
|
0
|
256
|
0
|
256
|
0
|
256
|
max server memory (MB)
|
16
|
2147483647
|
128
|
2147483647
|
128
|
2147483647
|
max text repl size (B)
|
-1
|
2147483647
|
-1
|
2147483647
|
-1
|
2147483647
|
max worker threads
|
128
|
32767
|
128
|
65535
|
128
|
65535
|
media retention
|
0
|
365
|
0
|
365
|
0
|
365
|
min memory per query (KB)
|
512
|
2147483647
|
512
|
2147483647
|
512
|
2147483647
|
min server memory (MB)
|
0
|
2147483647
|
0
|
2147483647
|
0
|
2147483647
|
nested triggers
|
0
|
1
|
0
|
1
|
0
|
1
|
network packet size (B)
|
512
|
32767
|
512
|
32767
|
512
|
32767
|
Ole Automation Procedures
|
0
|
1
|
0
|
1
|
0
|
1
|
open objects
|
0
|
2147483647
|
0
|
2147483647
|
0
|
2147483647
|
optimize for ad hoc
workloads
|
0
|
1
|
0
|
1
|
0
|
1
|
PH timeout (s)
|
1
|
3600
|
1
|
3600
|
1
|
3600
|
precompute rank
|
0
|
1
|
0
|
1
|
0
|
1
|
priority boost
|
0
|
1
|
0
|
1
|
0
|
1
|
query governor cost limit
|
0
|
2147483647
|
0
|
2147483647
|
0
|
2147483647
|
query wait (s)
|
-1
|
2147483647
|
-1
|
2147483647
|
-1
|
2147483647
|
recovery interval (min)
|
0
|
32767
|
0
|
32767
|
0
|
32767
|
remote access
|
0
|
1
|
0
|
1
|
0
|
1
|
remote admin connections
|
0
|
1
|
0
|
1
|
0
|
1
|
remote login timeout (s)
|
0
|
2147483647
|
0
|
2147483647
|
0
|
2147483647
|
remote proc trans
|
0
|
1
|
0
|
1
|
0
|
1
|
remote query timeout (s)
|
0
|
2147483647
|
0
|
2147483647
|
0
|
2147483647
|
Replication XPs
|
0
|
1
|
0
|
1
|
0
|
1
|
scan for startup procs
|
0
|
1
|
0
|
1
|
0
|
1
|
server trigger recursion
|
0
|
1
|
0
|
1
|
0
|
1
|
set working set size
|
0
|
1
|
0
|
1
|
0
|
1
|
show advanced options
|
0
|
1
|
0
|
1
|
0
|
1
|
SMO and DMO XPs
|
0
|
1
|
0
|
1
|
0
|
1
|
SQL Mail XPs
|
0
|
1
|
||||
transform noise words
|
0
|
1
|
0
|
1
|
0
|
1
|
two digit year cutoff
|
1753
|
9999
|
1753
|
9999
|
1753
|
9999
|
user connections
|
0
|
32767
|
0
|
32767
|
0
|
32767
|
user options
|
0
|
32767
|
0
|
32767
|
0
|
32767
|
xp_cmdshell
|
0
|
1
|
0
|
1
|
0
|
1
|
