How to find current long running queries in SQL Server and how to kill them instantly?

Question

Sometimes my application runs slow. The major problem is that some expensive reports are running. How can I find these reports and how to kill these instantly?

Answer 1

You can use the following command to get the long running queries.

SELECT r.session_id,
       st.TEXT AS batch_text,
       qp.query_plan AS 'XML Plan',
       r.start_time,
       r.status,
       r.total_elapsed_time
FROM sys.dm_exec_requests AS r
     CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
     CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
WHERE DB_NAME(r.database_id) = '{db_name}'
ORDER BY cpu_time DESC;

Then you can use

KILL 60 

to kill session_id 60 for example.