I know I have a large number of single use plans being created on my SQL 2008 STD Server.
I have enabled "optimize for ad hoc workloads" which has reduced the size of each plan but the cache size still increases (but more plans).
I understand that SQL will manage the Plan Cache based on its own algorithm but I am checking I fully understanding the following article suggests.
It is basically saying that if the SQL Plan part of the cache is greater than a set threshold then clear the cache:
DBCC FREESYSTEMCACHE('SQL Plans')
Am I correct in saying doing this would free up more memory for other processes (if it was run every one or two days) and the only hit would be higher compilation times the next time the query was run?
So it might be a good thing for me to do this on my Prod server -- if I have understood the article correctly!
Out of interest on a 8MB 64 bit machine the returned figures as now are (using Kimberly's logic):
TotalPhysicalMemory (MB) 8191.000
TotalConfiguredMemory (MB) 0.000
MaxMemoryAvailableToSQLServer (%) 0.000000000000000
MemoryInUseBySQLServer (MB) 7089.000
TotalSingleUsePlanCache (MB) 1695.375
PercentOfConfiguredCacheWastedForSingleUsePlans (%) 23.916