Most Expensive Requests
A script to get the 50 most expensive requests since the last service restart. This needs SQL Server 2005 or later.Used as is, it returns the top 50 in terms of total logical reads. The ORDER BY clause can easily be modified to look at top users of CPU or total elapsed time.
select top 50
db_name(qp.dbid) dbname, st.text,
substring(st.text, qs.statement_start_offset/2 + 1,
(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 + 1) stmt_text,
qs.last_execution_time, qs.execution_count,
qs.total_worker_time, qs.last_worker_time,
qs.min_worker_time, qs.max_worker_time,
qs.total_worker_time / qs.execution_count avg_worker_time,
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_logical_reads / qs.execution_count avg_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.min_logical_writes, qs.max_logical_writes,
qs.total_logical_writes / qs.execution_count avg_logical_writes,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.total_elapsed_time / qs.execution_count avg_elapsed_time,
qs.total_clr_time, qs.last_clr_time,
qs.min_clr_time, qs.max_clr_time,
qs.total_clr_time / qs.execution_count avg_clr_time
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_query_plan(qs.plan_handle) qp
cross apply sys.dm_exec_sql_text(qs.sql_handle) st
order by qs.total_logical_reads desc
db_name(qp.dbid) dbname, st.text,
substring(st.text, qs.statement_start_offset/2 + 1,
(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 + 1) stmt_text,
qs.last_execution_time, qs.execution_count,
qs.total_worker_time, qs.last_worker_time,
qs.min_worker_time, qs.max_worker_time,
qs.total_worker_time / qs.execution_count avg_worker_time,
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_logical_reads / qs.execution_count avg_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.min_logical_writes, qs.max_logical_writes,
qs.total_logical_writes / qs.execution_count avg_logical_writes,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.total_elapsed_time / qs.execution_count avg_elapsed_time,
qs.total_clr_time, qs.last_clr_time,
qs.min_clr_time, qs.max_clr_time,
qs.total_clr_time / qs.execution_count avg_clr_time
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_query_plan(qs.plan_handle) qp
cross apply sys.dm_exec_sql_text(qs.sql_handle) st
order by qs.total_logical_reads desc
Not found what you're looking for?
Use this search box. It is tuned for SQL Server searches. Try it and see!Do you have a question about SQL Server? Would you like to answer a question?
Go to the SQL FAQ to get started.
