Clear SQL Plans -- DBCC FREESYSTEMCACHE('SQL Plans')

• UncleBoris

  SSCrazy

  Points: 2018

  More actions

  May 14, 2013 at 4:38 pm

  #293958

  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!

  http://www.sqlskills.com/blogs/kimberly/plan-cache-adhoc-workloads-and-clearing-the-single-use-plan-cache-bloat/

  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

  TotalNumberOfSingleUsePlans 99134

  PercentOfConfiguredCacheWastedForSingleUsePlans (%) 23.916

  thanks