SQL SERVER – 3 Different Ways to Set MAXDOP

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)