Procedure cache value

• klnsuddu

  SSCertifiable

  Points: 5836

  More actions

  March 23, 2010 at 1:21 pm

  #386563

  Hi,

  We have 3 node a/a/p cluster setup with SQL Server 2005 Eterprise edition 64 bit with SP3 and have 16 GB Memory

  We have 3 sql instances on one of the active node and max memory is set to 4 GB to each instancee and left 4 GB to OS and lock pages in memory is enabled.

  I'm seeing the value for Procedure cache dropping to 75% and quickly reaching to 80% then drops again to 75% for all the 3 instances. This change 75% to 80% (but not <75%) occuring whole day.

  So is this an issue with Memory contention? and what is the best value for Procedure cache?

  Thanks

• MANU-J.

  SSC-Dedicated

  Points: 31126

  More actions

  March 23, 2010 at 2:49 pm

  #1138566

  First try to find what exactly is consuming the most in proc. cache.

  SELECT name,

  [MB used] = SUM(single_pages_kb + multi_pages_kb)/1024.0

  FROM sys.dm_os_memory_clerks

  WHERE name IN (

  N'Object Plans', -- Procedures, triggers, etc

  N'SQL Plans' -- Ad-hoc stuff

  )

  GROUP BY

  name

  ORDER BY

  [MB used] DESC;

  MJ