Finding the contents of the Plan Cache

Download PDF

I often get asked “how do I find the contents of the SQL Server Plan Cache?”   My first response is just use the Database Health Reports and you can get this through a nice user interface.  But if you still want to get at the Plan Cache contents, I use the following query.

SELECT UseCounts,
       RefCounts,
       CacheObjtype,
       ObjType,
       DB_NAME(dbid) as DatabaseName,
       SQL,
       sqlbytes
  FROM sys.syscacheobjects
 WHERE dbid = db_id();

Again, the Database Health Reportsgives you a much better UI presentation of the conents of the plan cache.

Enjoy the query and please try out the Database Health Reports.

 

More from Stedman Solutions:

SteveStedman5
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!

Leave a Reply

Your email address will not be published. Required fields are marked *

*