-- Originally from http://www.databasejournal.com/features/mssql/article.php/3687186/Monitoring-Stored-Procedure-Usage.htm
SELECT DB_NAME(st.dbid) DBName
      ,cp.objtype
      ,cp.cacheobjtype
      ,OBJECT_SCHEMA_NAME(objectid,st.dbid) SchemaName
      ,OBJECT_NAME(objectid,st.dbid) OBJECT
      ,MAX(cp.usecounts) execution_count
      ,SUM(CONVERT(BIGINT,qs.total_physical_reads) + CONVERT(BIGINT,qs.total_logical_reads) + CONVERT(BIGINT,qs.total_logical_writes)) total_IO
      ,SUM(CONVERT(BIGINT,qs.total_physical_reads) + CONVERT(BIGINT,qs.total_logical_reads) + CONVERT(BIGINT,qs.total_logical_writes)) / (MAX(cp.usecounts)) avg_IO
      ,SUM(CONVERT(BIGINT,qs.total_physical_reads)) total_physical_reads
      ,SUM(CONVERT(BIGINT,qs.total_physical_reads)) / (MAX(cp.usecounts) * 1.0) avg_physical_read
      ,SUM(CONVERT(BIGINT,qs.total_logical_reads)) total_logical_reads
      ,SUM(CONVERT(BIGINT,qs.total_logical_reads)) / (MAX(cp.usecounts) * 1.0) avg_logical_read
      ,SUM(CONVERT(BIGINT,qs.total_logical_writes)) total_logical_writes
      ,SUM(CONVERT(BIGINT,qs.total_logical_writes)) / (MAX(cp.usecounts) * 1.0) avg_logical_writes
      ,SUM(CONVERT(BIGINT,qs.total_worker_time)) total_cpu_time
      ,SUM(CONVERT(BIGINT,qs.total_worker_time)) / (MAX(cp.usecounts) * 1.0)  avg_cpu_time
      ,SUM(CONVERT(BIGINT,qs.total_elapsed_time)) total_elapsed_time
      ,SUM(CONVERT(BIGINT,qs.total_elapsed_time)) / MAX(cp.usecounts) avg_elapsed_time
      ,SUM(CONVERT(BIGINT,cp.size_in_bytes)) AS SumSize_in_bytes
      ,AVG(CONVERT(BIGINT,cp.size_in_bytes)) AS AvgSize_in_bytes
      ,MAX(qs.plan_generation_num) AS plan_generation_num
      ,MIN(qs.creation_time) AS Creation_time
      ,MAX(qs.last_execution_time) AS Last_execution_time
      ,MAX(st.text) AS SQLtext
      --,MAX(qs.query_hash) AS MaxQueryHash -- 2008 and up only.
      --,MAX(qs.query_plan_hash) AS MaxQueryPlanHash -- 2008 and up only.
 FROM sys.dm_exec_query_stats qs
 CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
 INNER JOIN sys.dm_exec_cached_plans cp ON qs.plan_handle = cp.plan_handle
-- WHERE 
--   DB_NAME(st.dbid) is NOT NULL -- in SQL2005, this removes Ad-hoc as well as system entries. In SQL2012, this removes some system entries, but not Adhoc.
--   cp.objtype = 'Proc'
--   cp.objtype = 'Prepared'
--   cp.objtype = 'Trigger'
--   cp.objtype = 'Adhoc'
--   cp.objtype <> 'Adhoc'
 GROUP BY DB_NAME(st.dbid)
  ,OBJECT_SCHEMA_NAME(objectid,st.dbid)
  ,OBJECT_NAME(objectid,st.dbid)
  ,cp.cacheobjtype
  ,cp.objtype
  ,qs.plan_handle -- SQL2005 way of differentiating between Adhoc statements.  Even changes in only literal values are seen as different!
  --,qs.query_hash -- normal SQL2008 and up Adhoc subgrouping (non-literal differences)- Queries that differ only by literal values have the same query hash
  --,qs.query_plan_hash -- alternate SQL2008 and up Adhoc subgrouping (execution plan differences + non-literal differences) - Query execution plans that have the same physical and logical operator tree structure, as well as identical attribute values for the subset of important operator attributes, will have the same query plan hash.  When queries with identical query hashes are executed against different data, the differences in the cardinality of query results can cause the query optimizer to choose different query execution plans, resulting in different query plan hashes.
 --ORDER BY MAX(st.text)
 ORDER BY SUM(CONVERT(BIGINT,qs.total_physical_reads) + CONVERT(BIGINT,qs.total_logical_reads) + CONVERT(BIGINT,qs.total_logical_writes)) DESC
 --ORDER BY AVG(CONVERT(BIGINT,cp.size_in_bytes)) DESC
 --ORDER BY SUM(CONVERT(BIGINT,qs.total_worker_time)) / (MAX(cp.usecounts) * 1.0) DESC