Longest running queries/processes in SQL.
-
Hi Guys,
I need a script to identify the longest running queries/processes in SQL.
Thanks in advance
-
There are plenty of examples online. Just Googling for the phrase you posted "Find the longest running queries/processes in SQL" yielded lots of relevant results.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Hi,
Please refer the below link.
http://blog.sqlauthority.com/2009/01/02/sql-server-2008-2005-find-longest-running-query-tsql/
-
Here's a similar one to the one linked directly above, but that also brings in the showplan XML. A little "heavier" query, but very useful if used sparingly.
SELECT TOP 200
sql.text as sql
, qp.query_plan
, qs.plan_handle
, creation_time
, last_execution_time
, execution_count
, (total_worker_time / execution_count) as avg_cpu_usec
, total_worker_time as total_cpu_usec
, (total_elapsed_time/execution_count) as avg_duration_usec
, last_worker_time as last_cpu_usec
, min_worker_time as min_cpu_usec
, max_worker_time as max_cpu_usec
, (total_physical_reads + total_logical_reads) as total_reads
, (max_physical_reads + max_logical_reads) as max_reads
, (total_physical_reads + total_logical_reads) / execution_count as avg_reads
, max_elapsed_time as max_duration_usec
, total_elapsed_time as total_duration_usec
, sql.dbid
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) sql
CROSS APPLY sys.dm_exec_query_plan (plan_handle) qp
where isnull(sql.dbid,0) <> 32767 and sql.text not like 'select top%sql.text%'
ORDER BY qs. total_worker_time desc, avg_duration_usec DESC
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply