How do I find long running queries in SQL server with process ID, process name , login time, user , start time and duration?

38.7K Asked by EdythFerrill in SQL Server , Asked on May 22, 2024

Can anyone help me to find a below given details for long running query. ProcessID, process name, database, host ,user ,process login time, query start time and query duration. How to find long running queries in sql server?I am looking for a query or an SP which gives me this data.

Answered by Caroline Brown

Their is a tool sp_whoIsActive by Adam Machanic used for several purpose like to see what is running at the moment you launch the script or you can run it in loops to monitor some specific action, as slow queries for example. To run in a loop take a look here: How to Log Activity Using sp_whoisactive in a Loop To detect slow queries: How to Use sp_WhoIsActive to Find Slow SQL Server Queries You can directly use DMV's to get your slowest queries and act from there. Check Glenn Berry's diagnostic queries. And finally you can use this query to find most time consuming queries. You can play around with the dm_exec_query_stats to add more data or join with other ones to get more information. Be aware that dmv's gets flushed away and refreshed each time the server is restarted.

      SELECT creation_time ,last_execution_time ,total_physical_reads ,total_logical_reads ,total_logical_writes , execution_count , total_worker_time , total_elapsed_time , total_elapsed_time / execution_count avg_elapsed_time ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st ORDER BY total_elapsed_time / execution_count DESC;

EDIT A new option is available from some time already, First Reponder Kit. Is a set of scripts, freely provided under MIT license by BrentOzar team, that will help on various tasks, including the one asked by the OP. Mainly sp_BlitzFirst and sp_BlitzWho scripts for this case.

Here are the steps to find the long running query in SQL Server

SELECT DISTINCT TOP 20.
est.TEXT AS QUERY ,
Db_name(dbid),
eqs.execution_count AS EXEC_CNT,
eqs.max_elapsed_time AS MAX_ELAPSED_TIME,
ISNULL (eqs.total_elapsed_time / NULLIF (eqs.execution_count,0), 0) AS AVG_ELAPSED_TIME,
eqs.creation_time AS CREATION_TIME,

Your Answer

Answer (1)

Ranjana

To find long-running queries in SQL Server along with process ID, process name, login time, user, start time, and duration, you can use the following query:

SELECT

    s.session_id AS 'Process ID',

    s.program_name AS 'Process Name',

    s.login_time AS 'Login Time',

    s.login_name AS 'User',

    r.start_time AS 'Start Time',

    DATEDIFF(MINUTE, r.start_time, GETDATE()) AS 'Duration (Minutes)'

FROM

    sys.dm_exec_sessions AS s

JOIN

    sys.dm_exec_requests AS r

ON

    s.session_id = r.session_id

WHERE

    r.status IN ('running', 'runnable')

ORDER BY

    r.start_time;

This query retrieves information about active sessions (sys.dm_exec_sessions) and their corresponding requests (sys.dm_exec_requests). It filters out requests that are currently running or runnable. The duration of the query is calculated by finding the difference in minutes between the start time of the request and the current time. Finally, the results are ordered by the start time of the request.

Using this query, you can identify long-running queries along with additional details such as process ID, process name, login time, user, start time, and duration.

7 Months