How to write query get long process job or query or stored procedure running on server ?

Question

I work on sql server 2014
How to write query get long process job or query or stored procedure running on server ?

as example suppose i run
exec sp_joblong
how to know this procedure running now
and which place it stop and take long time
and how to know it finish execution on server

Answer 1

Hi @ahmed salah ,

You can use sp_WhoIsActive from Adam Machanic for finding long running queries. You are interested in the first 2 columns of the output of sp_WhoIsActive. The first column defines how long the query is running. The second column is the session_id (or SPID) of the query. You can use KILL 60 to kill session_id 60 for example.

Or you can try below T-SQL to find current long running queries in SQL Server;

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;  

Refer to this similar thread to get detail information.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".