CODEWITHSUNDEEP
sql-server
Prudhviraj kamineni
Prudhviraj kamineni

Reputation: 191

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

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?

Upvotes: 19

Views: 152879

Answers (3)

Dogan
Dogan

Reputation: 106

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

  1. Reporting and CRUD operations must be separated. 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 practice is always separate the databases for reporting and CRUD operations.

Upvotes: 1

Sonu K
Sonu K

Reputation: 2802

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.

Upvotes: 29

NickyvV
NickyvV

Reputation: 1746

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.

Upvotes: 6

Related Questions