29

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.

I am looking for a query or an SP which gives me this data.

0

2 Answers 2

43

As commented, best tool around is sp_whoIsActive by Adam Machanic. It can be used in several ways, 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.

1
  • How to add LIMIT for this query
    – jeevu94
    Dec 22, 2022 at 12:57
11

I'd highly recommend sp_whoisactive. It's a third-party SP, but can tell you everything you listed above, and much more than that. It's an incredible tool to have in your arsenal.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.