Test your MAXDOP – SQL Server max degree of parallelism

The default value of your SQL server is MAXDOP=0. It means there is no limit for parallelism. Your query can use all of your CPU core.

Scenario:
If your SQL Server is not used, than your CPU utilization is low,
your query can use all of the server’s resources.
I run a cross join query in a table which has 40.000 rows.
The server has 8 core.
I set hint for the query to simulate the MAXDOP server setting.

The query and the result:

So if your query use only 1 thread it run longer ( in the example 232 seconds), if it use 8 thread it run much more quicker ( 33 seconds).
So 232/33 ~= 7 . Ok the speed is not 8x , but almost 8. The operating system and other processes use system resources.

Should we use the maxdop value without limit? In the real life a lot of queries run at the same time, so if your query get all of the resource the other waiting the resources. So you try to figure out what is ideal value?
If you have no idea, or don’t have experience with the new databases , you can set the recommended value:
https://support.microsoft.com/en-us/help/2806535/recommendations-and-guidelines-for-the-max-degree-of-parallelism-confi

Server Propertis - MAXDOP
Server Propertis – MAXDOP

You can reduce the parallelism in your server with increasing the Cost Threshold for Parallelims.

You can set it for individually for every database:

Database settings
Database settings

Max DOP for you database and Max DOP For Secondary for your Always ON AG secondary replicas.

Leave a Reply

Your email address will not be published. Required fields are marked *