question

KrisT_SQL avatar image
KrisT_SQL asked

MaxDOP and CostThresholdForParallelism

Hi SSC, I have a Production AWS Windows Server with 8 cores, SQL 2016 Enterprise installed, MaxDOP setting of 8 at Instance/Server Level, MaxDOP of 0 at Database Level, CostThresholdForParallelism of 35. My understanding is that every query estimated to take 35 or more seconds to execute will go parallel, using all 8 cores (leaving none available for other processes). As a result other processes will have to wait (blocking) until at least one or more cores become available based on its estimated cost of execution.

performanceparallelismmaxdop
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
philmac750 avatar image
philmac750 answered

Hi, the Cost is the compute cost as estimated by the SQL Query optimiser, not a run time in seconds, but your understanding of the threshold is spot on and works exactly as shown here http://sqlgrandad.co.uk/Insights/MAXDOP.html

SQL Grandad

,

Hi the CostThreshold isn't a number of seconds, it is the computer cost of the query as determined by the SQL optimiser. If the cost of running a query is < 35, the query will use a single CPU, whereas > 35 it should parallel. Have a look at this http://sqlgrandad.co.uk/Insights/MAXDOP.html, it might help a bit.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.