Explanation/Reference:
Explanation:
Box 1: EXEC sp_configure 'show advanced options', 1; RECONFIGURE
Box 2: sp_CONFIGURE 'optimize for ad hoc workloads',1
Box 3: RECONFIGURE
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.
References: https://blog.sqlauthority.com/2009/03/21/sql-server-2008-optimize-for-ad-hoc-workloads- advance-performance-optimization/