Microsoft – SQLServer – Plan Re\use
You have spent a lot of time writing your Stored Procedures, but are you getting the re-use that makes them better for the system:
select tblPlanCache.cacheobjtype , tblPlanCache.objType , tblPlanCache.usecounts , tblPlanCache.refcounts , object_name(tblSQLText.objectid, tblSQLText.dbid) as obj , tblSQLText.Text from sys.dm_exec_cached_plans tblPlanCache cross apply sys.dm_exec_sql_text(tblPlanCache.plan_handle) tblSQLText order by tblPlanCache.usecounts desc
The Stored Procedure \ Prepared Statements that are getting the most re-used are those with the highest useCounts.
Knew this already, but forced to revisit while reading Andrew J. Kelly piece for SQL Server Pro \ SQLMag.
The web link is available:
A lot more data is available