SQL Server 2016 Server Configuration Options

To do a follow up regarding SQL Server 2016 (meaning CTP 3.2) to my previous post about “Configuration change might flush the SQL cache”;
http://www.sqlservice.se/configuration-change-might-flush-the-sql-cache since that one only covered the earlier versions.

There are six new Server Configuration Options added in SQL Server 2016:

* automatic soft-NUMA disabled

* external scripts enabled
Requires the feature ‘Advanced Analytics Extensions’ to be installed to enable the execution of R scripts.
https://msdn.microsoft.com/en-us/library/mt590884.aspx

* hadoop connectivity
PolyBase Configuration Options; https://msdn.microsoft.com/en-us/library/mt143174.aspx

* polybase mode

* polybase network encryption

* remote data archive
Configure the remote data archive Server Configuration Option; https://msdn.microsoft.com/en-us/library/mt143175.aspx

And there are three configuration options have been removed in SQL Server 2016 (I will certainly not miss them):
* SQL Mail XPs
* awe enabled
* Web Assistant Procedures

In SQL Server 2016 the whole procedure cache is cleared if one of the following server options is changed by the Reconfigure statement:
• access check cache bucket count
• access check cache quota
• affinity mask
• affinity I/O mask (Also requiring a restart)
• affinity64 I/O mask
• affinity64 mask (Also requiring a restart)
• automatic soft-NUMA disabled
• cost threshold for parallelism
• clr enabled
• cross db ownership chaining
• external scripts enabled
• filestream access level (Note that requiring a SQL restart for setting to off)
• index create memory (KB)
• hadoop connectivity
• locks (Also requiring a restart)
• max degree of parallelism
• max text repl size (B)
• max server memory (MB)
• max worker threads
• min memory per query (KB)
• min server memory (MB)
• polybase mode
• polybase network encryption
• query governor cost limit
• query wait (s)
• remote data archive
• remote query timeout (s)
• set working set size
• user connections
• user options

Note: There is a change in behavior because in SQL Server 2012 the procedure cache will not be cleared if the actual value doesn’t change but in SQL 2014 and also SQL 2016 it seems the procedure cache will be flushed even if the actual value doesn’t change.
So be aware when there is configuration scripts that needs to be run to setup a new application database because that even if the setting isn’t changed and will remain as the old value then it will anyway trigger a flushed cache in SQL 2014 and SQL 2016.

Tested environments:
SQL Server 2016 Community Technology Preview 3 (CTP 3.2) – 13.0.900.73 (X64) indicated by running a Profiler trace with the result from; SP:CacheRemove – ”Entire Procedure Cache Flushed”.

* MSDN-article: Server Configuration Options (SQL Server)
https://msdn.microsoft.com/en-us/library/ms189631.aspx