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.
Question
Options
- AEnable Contained Databases
- BAllow Triggers to Fire Others
- COptimize for Ad hoc Workloads
- Dscan for Startup Procs
How the community answered
(39 responses)- A8% (3)
- B3% (1)
- C90% (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.
Enable Contained Databases controls authentication scope and cross-database dependencies, not query plan cache memory allocation.
Allow Triggers to Fire Others governs recursive and nested trigger execution behavior and has no relationship to plan cache memory consumption.
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.
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
Community Discussion
No community discussion yet for this question.