Correct Answer:
Explanation/Reference:
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 a 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.