Monitoring the Plan Cache

If you run SQL Server on a 64bit server, then something that you should be monitoring from time to time is the size of your procedure cache.  This is especially important if you have an application that issues adhoc/non-parameterized queries against the SQL Server.  Since the procedure cache is stored as a part of the BPool, it can starve your buffer cache for precious memory.  A simple query that can help monitor this is:

with plancache_cte as
(select single=sum(case usecounts when 1 then 1    else 0 end),
        singlesize=sum(case usecounts when 1 then cast(size_in_bytes as bigint)/1024 else 0 end),
        reused=sum(case usecounts when 1 then 0 else 1 end),
        reusedsize=sum(case usecounts when 1 then 0 else cast(size_in_bytes as bigint)/1024 end),
        total=count(usecounts),
        totalsize=sum(cast(size_in_bytes as bigint)/1024)
from sys.dm_exec_cached_plans)

select
'Single use plans (usecounts=1)'= single,
'Single use plans size KB (usecounts=1)'= singlesize,
'Re-used plans (usecounts>1)'= reused,
'Re-used plans size KB (usecounts>1)'= reusedsize,
're-use %'=cast(100.0*reused / total as dec(5,2)),
'total usecounts'=total,
'total cache size'=totalsize
from plancache_cte

The SQL Programability Team blogged a wonderful series of blog posts in January 2007, that cover this topic and explain exactly what is going on and how it was somewhat fixed in Service Pack 2 of SQL Server 2005

http://blogs.msdn.com/sqlprogrammability/archive/2007/01.aspx