SQL Server – Programmable Objects – Usage Pattern


Everyone is familiar with running top N Queries to determine a System’s most prevalent SQL Queries.

It is always good to be able to see those queries and tune them.

However, on a system that I am working on we do not really use as much hand-written SQL, but rely more on ORM Tooling.

In our case, Entity Framework.

It so happens that we rely quite a bit on Views and for sake of re-usability the views use quite a bit of Scaler Functions,  as well.


So how do I know which programmables objects are being used the most.  Well try sys.dm_exec_cached_plans.

Test Query:

	  ROW_NUMBER() OVER(ORDER BY tblCachedPlan.usecounts desc) AS rowNumber
	, object_name(tblObject.object_id) [ObjectName]
	, tblCachedPlan.usecounts as [useCount]
	, tblCachedPlan.refcounts as [referenceCount]
	, tblCachedPlan.size_in_bytes as [sizeInBytes]
	, tblObject.type_desc as [Type]
	, tblSQLText.text as sqlText

FROM sys.dm_exec_cached_plans tblCachedPlan

       CROSS APPLY sys.dm_exec_sql_text(tblCachedPlan.plan_handle) tblSQLText

       INNER JOIN sys.objects tblObject

            ON tblSQLText.objectid = tblObject.object_id

WHERE tblSQLText.dbid = DB_ID()

order by tblCachedPlan.usecounts desc





This post is in gratitude to Michael K. Campbell.  Michael has an article in SQL Server Pro – “Finding Potentially Unused Views in Your Databases” ( http://sqlmag.com/blog/finding-potentially-unused-views-your-databases ).

Michael in turn thanked Glenn Berry – Looking for Unused Stored Procedures in SQL  (http://sqlserverperformance.wordpress.com/2009/07/08/looking-for-unused-stored-procedures-in-sql-server-2008/)


I now have a good data as to which programmable objects are the most popular.  And, possibly can focus my attention on those.


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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s