Changes to SQL Server configuration options

Continuing on from the previous post on sys.databases, DBAs may/may not have noticed the adds/removes/tweaks occurring behind the scenes as each version changes.
A nice one of note is the allowing the backup checksums by default from SQL Server 2014.
Below is a breakdown of sys.configurations changes since SQL Server 2005.
SQL Server 2008

Setting Change
Web Assistant Procedures Removed
access check cache bucket count Added
access check cache quota Added
affinity64 mask Added
affinity64 I/O mask Added
backup compression default Added
EKM provider enabled Added
filestream access level Added
optimize for ad hoc workloads Added
max text repl size (B) Maximum value changed from 0 to -1
min server memory (MB) Default value changed from 8 to 16

SQL Server 2008 R2

Setting Change
max degree of parallelism Maximum value changed from 64 to 1024

SQL Server 2012

Setting Change
awe enabled Removed
SQL Mail XPs Removed
contained database authentication Added
EKM provider enabled Is dynamic changed from 0 to 1
max degree of parallelism Maximum value changed from 1024 to 32767
max server memory (MB) Minimum value changed from 16 to 128
max worker threads Is dynamic changed from 0 to 1
max worker threads Maximum value changed from 32767 to 65535
remote login timeout (s) Default value changed from 20 to 10

SQL Server 2014

Setting Change
backup checksum default Added

SQL Server 2016

Setting Change
allow polybase export Added
automatic soft-NUMA disabled Added
external scripts enabled Added
hadoop connectivity Added
polybase network encryption Added
remote data archive Added


SQL Server vNext (CTP 1.3)
No changes at this time.

Leave a Reply