Q8.11: View MRU-LRU procedure cache chain
dbcc procbuf gives a listing of the current contents of the procedure
cache. By repeating the process at intervals it is possible to watch
procedures moving dowm the MRU-LRU chain, and so to see how long
procedures remain in cache. The neat thing about this approach is that
you can size your cache according to what is actually happening,
rather than relying on estimates based on assumptions that may not
hold on your site.
To run it:
<![CDATA[
dbcc traceon(3604)
go
dbcc procbuf
go
]]>
If you use sqsh it's a bit easier to grok the output:
<![CDATA[
dbcc traceon(3604);
dbcc procbuf;|fgrep pbname
]]>
See Q8.7 regarding procedure cache sizing.