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