Optimize query plan memory used for Ad-hoc Queries

Problem: An application executes a large volume of dynamic queries. How to reduce the amount of memory used for cached query plans?

Solution: 

Optimize for Ad Hoc Workload, Starting from SQL Server 2008

A Query when gets executed against a database engine creates and execution plan which will be stored in the query plan cache. Plans are stored for using the same execution plan when the same query is executed again it saves time to compile.

 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.

SQL Server 2008 has feature of optimizing ad-hoc workloads which will work best for the ad hoc queries and looks for the execution counts in the plan caches to see if the query is executed more than once or else it will not cache it. This will reduce the memory used to store the plans for un used queries or procedures.

Example:

In this example i would like to show we can enable the optimize ad hoc workload option from T-SQL and SSMS Gui.

EXEC sp_CONFIGURE 'show advanced options',  1

RECONFIGURE

GO

EXEC sp_CONFIGURE 'optimize for ad hoc workloads',     1

RECONFIGURE

GO

Select name,description,value_in_use,is_dynamic from sys.configurations where name ='optimize for ad hoc workloads'

OUTPUT:

name	description	value_in_use	is_dynamic
optimize for ad hoc workloads	When this option is set, plan cache size is further reduced for single-use adhoc OLTP workload.	1	1