Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Clear SQL Plans -- DBCC FREESYSTEMCACHE('SQL Plans') Expand / Collapse
Author
Message
Posted Tuesday, May 14, 2013 4:38 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, October 19, 2014 7:20 PM
Points: 100, Visits: 501
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


Post #1452877
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse