Grab a pen and notepad, and jot down your answers as you go, then check your answers at the key at the bottom of the page.
Questions
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
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
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
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
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
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
Q7. ‘Estimated subtree cost’ is an estimate of how many milliseconds the query will take.
- True
- False
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
Q9. You cannot override the ‘cost threshold for parallelism’ setting with a supported query hint.
- True
- False
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
Scroll down for the answer key :point_down:
Answer Key
- A1: 1
- A2: 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
- A3: 4
- A4: 8
- A5: 2
- A6: 4
- A7: False. Cost isn’t a measure of time in SQL Server.
- A8: This is true! Single threaded plans are compared against the threshold. Parallel plans may be below the cost.
- A9: This is false, because the ‘USE PLAN’ hint, hinting a parallel plan, is supported.
- A10. 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.