You are deploying a Microsoft SQL Server database that will support a mixed OLTP and OLAP workload. The target virtual machine has four CPUs.

You need to ensure that reports do not use all available system resources.

What should you do?

A. Increase the default database file auto growth sizes.

B. Increase the value for the Minimum System Memory setting.

C. Set MAXDOP to half the number of CPUs available.

D. Increase the value for the Minimum Memory per query setting.

Explanation:

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 (MAXDOP) option to limit the number of processors to use in parallel plan execution.

Incorrect Answers:

A: The Auto Close property exposes server behavior for databases not accessed by a user. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

References: https://msdn.microsoft.com/en-us/library/ms189094.aspx