SQL SERVER 2019 – New Values in Sys.Configurations

Since the release of SQL Server 2019, I have written quite a few blogs about this release. In this blog, we would see the new options available in sys.configurations catalog view. If you are not familiar with this, then you need to correlate this with the values available in sp_configure.

Here is the way I figured out the new values. I have multiple SQL Server instances running on my lab server. I used SQLCMD mode in SSMS to get the output of sys.configurations from both SQL Servers in the same query windows.

:connect (local)\SQL2017
select count(*) from sys.configurations
GO
:connect (local)\SQL2019
select count(*) from sys.configurations

Output:

This means that there are six new options added in SQL Server 2019 from the previous version (SQL Server 2017) in sys.configurations. The names are as following:

Sys.Configurations

Here is the information for a few settings which I could find on Microsoft documentation.

• column encryption enclave type
• tempdb metadata memory-optimized
• polybase enabled

I didn’t find any information about ADR related setting (#3 and #4). I think they are for the Accelerated Database Recovery feature but how to use them is unknown to me as of now. I will share it via the blog if I learn more about it.

Here is the blog post series earlier I wrote about how we can get read of the parameter sniffing as well as improve the performance of SQL Server when it is struggling with the incorrect cache plan stored.

• SQL SERVER – Parameter Sniffing Simplest Example
  In this blog post, we discuss what actually is parameter sniffing and how we can overcome it with the help of recompiling the stored procedure.
• SQL SERVER – Parameter Sniffing and Local Variable in SP
  It is easy to overcome the parameter sniffing for the stored procedure by declaring the local variable inside the stored procedure.
• SQL SERVER – Parameter Sniffing and OPTIMIZE FOR UNKNOWN
  You can take advantage of the new query hint of Optimize For Unknown to simulate the local variable in the stored procedure. A very underutilized technique indeed.
• SQL SERVER – DATABASE SCOPED CONFIGURATION – PARAMETER SNIFFING
  This new database level enhancement was introduced recently which can help you overcome any issue with the parameter sniffing.
• SQL SERVER – Parameter Sniffing and OPTION (RECOMPILE)
  The oldest and most traditional technique to not cache the query plans and compile your stored procedure or queries every single time to get optimal performance.
• Performance and Recompiling Query – Summary
  This post summarizes the entire series of parameter sniffing, performance and recompiling query.

Reference: Pinal Dave (https://blog.sqlauthority.com)