SQL Server optimizes ad hoc caching

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