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.

SQL SERVER - 3 Different Ways to Set MAXDOP 3differentways-800x172

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

Database Scoped Configurations, MAXDOP, SQL CPU, SQL Scripts, SQL Server, SQL Server Configuration
Previous Post
SQL SERVER – PREEMPTIVE Waits – Simple Definition
Next Post
SQL SERVER – What is Logical Read?

Related Posts

4 Comments. Leave new

  • gdfishingtime
    April 23, 2021 1:36 am

    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 ?

    Reply
  • From which MS SQL Version this syntax is supported:
    ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 2
    ?

    Reply
  • What if MAXDOP is changed to 8 at server level and still 0 at database level ?

    Reply

Leave a Reply