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.)
Select and Place:
Correct Answer:
Explanation/Reference:
Explanation:
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.