Recently we experienced issue with DB performance. We found that some stored procedures and queries were running for more than 24 hours.
Any way to monitor this situation. Means, how can I monitor SQL queries which are running for a long time.
If this is MSSQL, the following query should work to show long running queries.
SELECT TOP 100 qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds, qs.total_elapsed_time / 1000000.0 AS total_seconds, 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 qsCROSS APPLYsys.dm_exec_sql_text(qs.sql_handle) as qtLEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_idWHERE qt.dbid = DB_ID()ORDER BY average_seconds DESC;