To view the cache plan SQL that SQL Server has used:
SELECTObjType as 'Cached Object Type',COUNT(*) as 'Number of plans',SUM(CAST(size_in_bytes as BIGINT))/1024x768/1024x768 as 'Plan Cache Size (MB)',AVG(usecounts) as 'AVG Use Count' fromSys.dm_exec_cached_plansGROUP byObjType
The result of the execution is:
Most of the cache plan is the Ad hoc cache Plan (ADHOC) occupancy, 1653Mb, average usage is only 72, lower, and then we look at these occupy the cache only once performed the ad-hoc cache plan:
SELECT COUNT(*) as 'Number of plans', SUM(CAST(size_in_bytes as BIGINT))/1024x768/1024x768 as 'Plan Cache Size (MB)' fromSys.dm_exec_cached_plansWHEREUsecounts= 1 andObjType= '
The result of the execution is:
There are 1461 ad-hoc cache plans that have been executed only once, taking up 338Mb in the cache, which is completely wasted on programs that cannot be reused, and can be stripped of cache space by removing these unused cache plans.
Conclusion:
The option to optimize for an ad hoc cache workload ensures that this does not occur and it is recommended that all SQL Server installations be turned on by default.
Option settings:
Open SQL Server Properties--advanced--optimize for ad-hoc workloads->true can
SQL Server optimizes ad hoc caching