use master

go

select top 10 substring(qt.text,(qs.statement_start_offset/2)+1,

((case qs.statement_end_offset

when -1 then datalength(qt.text)

else qs.statement_end_offset

end -qs.statement_start_offset)/2)+1)

,database_name=db.name

,qs.execution_count

,qs.total_logical_reads

,logical_reads_per_run=cast(qs.total_logical_reads*1.0/qs.execution_count as decimal(12,2))

,qs.last_logical_reads

,qs.total_logical_writes

,qs.last_logical_writes

,qs.total_worker_time

,qs.last_worker_time

,total_elapsed_time_in_s=qs.total_elapsed_time*1.0/1000000

,last_elapsed_time_in_s=qs.last_elapsed_time*1.0/1000000

,qs.last_execution_time

,qp.query_plan

from sys.dm_exec_query_stats qs

cross apply sys.dm_exec_sql_text(qs.sql_handle) qt

cross apply sys.dm_exec_query_plan(qs.plan_handle) qp

inner join sys.databases as db

on qp.dbid=db.database_id

--where qs.execution_count>=100

--order by qs.total_logical_reads/qs.execution_count desc --logical reads per run

--order by qs.total_logical_writes/qs.execution_count desc --logical writes per run

--order by qs.total_worker_time/qs.execution_count desc --cpu time per run

--order by qs.total_logical_reads desc --logical reads

--order by qs.total_logical_writes desc --logical writes

order by qs.total_worker_time desc --cpu time