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!
Leave a Reply