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.
Exhibit
You need to optimize SRV1.
What configuration changes should you implement? To answer, select the appropriate option from each list in
the answer area.
Hot Area:
Answer: 
Explanation:
From the scenario: 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.
Box 1: Change the sisze of the tempdb log file.
The size and physical placement of the tempdb database can affect the performance of a system. For example,
if the size that is defined for tempdb is too small, part of the system-processing load may be taken up with
autogrowing tempdb to the size required to support the workload every time you restart the instance of SQL
Server. You can avoid this overhead by increasing the sizes of the tempdb data and log file.
Box 2: Add additional tempdb files.
Create as many files as needed to maximize disk bandwidth. Using multiple files reduces tempdb storage
contention and yields significantly better scalability. However, do not create too many files because this canreduce performance and increase management overhead. As a general guideline, create one data file for each
CPU on the server (accounting for any affinity mask settings) and then adjust the number of files up or down as
necessary.
Incorrect Answers:
Not MAXDOP:
The MAXDOP setting is fine. From the exhibit we see that MAXDOP is set to 0. This is the default setting,
which enables the server to determine the maximum degree of parallelism.
Note: 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.
https://technet.microsoft.com/en-us/library/ms175527(v=sql.105).aspx