Aggregate performance statistics

I mentioned in another post how impressed I am with the new DMV's.  The sys.dm_exec_query_stats view is very helpful for determining what queries are running long, or consuming a bit heavily on the resources.  Per BOL, this view returns aggregate performance statistics for cached query plans.  I've posted some examples below.  Let me know if you have any questions, or any examples of your own to share.

This one will return the TOP 10 most CPU heavy statements being run:

SELECT TOP 10  
  SUBSTRING(b.text, (a.statement_start_offset/2) + 1,((CASE  
   statement_end_offset WHEN -1 THEN DATALENGTH(b.text) 
   ELSE a.statement_end_offset END - a.statement_start_offset)/2) + 1) AS statement_text,
  c.query_plan,
  total_worker_time [cpu_time]
FROM sys.dm_exec_query_stats a  
CROSS APPLY sys.dm_exec_sql_text (a.sql_handle) b  
CROSS APPLY sys.dm_exec_query_plan (a.plan_handle) c  
ORDER BY 
total_worker_time DESC

This one will show you the TOP 5 procedures, based on which ones have the greatest execution count:

SELECT TOP 5        

b.text AS 'Procedure Name',

a.execution_count AS 'Execution Count',

a.execution_count/DATEDIFF(SECOND, a.creation_time, GETDATE()) AS 'Calls/Second',

a.total_worker_time/a.execution_count AS 'Avg CPU Time',

a.total_worker_time AS 'Total CPU Time',

a.total_elapsed_time/a.execution_count AS 'Avg Elapsed Time',

a.max_logical_reads [MAX Logical Reads],

a.max_logical_writes [MAX Logical Writes],

a.total_physical_reads [Total Physical Reads],

DATEDIFF(MINUTE, a.creation_time, GETDATE()) AS 'Cached How Long'  

FROM
  sys.dm_exec_query_stats a

CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b  

WHERE 

b.dbid = DB_ID() -- <<<ONLY FOR THE CURRENT DATABASE  

ORDER BY 

a.execution_count DESC

There are many other statistics you could retrieve, such as the top I/O bound statements, or even which ones are recompiled more than the others.  This is one that I use to see which queries or procedures are performing slowly:

SELECT 

last_execution_time,

execution_count, 

total_physical_reads,

total_logical_reads,

total_logical_writes,

total_worker_time, 

total_elapsed_time, 

total_elapsed_time / execution_count avg_elapsed_time,

SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,((

CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) 

    ELSE qs.statement_end_offset END -   
  qs.statement_start_offset)/2) + 1) AS statement_text

FROM 

sys.dm_exec_query_stats AS qs 
  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

ORDER BY 

total_elapsed_time / execution_count DESC;

NOTES:   

Important to remember, it can only retrieve what is cached.