HOTSPOT
Background
You manage the Microsoft SQL Server environment for a company that manufactures and sells automobile
parts.
The environment includes the following servers: SRV1 and SRV2. SRV1 has 16 logical cores and hosts a SQL
Server instance that supports a mission-critical application. The application has approximately 30,000
concurrent users and relies heavily on the use of temporary tables.
The environment also includes the following databases: DB1, DB2, and Reporting. The Reporting database is
protected with Transparent Data Encryption (TDE). You plan to migrate this database to a new server. You
detach the database and copy it to the new server.
You are performing tuning on a SQL Server database instance. The application which uses the database was
written using an object relationship mapping (ORM) tool which maps tables as objects within the application
code. There are 30 stored procedures that are regularly used by the application.
After reviewing the plan cache you have identified that a large number of simple queries are using parallelism,
and that execution plans are not being kept in the plan cache for very long.
You review the properties of the instance (Click the Exhibit button).
Exhibit:
You need to resolve the identified issues.
Use the drop-down menus to select the answer choice that answers each question based on the information
presented in the graphic.
Hot Area:
Answer: 
Explanation:
From exhibit we see:
Cost Threshold of Parallelism: 5
Optimize for Ad Hoc Workloads: false
Max Degree of Parallelism: 0 (This is the default setting, which enables the server to determine the maximum
degree of parallelism. It is fine.)
Locks: 0
Query Wait: -1
Box 1: Optimize for Ad Hoc Workload
Change the Optimize for Ad Hoc Workload setting from false to 1/True.
The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that
contain many single use ad hoc batches. When this option is set to 1, the Database Engine stores a small
compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled
plan. This helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans
that are not reused.
Incorrect Answers:
Not Query Wait: Use the query wait option to specify the time in seconds (from 0 through 2147483647) that a
query waits for resources before timing out.Box 2: Cost Threshold for Parallelism to 50
Increase the Cost Threshold for Parallelism from 5 to 50.
Use the cost threshold for parallelism option to specify the threshold at which Microsoft 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.
Note: Longer queries usually benefit from parallel plans; the performance advantage negates the additional
time required to initialize, synchronize, and terminate parallel plans. The cost threshold for parallelism option is
actively used when a mix of short and longer queries is run. The short queries run serial plans, whereas the
longer queries use parallel plans. The value of cost threshold for parallelism determines which queries are
considered short, and they should therefore be run using serial plans.https://technet.microsoft.com/en-us/library/ms188603(v=sql.105).aspx
https://msdn.microsoft.com/en-us/library/cc645587.aspx