What is the Priority of Database Scoped Configurations? – Interview Question of the Week #254

Question: What is the Priority of Database Scoped Configurations over Global Configuration at Server Level?

Answer: This is a follow-up question of what I have earlier discussed in the previous blog post SQL SERVER – Database Scoped Configurations. In the earlier blog post, I explained how now we can configure SQL Server at the database level, instead of changing the values at the server level which changes the values for every single database on the instance.

Right following the blog post I received a note from my regular client who often avails my services for Comprehensive Database Performance Health Check for their server. The question is as follows:

What happens if someone changes the settings at the database level as well as the server level, which settings your queries will follow?

Let us see the answer to this question in one statement here: Database Scoped Configuration takes priority over Global Configuration.

Now let us prove that with a demonstration.

First, change the server level configuration for the Max Degree of Parallelism to run the query at the MAXDOP 2.

EXEC sys.sp_configure N'max degree of parallelism', N'2'
GO

Next, change the database level configuration for the Max Degree of Parallelism to run the query at the MAXDOP 4.

USE AdventureWorks2017
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4;
GO

Now run a query that uses the parallel plan by enabling the execution plan and check its MAXDOP.

USE AdventureWorks2017
GO
SELECT *
FROM Sales.SalesOrderDetail
ORDER BY UnitPrice DESC
GO

Now let us carefully observe the execution plan. It is very clear from the execution plan that SQL Server overrides the server level global configuration with the database level local configuration.

Well, that’s it. I think it is indeed blessings for the SQL Server Performance Tuning Experts who can make all the necessary changes into a single database and make them run at amazing speed. I have started to use various Database Scoped Configurations for many of my clients. Have you experimented with this feature of SQL Server? Here is another good article on this topic from Microsoft.

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