Quiz on MAXDOP and Cost Threshold for Parallelism

Q1.If you have the instance level max degree of parallelism set to 2, and a query is classified by Resource Governor into a workload group with MAX_DOP = 1, how many cores will the query use?

Your instance has 8 logical processors. Assume a parallel plan is selected for the query by the SQL Server optimizer.

  •  Up to 64 logical processors
  •  2
  •  0
  •  1
1

Q2. If you have the instance level max degree of parallelism set to 2, and a query is classified by Resource Governor into a workload group with MAX_DOP = 4, how many cores will the query use?Your instance has 8 logical processors. Assume a parallel plan is selected for the query by the SQL Server optimizer.

  •  0
  •  2
  •  Up to 64 logical processors
  •  4
4. This one is tricky! Resource Governor will win unless a query hint lower than the Resource Governor setting is used, in which case it will use the minimum value

Q3. If you have the instance level max degree of parallelism set to 2, and you use an OPTION (MAXDOP 4) query hint, how many cores will the query use?

Your instance has 8 logical processors. Assume a parallel plan is selected for the query by the SQL Server optimizer.

  •  Up to 64 logical processors
  •  0
  •  2
  •  4
4

Q4. If you have the instance level max degree of parallelism set to 0, and you are not otherwise controlling parallelism, how many cores will your query use?

Your instance has 8 logical processors. Assume a parallel plan is selected for the query by the SQL Server optimizer.

  •  4
  •  1
  •  8
  •  0
8

Q5. If you have the instance level max degree of parallelism set to 4, you are using database level configuration to set max degree of parallelism to 2, how many cores will a query using that database use?

Your instance has 8 logical processors. Assume a parallel plan is selected for the query by the SQL Server optimizer.

  •  4
  •  0
  •  2
  •  Up to 64 logical processors
2

Q6. If you have the instance level max degree of parallelism set to 6, you are using database level configuration to set max degree of parallelism to 2, AND you use an OPTION (MAXDOP 4) query hint, how many cores will that query using that database use?

Your instance has 8 logical processors. Assume a parallel plan is selected for the query by the SQL Server optimizer.

  •  6
  •  2
  •  Up to 64 logical processors
  •  4
4

Q7. ‘Estimated subtree cost’ is an estimate of how many milliseconds the query will take.

  •  True
  •  False
False. Cost isn’t a measure of time in SQL Server.

Q8. It is possible to see parallel query plans in Query Store or the SQL Server execution plan cache with an estimated cost below the ‘cost threshold for parallelism’ setting.

  •  True
  •  False
This is true! Single threaded plans are compared against the threshold. Parallel plans may be below the cost.

Q9. You cannot override the ‘cost threshold for parallelism’ setting with a supported query hint.

  •  True
  •  False
This is false, because the ‘USE PLAN’ hint, hinting a parallel plan, is supported.

Q10. You can use Query Store to force a parallel plan for a single-threaded query whose cost is below the cost threshold for parallelism.

  •  True
  •  False
This is true– as long as the plan you want to force is in Query Store for the same query text. This is most likely to happen if you’ve been tuning cost threshold and have raised the value recently.
Back to: How To Decipher CXPACKET Waits and Control Parallelism (4 hours) > Quiz

Share a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu