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?

You should take a look at this question here: https://stackoverflow.com/questions/1873025/how-can-i-clear-the-sql-server-query-cache . I think it will solve your problem. — Fotis Grigorakis, Jul 24 '17 at 09:30
@FotisGrigorakis this is not the answer the OP is looking for. Your answer talks about the cache (and clearing it). — NickyvV, Jul 24 '17 at 09:34

score 17 · Answer 1 · answered Dec 11 '17 at 10:11

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.

NickyvV · Accepted Answer · 2017-07-25T11:17:47.023

3

I always use sp_WhoIsActive from Adam Machanic for finding long running queries. sp_WhoIsActive is described in detail on dba.stackexchange.com.

Although you can also write your own script or use sp_who2 for example.

Update
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.
Have a look over here for a detailed explanation of the stored procedure.

edited Jul 25 '17 at 11:17

answered Jul 24 '17 at 09:37

NickyvV

1,720
2
16
18

I am new to the SQL., so., after sp_whoisactive or sp_who2 how could we know what is long running and how to kill it? – Prudhviraj kamineni Jul 25 '17 at 10:19
@Prudhvirajkamineni did you work it out? Did I answer your question? Could you mark it so other users can find it? – NickyvV Jul 26 '17 at 13:52
hi nicky., ur answer work out really very well. thank u – Prudhviraj kamineni Jul 27 '17 at 12:26
@Prudhvirajkamineni that's great, could you mark it as an answer then? – NickyvV Jul 27 '17 at 14:59

score 1 · Answer 3 · answered Jul 24 '17 at 12:03

I have a few advices for you but not all them fit for you.

1- Reporting and CRUD operations must be sparated. At least you can use nolock or something or run them at night and can work offline. 2 - Check your queries because if the data amount less then the 2 000 000, the main problem is queries for many time. 3- Analyse the report types and if suitable for offline work, use offline system for reporting 4- can use mirroring or other techniques for reporting. 5- Best practise is always sparate the databases for reporting and CRUD operations.

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

3 Answers3