Which two database settings should you modify?

HOTSPOT

You have a server named SQL1 that has SQL Server 2014 installed. SQL1 has 32 processor cores and 64 GB of RAM.

SQL1 hosts a database used for a public-facing marketing website. SQL1 performs Online Transaction Processing (OLTP) operations only.

Several of the queries that run on SQL1 use the 32 processor cores and complete in 30 ms.

You need to reduce the number of queries that use multiple cores. The solution must also reduce the number of processor cores used by the queries that require multiple processor cores.

Which two database settings should you modify? To answer, select the appropriate settings in the answer area.

View Answer

Answer:

Explanation:

Cost Threshold for Parallelism

The cost threshold for parallelism option specifies the threshold at which SQL Server creates and runs parallel plans for queries. SQL Server creates and runs a parallel plan for a query only when the estimated cost to run a serial plan for the same query is higher than the value set in cost threshold for parallelism. The cost refers to an estimated cost required to run the serial plan on a specific hardware configuration, and is not a unit of time. The cost threshold for parallelism option can be set to any value from 0 through 32767. The default value is 5.

Max Degree of Parallelism

When an instance of SQL Server runs on a computer that has more than one microprocessor or CPU, it detects the best degree of parallelism, that is, the number of processors employed to run a single statement, for each parallel plan execution. You can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution.

References: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-cost­threshold-for-parallelism-server-configuration-option