From BOL: Returns aggregate performance statistics for cached query plans. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view.

The CROSS APPLYs to sys.dm_exec_sql_text and sys.dm_exec_query_plan are the expensive part of this query - if running this query on a busy server then limit the rows for which these calls are necessary or if the query text or query plan are not needed remove them altogether. If you do want to use the XML query plan for the statement make sure you read up on the sys.dm_exec_query_plan dmv that returns this. There are plenty of circumstances where what you actually want may require a little extra work.

The expression (elapsed_time - worker_time) can be used to produce non-cpu time, but this figure should not be described in any other way. In practice the non-cpu time is probably the sum of wait time plus time spent in the runnable queue. Calling it wait time is almost reasonable if CPUs are not overloaded and runnable queues short, but I've seen people calling it blocked time and this is not reasonable because there is nothing to suggest that the execution is blocked, it is merely not currently executing.

The plan_generation_num column is often used to show recompiles - generation 1 is a compile, generation 2 onwards are recompiles, so number of recompiles is (plan_generation_num - 1). The Feb 2007 update to SQL 2005 BOL says that this column is used to 'distinguish between instances of plans after a recompile' so the value returned could be greater than the actual number of recompiles.

Don't be a muppet like me and assume the time columns are in milliseconds as they are in so many other places. The times are in microseconds - one millionth of a second. If you see weird timings then check this KB article first "SQL Server timing values may be incorrect when you use utilities or technologies that change CPU frequencies".

SELECT TOP 50
   -- Database, object and SP# are not available for ad hoc and prepared SQL statements.
   CASE WHEN st.dbid = 32767 THEN 'resourceDb' ELSE db_name(st.dbid) END as database_name,
   CASE WHEN st.dbid IS NULL THEN NULL ELSE object_schema_name(st.objectid, st.dbid) END as object_schema_name,
   CASE WHEN st.dbid IS NULL THEN NULL ELSE object_name(st.objectid, st.dbid) END as object_name,
   --st.number as stored_procedure_number,
   --st.encrypted as query_text_is_encrypted,

   substring(st.text,qs.statement_start_offset/2 ,(CASE WHEN qs.statement_end_offset = -1 THEN len(convert(nvarchar(max), st.text)) * 2 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) as statement,
   --st.text as query_text,
   --qp.query_plan as xml_query_plan,

   (qs.plan_generation_num - 1) as statement_recompiles,
   qs.creation_time,
   qs.last_execution_time,
   qs.execution_count,

   (qs.total_elapsed_time / qs.execution_count) as average_elapsed_time,
   (qs.total_worker_time / qs.execution_count) as average_worker_cpu_time,
   ((qs.total_elapsed_time - qs.total_worker_time) / qs.execution_count) as average_worker_wait_time,
   (qs.total_clr_time / qs.execution_count) as average_clr_time,
   (qs.total_physical_reads / qs.execution_count) as average_physical_reads,
   (qs.total_logical_writes / qs.execution_count) as average_logical_writes,
   (qs.total_logical_reads / qs.execution_count) as average_logical_reads,

   qs.total_elapsed_time,
   qs.total_worker_time as total_worker_cpu_time,
   (qs.total_elapsed_time - qs.total_worker_time) as total_worker_wait_time,
   qs.total_clr_time,
   qs.total_physical_reads,
   qs.total_logical_writes,
   qs.total_logical_reads

   --qs.last_elapsed_time,
   --qs.last_worker_time as last_worker_cpu_time,
   --(qs.last_elapsed_time - qs.last_worker_time) as last_worker_wait_time,
   --qs.last_clr_time,
   --qs.last_physical_reads,
   --qs.last_logical_writes,
   --qs.last_logical_reads,
   --
   --qs.min_elapsed_time,
   --qs.min_worker_time as min_worker_cpu_time,
   ---- as min_worker_wait_time,
   --qs.min_clr_time,
   --qs.min_physical_reads,
   --qs.min_logical_writes,
   --qs.min_logical_reads,
   --
   --qs.max_elapsed_time,
   --qs.max_worker_time as max_worker_cpu_time,
   ---- as max_worker_wait_time,
   --qs.max_clr_time,
   --qs.max_physical_reads,
   --qs.max_logical_writes,
   --qs.max_logical_reads

FROM
   sys.dm_exec_query_stats qs
      CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
      --CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

ORDER BY
   --average_worker_cpu_time DESC
   --average_elapsed_time DESC
   --average_worker_cpu_time DESC
   --average_worker_wait_time DESC
   average_clr_time DESC
   --average_physical_reads DESC
   --average_logical_writes DESC
   --average_logical_reads DESC

; WITH cte AS (
SELECT TOP 50

   --qs.plan_handle as plan_handle,
   qs.sql_handle as sql_handle,

   MAX(qs.plan_generation_num - 1) as max_statement_recompiles,
   MIN(qs.creation_time) as min_statement_creation_time,
   MAX(qs.last_execution_time) as max_statement_last_execution_time,
   MAX(qs.execution_count) as max_statement_execution_count,

   -- All timings are in microseconds, one millionth of a second.
   -- Averages use integer maths, not much need for fractions of millionths.
   (SUM(qs.total_elapsed_time) / MAX(qs.execution_count)) as query_statement_average_elapsed_time,
   SUM(qs.total_elapsed_time) as query_statement_total_elapsed_time,
   SUM(qs.last_elapsed_time) as query_statement_last_elapsed_time,

   (SUM(qs.total_worker_time) / MAX(qs.execution_count)) as query_statement_average_worker_cpu_time,
   SUM(qs.total_worker_time) as query_statement_total_worker_cpu_time,
   SUM(qs.last_worker_time) as query_statement_last_worker_cpu_time,

   ((SUM(qs.total_elapsed_time - qs.total_worker_time)) / MAX(qs.execution_count)) as query_statement_average_worker_wait_time,
   (SUM(qs.total_elapsed_time - qs.total_worker_time)) as query_statement_total_worker_wait_time,
   (SUM(qs.last_elapsed_time - qs.last_worker_time)) as query_statement_last_worker_wait_time,

   (SUM(qs.total_clr_time) / MAX(qs.execution_count)) as query_statement_average_clr_time,
   SUM(qs.total_clr_time) as query_statement_total_clr_time,
   SUM(qs.last_clr_time) as query_statement_last_clr_time,

   (SUM(qs.total_physical_reads) / MAX(qs.execution_count)) as query_statement_average_physical_reads,
   SUM(qs.total_physical_reads) as query_statement_total_physical_reads,
   SUM(qs.last_physical_reads) as query_statement_last_physical_reads,

   (SUM(qs.total_logical_writes) / MAX(qs.execution_count)) as query_statement_average_logical_writes,
   SUM(qs.total_logical_writes) as query_statement_total_logical_writes,
   SUM(qs.last_logical_writes) as query_statement_last_logical_writes,

   (SUM(qs.total_logical_reads) / MAX(qs.execution_count)) as query_statement_average_logical_reads,
   SUM(qs.total_logical_reads) as query_statement_total_logical_reads,
   SUM(qs.last_logical_reads) as query_statement_last_logical_reads

FROM 
   sys.dm_exec_query_stats qs 

GROUP BY
   qs.sql_handle
)
SELECT TOP 50

-- Columns from sys.dm_exec_sql_text
   -- Database, object and SP# are not available for ad hoc and prepared SQL statements.
   db_name(st.dbid) as database_name,
   object_schema_name(st.objectid, st.dbid) as object_schema_name,
   object_name(st.objectid, st.dbid) as object_name,
   --st.number as stored_procedure_number, -- rarely used.
   --st.encrypted as query_text_is_encrypted, 
   st.text as query_text, -- Is NULL for encrypted objects.

-- Columns from sys.dm_exec_query_stats

   cte.*

FROM 
   cte 
      CROSS APPLY sys.dm_exec_sql_text(cte.sql_handle) st 

ORDER BY
   query_statement_average_worker_cpu_time DESC

SELECT
   DATEDIFF(hh,qs.creation_time,GETDATE()) AS age_in_hours,
   COUNT(*) AS count_at_this_age,
   CAST((100.0 * COUNT(*) / SUM(COUNT(*)) OVER ()) AS NUMERIC(4,1)) AS percentage_of_total,
   MAX(qs.last_execution_time) AS last_execution_time_at_this_age
FROM
   sys.dm_exec_query_stats qs 
GROUP BY
   DATEDIFF(hh,qs.creation_time,GETDATE())
ORDER BY
   age_in_hours