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:

 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:

http://www.sqlmag.com/project-plans/migrating-to-sql-server-2008-r2/detail/catpath/sql-server/topic/top-10-performance-tips-for-sql-server/page/2

A lot more data is available

http://blogs.msdn.com/b/sqlprogrammability/archive/2007/01/23/4-0-useful-queries-on-dmv-s-to-understand-plan-cache-behavior.aspx

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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