Long running queries during specific time interval in a specific database

SELECT TOP 10
qs.total_elapsed_time / qs.execution_count / 60000000.0 AS average_minutes,
qs.total_elapsed_time / 60000000.0 AS total_minutes,
qs.execution_count,
SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END – qs.statement_start_offset)/2) AS individual_query,
o.name AS object_name,
DB_NAME(qt.dbid) AS database_name
FROM
sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
WHERE
qt.dbid = DB_ID()
and last_execution_time>’2013-05-04 15:12:50’and
last_execution_time< ‘2013-05-05 02:22:40’
ORDER BY
total_minutes DESC

Advertisements