Finding the contents of the Plan Cache

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:

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!