Microsoft – SQLServer – Plan Re\use

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:


     , 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)


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

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s