DRAG DROP
You administer a Microsoft SQL Server 2012 instance.
An application executes a large volume of dynamic queries.
You need to reduce the amount of memory used for cached query plans.
What should you do? (To answer, move the appropriate statements from the list of
statements to the answer area and arrange them in the correct order.)

Answer: See the explanation.

Explanation:
Box 1:

Box 2:

Box 3:

Note:
* SQL SERVER – 2008 – Optimize for Ad hoc Workloads – Advance Performance
Optimization
Every batch (T-SQL, SP etc) when ran creates execution plan which is stored in system for
re-use. Due to this reason large number of query plans are stored in system. However, there
are plenty of plans which are only used once and have never re-used again. One time ran
batch plans wastes memory and resources.
* Let us now enable the option of optimizing ad hoc workload. This feature is available in all
the versions of SQL Server 2008.
sp_CONFIGURE ‘show advanced options’,1
RECONFIGURE
GO
sp_CONFIGURE ‘optimize for ad hoc workloads’,1
RECONFIGURE
GO
SQL Server 2008 has feature of optimizing ad hoc workloads.