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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) ) FROM szamolotabla o1 CROSS JOIN szamolotabla o2 option (maxdop 8) -- result 33520 ms SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) ) FROM szamolotabla o1 CROSS JOIN szamolotabla o2 option (maxdop 6) -- result 43461 ms SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) ) FROM szamolotabla o1 CROSS JOIN szamolotabla o2 option (maxdop 4) -- result 59837 ms SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) ) FROM szamolotabla o1 CROSS JOIN szamolotabla o2 option (maxdop 3) -- result 80219 ms SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) ) FROM szamolotabla o1 CROSS JOIN szamolotabla o2 option (maxdop 2) -- result 116805 ms SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) ) FROM szamolotabla o1 CROSS JOIN szamolotabla o2 option (maxdop 1) -- result 232825 |
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
You can reduce the parallelism in your server with increasing the Cost Threshold for Parallelims.
You can set it for individually for every database:
Max DOP for you database and Max DOP For Secondary for your Always ON AG secondary replicas.