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.
SELECT 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 GO ;
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.
- sys.dm_exec_cached_plans (Transact-SQL)
- Useful queries on DMV to understand plan cache behavior