SQL SERVER – Database Scoped Configurations

What I enjoy the most while working on difficult and different challenges every single day with Comprehensive Database Performance Health Check. Recently I was asked a very interesting question by one of my clients on the topic of Database Scoped Configurations. Let us learn about that in today’s blog post.

Database Scoped Configurations

SQL Server supports database level configurations that affect the behavior of the application code at the database level. Earlier we used to have many different configurations that were supported at only the instance level but not at the database level. However, As of SQL Server 2016, there are many settings now available at the primary database level and if you are using AlwaysOn in that case, also on the secondary server.

Here is the query which you can run and see what are the different version of Database Scoped Configuration available on different versions of SQL Server.

SELECT *
FROM sys.database_scoped_configurations

SQL Server 2016

There were only four scoped configurations were available in SQL Server 2016. It was possible to modify these values via database properties in SSMS.

SQL Server 2017

In SQL Server 2017 there was one more addition to the scoped configuration list and the total available options where 5.

SQL Server 2019 – Database Scoped Configurations

In SQL Server 2019 the number of the available configuration went all the way up to total 23.

Besides the four initially launched scoped configurations in 2016, there is no GUI for any of the newly launched options. You can only change them via the T-SQL script as demonstrated in the following script.

USE [AdventureWorks2017]
GO
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;
GO

If you are using the latest version of SQL Server, I would like to know what are the different scoped configurations you configure for your database and why. I find it is extremely convenient to set this value at the database level instead of the Server level.

Question for you!

If you carefully noticed the image of SQL server 2017 and 2019, you will see that there is no value for the configuration_id = 5. So now the question is, why the #5 is skipped and what was an actual plan for this value which we never learned anywhere? 

If you know the answer, please leave a comment. You can follow me on twitter for the latest updates.

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