SQL Server 2000: How can I tell how many plans a stored procedure has cached?

Question

Sometimes when diagnosing issues with our SQL Server 2000 database it might be helpful to know that a stored procedure is using a bad plan or is having trouble coming up with a good plan at the time I'm running into problems. I'm wondering if there is a query or command I can run to tell me how many execution plans are cached currently for a particular stored procedure.

Answer 1

You can query the cache in a number of different ways, either looking at its contents, or looking at some related statistics.

A couple of commands to help you along your way:

SELECT * FROM syscacheobjects -- shows the contents of the procedure 
    -- cache for all databases
DBCC PROCCACHE -- shows some general cache statistics
DBCC CACHESTATS -- shows  the usage statistics for the cache, things like hit ratio

If you need to clear the cache for just one database, you can use:

DBCC FLUSHPROCINDB (@dbid) -- that's an int, not the name of it. 
           -- The int you'd get from sysdatabases or the dbid() function

---

Edit: above the line is for 2000, which is what the question asked. However, for anyone visiting who's using SQL Server 2005, it's a slightly different arrangement to the above:

select * from sys.dm_exec_cached_plans -- shows the basic cache stuff

A useful query for showing plans in 2005:

SELECT  cacheobjtype, objtype, usecounts, refcounts, text
from sys.dm_exec_cached_plans p
join  sys.dm_exec_query_stats s on p.plan_handle = s.plan_handle
cross apply sys.dm_exec_sql_text(s.sql_handle)