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.