Tuesday, April 4, 2017

sql server configuration default values of 2008R2/2012/2014 versions



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