SQL Server 2019: What’s new in sp_configure and sys.configurations options?

SQL Server 2019 added new options in sp_configure and sys.configurations.
First, how can we find the difference between these SQL Server Versions.
This simple query will give us the number of options and the SQL Server Version:

select count(*),@@version FROM sys.configurations

In SQL server 2016, we have 74 parameters for the instance configuration:

In SQL server 2017, we have 77 parameters for the instance configuration:

In SQL server 2019, we have 84 parameters for the instance configuration:

In SQL Server 2019 we have 7 more parameters than in SQL Server 2017.

In detail, we see that one parameter has been removed and 8 added:

• • The parameter removed is with the ID 1577 named “common criteria compliance enabled”. More detail here
  • The 8 new options are:
    • ID 1588 named “column encryption enclave type”
    • ID 1589 named “tempdb metadata memory-optimized”
    • ID 1591 named “ADR cleaner retry timeout (min) ”
    • ID 1592 named “ADR Preallocation Factor”
    • ID 1593 named “version high part of SQL Server”
    • ID 1594 named “version low part of SQL Server”
    • ID 16398 named “allow filesystem enumeration”
    • ID 16399 named “polybase enabled”

After we identify the new parameters, we will go a step forward with the configuration with this query:

select * FROM sys.configurations where configuration_id in (1588,1589,1591,1592,1593,1594,16398,16399)

We can see that only one value has 1 and not 0 by default, the parameter “allow filesystem enumeration”.
The 2 others interesting columns is “is_dynamic” and “is_advanced”:

• When “is_dynamic” is set to 1, the parameter need a RECONFIGURE to be activate.
• When “is_advanced” is set to1, the parameter is in the advanced configuration.

I will not explain or test the new parameter in this article.
It’s just to give you a view of the new SQL Server 2019 Instance configuration options.