SQL SERVER – Override Server’s Configuration of Max Degree of Parallelism

One of the interesting questions, I received during the recent Comprehensive Database Performance Health Check by one of the attendees. The question was – Is it possible to Override Server’s Configuration of Max Degree of Parallelism?

Of course Yes. You can always override the server’s max degree of parallelism with the Option MAX Degree. Let us see the simple example of the same. My laptop on which I ran this demonstration has a total of 8 logical threads.

From Low to High Max Degree of Parallelism

First set server’s configuration to a lower value of 2.

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

Now run the following query on the server with an explicit command of MAXDOP and check the execution plan.

SELECT *
FROM [WideWorldImporters].[Sales].[Orders]
ORDER BY [OrderDate]
OPTION (MAXDOP 8) 

You may notice even though we have a server configuration set to 2, our query ran with 8 processors.

From High to Low MAXDOP of Parallelism

First set server’s configuration to a lower value of 8.

EXEC sys.sp_configure N'max degree of parallelism', N'8'
GO
RECONFIGURE WITH OVERRIDE
GO

Now run the following query on the server with an explicit command of MAXDOP and check the execution plan.

SELECT *
FROM [WideWorldImporters].[Sales].[Orders]
ORDER BY [OrderDate]
OPTION (MAXDOP 2) 

You may notice even though we have a server configuration set to 8, our query ran with 2 processors.

The hint Option MAXDOP overrides the SQL Server’s configuration settings in the server’s settings.

Here are a few additional blog posts on this topic:

Do Queries Always Respect Cost Threshold of Parallelism? – Interview Question of the Week #216

Microsoft Dynamics CRM – Max Degree of Parallelism Settings and Slow Performance

SQL SERVER – Is Stream Aggregate is Same as Gather Streams of Parallelism?

SQL SERVER – Parallelism for Heap Scan

SQL SERVER – CXPACKET – Parallelism – Usual Solution – Wait Type – Day 6 of 28
If I have a dual core computer and I would like to get a query executed with parallelism in order to test it, how would I do that? You can use the AdventureWorks database and let me know if you can get a query to execute in parallel.

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