DBCC PROCCACHE

• SQL Galaxy

  One Orange Chip

  Points: 29472

  More actions

  February 8, 2011 at 1:40 am

  #143242

  Hi,

  SQL server 2000 & 3 GB RAM, procedure cache found lot of values, really i don't know these values are correct or incorrect. it is depending on database workload. please tell me below values are performance condition. or can i run DBCC FREEPROCCACHE for improve one of the way of performance.

  DBCC PROCCACHE Display information about the procedure cache.

  Num Proc Buffs = 1286

  num proc buffs used = 1286

  num proc buffs = 647

  active proc cache size = 1328

  proc cache used = 1328

  proc cache active = 689

  2. DBCC CACHESTATS

  Object Type LW Avg Stay (ms)

  Proc 2178799.8

  Prepared 6994499.917

  Adhoc 6724906

  These values are very high, it is not remove cache from Lazy writer.

  How to clear the LW Avg Stay (ms)?

  Thanks in advance

  ananda

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  February 9, 2011 at 12:01 pm

  #1284744

  I am sorry, but I really don't understand what you are looking for here.

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service