Lots of people reached out to me after reading yesterday’s blog post SQL SERVER – 16 CPU vs 1 CPU: Performance Comparison, I got lots of email about how to set MAXDOP for any query. Today we will be learning 3 different ways to set MAXDOP. I often discuss this topic during the Comprehensive Database Performance Health Check.
Method 1: MAXDOP at Server Level
Here is how you can change the MAXDOP at the server level.
EXEC sys.sp_configure N'max degree of parallelism', N'2' GO
Here I have specified the Max Degree of Parallelism to 2. You should be based on what works best for your query.
Method 2: MAXDOP at Database Level
Here is how you can change the MAXDOP at the database level.
USE [WideWorldImporters] GO ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 2; GO
Here I have specified the Max Degree of Parallelism to 2. You should be based on what works best for your query.
Method 3: MAXDOP at Query Level
Here is how you can change the MAXDOP at the query level.
SELECT * FROM Table OPTION (MAXDOP 2)
Here I have specified the Max Degree of Parallelism to 2. You should be based on what works best for your query.
One should remember that when you add more CPU or change MAXDOP to a higher value, it does not mean the query will run faster. I have done a test previously when I ran the test for a query with 16 CPU and 1 CPU. You can watch my experiment over here. If you like the blog post, you can always follow my YouTube channel where you can see many similar videos.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)Â
4 Comments. Leave new
What if there are two conflicting settings? Is there an order of precedence? For example if Server MAXDOP is 2 but the query hint specifies 4, what is the result ?
From which MS SQL Version this syntax is supported:
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 2
?
What if MAXDOP is changed to 8 at server level and still 0 at database level ?
It will follow the server-level settings.