A SQL SERVER RECENTLY SUFFERED FROM ALL CPUS RUNNING AT 100 PERCENT...

8. A SQL Server recently suffered from all CPUs running at 100 percent, which

you found to be caused by a missing index. You fixed the problem, but you

want to stop any new occurrences from saturating all CPUs. Which of these

options would achieve this?

A. Use sp_configure to set max degree of parallelism

B. Use Configuration Manager to configure the SQL Server Service to use

fewer CPUs

C. Run the Database Tuning Advisor and apply any recommendations

D. Use sp_configure to set cost threshold for parallelism

Correct Answer & Explanation: A. Setting max degree of parallelism to less than

the number of CPUs will prevent SQL Server running parallel executions

across all CPUs. Incorrect Answers & Explanations: B, C, D. Answer B is

incorrect; you cannot use Configuration Manager to configure SQL Server

CPU usage. Answer C is incorrect because DTA recommendations will only

reduce the amount of parallelism that occurs. Answer D is incorrect because

cost threshold for parallelism controls how expensive queries must be before

being considered for parallelism.