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: stackoverflow.com/questions/1873025/… . I think it will solve your problem.Fotis Grigorakis– Fotis Grigorakis2017-07-24 09:30:14 +00:00Commented Jul 24, 2017 at 9:30
-
@FotisGrigorakis this is not the answer the OP is looking for. Your answer talks about the cache (and clearing it).NickyvV– NickyvV2017-07-24 09:34:14 +00:00Commented Jul 24, 2017 at 9:34
-
Oooo yes, you are right sorry! My fault.Fotis Grigorakis– Fotis Grigorakis2017-07-24 09:35:49 +00:00Commented Jul 24, 2017 at 9:35
Add a comment
|
3 Answers
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.
Comments
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.
4 Comments
Prudhviraj kamineni
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?
NickyvV
@Prudhvirajkamineni did you work it out? Did I answer your question? Could you mark it so other users can find it?
Prudhviraj kamineni
hi nicky., ur answer work out really very well. thank u
NickyvV
@Prudhvirajkamineni that's great, could you mark it as an answer then?
I have a few advices for you but not all them fit for you.
- Reporting and CRUD operations must be separated. At least you can use
nolockor something or run them at night and can work offline. - Check your queries because if the data amount less then the 2 000 000, the main problem is queries for many time.
- Analyse the report types and if suitable for offline work, use offline system for reporting
- can use mirroring or other techniques for reporting.
- Best practice is always separate the databases for reporting and CRUD operations.
1 Comment
Belladonna
"NOLOCK Is Bad And You Probably Shouldn’t Use It. When you put NOLOCK in your query, SQL Server will: Read rows twice Skip rows altogether Show you changes that never actually got committed Let your query fail with an error" brentozar.com/archive/2021/11/…