Configuring SQL Server for Optimization - Microsoft SQL Server 2012 Performance Tuning - page 402

3. By running the SELECT query in step 1, it made and entry in plan cache in the very

first time. Confirm the same with the following screenshot.

4. Now, set the value of Optimize for Ad hoc Workloads to 1, by executing the

following query:
EXEC sp_configure 'optimize for ad hoc workloads',1
RECONFIGURE
GO

5. Again, clear the cache:

DBCC FREEPROCCACHE
GO

6. Execute the SELECT query again:

SELECT * FROM Sales.SalesOrderDetail WHERE SalesOrderID=43659
GO

7. You can confirm whether anything was inserted in plan cache or not by executing the
following query:
SELECT
CP.usecounts AS CountOfQueryExecution
,CP.cacheobjtype AS CacheObjectType
,CP.objtype AS ObjectType
,ST.text AS QueryText
FROM
sys.dm_exec_cached_plans AS CP
CROSS APPLY
sys.dm_exec_sql_text(plan_handle) AS ST