SQL Server dm_exec_query_stats scripts SQL Server Tips by Burleson Consulting

DMV queries:

-- Get Top 20 executed stored procedures ordered by total worker time (CPU pressure)

select
   top 20 qt.text                                                              as 'procedure name',
   qs.total_worker_time                                                        as 'tot worker time',
   qs.total_worker_time/qs.execution_count                                     as 'avg worker time',
   qs.execution_count                                                          as 'executions',
   isnull(qs.execution_count/datediff(second, qs.creation_time, getdate()), 0) as 'calls/second',
   isnull(qs.total_elapsed_time/qs.execution_count, 0)                         as 'avg elapsed time',
   qs.max_logical_reads,
   qs.max_logical_writes,
   datediff(minute, qs.creation_time, getdate())                               as 'age in cache'
from
   sys.dm_exec_query_stats                         as qs
   cross apply
   sys.dm_exec_sql_text(qs.sql_handle)             as qt
where
   qt.dbid = db_id()
order by
   qs.total_worker_time desc

-- Top 100 executed stored procedures by executions
select
   top 100 qt.text                                                  as 'procedure name',
   qs.execution_count                                               as 'execution count',
   qs.execution_count/datediff(second, qs.creation_time, getdate()) as 'calls/second',
   qs.total_worker_time/qs.execution_count                          as 'avg worker time',
   qs.total_worker_time                                             as 'tot worker time',
   qs.total_elapsed_time/qs.execution_count                         as 'avg elapsed time',
   qs.max_logical_reads,
   qs.max_logical_writes,
   qs.total_physical_reads,
   datediff(minute, qs.creation_time, getdate())                    as 'age in cache'
from
      sys.dm_exec_query_stats                                       as qs
   cross apply
      sys.dm_exec_sql_text(qs.sql_handle)                           as qt
where
   qt.dbid = db_id()
order by
   qs.execution_count desc