Server Configuration Option (optimize for ad hoc workloads)

During exam preparation I was asked to answer a question that there are a lots of execution plans generated during querying a database and used only once. How can I reduce that and I got following answer

Server Configuration Option (optimize for ad hoc workloads).

The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. When option is enabled only regularly used queries are cached that are used more than once. This helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused.

Let's understand how it works. Run the following text to enable advance option

sp_CONFIGURE 'show advanced options',1RECONFIGUREGO

sp_CONFIGURE ‘optimize for ad hoc workloads’,1RECONFIGUREGO

Now run the following SQL 

/* Clean Cache and Buffers */DBCC FREEPROCCACHEDBCC DROPCLEANBUFFERS
GO/* Run Adhoc Query two  Time */SELECT * FROM HumanResources.Shift
GO 5

/* Check if Adhoc query is cached.
It will return result with Adhoc Query ran two times*/SELECT usecounts, cacheobjtype, objtype, TEXT
FROM sys.dm_exec_cached_plansCROSS APPLY sys.dm_exec_sql_text(plan_handle)WHERE usecounts > 0 ANDTEXT LIKE '%[your sql]%'ORDER BY usecounts DESC;GO

Instead of show 5 different execution plan. It will comes up with only one plan and user count is 5. It means when this option is enabled same plan will execute again and for rarely used queries that are only used for one time will not be cached.

This option is very good for memory utilization. Microsoft mentioned that only advanced options should be changed only by an experienced database administrator or certified SQL Server technician.