How to identify the long-running queries in SQL Server database

 In the large SQL server database maximum time, we are stuck in the situation when database responding very slow when we check in the server end it shows the utilization of CPU is 95-98%, and when these things happen all processes running in the database starts taking so much time to execute.

To overcome this situation or to identify which query is taking time to execute the below query will help you to get SPID, when batch execution started and which query is taking time etc. so that you can go through that particular query to resolve the issue.

SELECT      r.start_time [Start Time],session_ID [SPID],

            DB_NAME(database_id) [Database],

            SUBSTRING(t.text,(r.statement_start_offset/2)+1,

            CASE WHEN statement_end_offset=-1 OR statement_end_offset=0

            THEN (DATALENGTH(t.Text)-r.statement_start_offset/2)+1

            ELSE (r.statement_end_offset-r.statement_start_offset)/2+1

            END) [Executing SQL],

            Status,command,wait_type,wait_time,wait_resource,

            last_wait_type

FROM        sys.dm_exec_requests r

OUTER APPLY sys.dm_exec_sql_text(sql_handle) t

WHERE       session_id != @@SPID -- don't show this SPID query

AND         session_id > 50 -- don't show system queries

ORDER BY    r.start_time;

What other ideas can you add to this post that I may have not mentioned?