nerdexam
Microsoft

70-466 · Question #75

You have a SQL Server 2012 instance that hosts a single-user database. The database does not contain user-created stored procedures or user-created functions. You need to minimize the amount of memory

The correct answer is C. Optimize for Ad hoc Workloads. This question tests knowledge of SQL Server advanced options for reducing plan cache memory in ad hoc workload environments without stored procedures.

Configure and maintain an analysis services database

Question

You have a SQL Server 2012 instance that hosts a single-user database. The database does not contain user-created stored procedures or user-created functions. You need to minimize the amount of memory used for query plan caching. Which advanced server option should you modify?

Options

  • AEnable Contained Databases
  • BAllow Triggers to Fire Others
  • COptimize for Ad hoc Workloads
  • Dscan for Startup Procs

How the community answered

(39 responses)
  • A
    8% (3)
  • B
    3% (1)
  • C
    90% (35)

Why each option

This question tests knowledge of SQL Server advanced options for reducing plan cache memory in ad hoc workload environments without stored procedures.

AEnable Contained Databases

Enable Contained Databases controls authentication scope and cross-database dependencies, not query plan cache memory allocation.

BAllow Triggers to Fire Others

Allow Triggers to Fire Others governs recursive and nested trigger execution behavior and has no relationship to plan cache memory consumption.

COptimize for Ad hoc WorkloadsCorrect

The 'Optimize for Ad hoc Workloads' option causes SQL Server to store only a small compiled plan stub on the first execution of an ad hoc query instead of the full plan object. Because the database has no stored procedures or user-defined functions, all queries are ad hoc, so this option prevents one-time-use plans from consuming large amounts of plan cache memory.

Dscan for Startup Procs

Scan for Startup Procs controls whether stored procedures flagged for auto-execution run at server startup and does not affect query plan caching in any way.

Concept tested: Optimize for Ad hoc Workloads server configuration option

Source: https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/optimize-for-ad-hoc-workloads-server-configuration-option

Topics

#plan cache optimization#optimize for ad hoc workloads#SQL Server memory#single-user database

Community Discussion

No community discussion yet for this question.

Full 70-466 Practice