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.

What is the Priority of Database Scoped Configurations? - Interview Question of the Week #254 Scoped-Configurations-800x369

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:

Solarwinds

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.

What is the Priority of Database Scoped Configurations? - Interview Question of the Week #254 Global 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)

Solarwinds
, , , ,
Previous Post
MemoryGrantInfo – What are Different Status of IsMemoryGrantFeedbackAdjusted? – Interview Question of the Week #253
Next Post
Why Execution Plan Operator Read More Rows Than Available? – Interview Question of the Week #255

Related Posts

2 Comments. Leave new

  • Do you know if this is the case for all database scoped settings? I haven’t looked at all especially 2019 settings so I am just wondering and probably expecting it is true

    Reply

Leave a Reply

Menu