Long running report- kill the session

Question

Long running report- kill the session

khushbu

SSCarpal Tunnel

Points: 4591
More actions
February 10, 2021 at 8:14 am

#3843266

There are few reports in our environment which run on read only replica, but they take over 3+hrs.
We have decided that any report that runs for more than 2 hrs should be killed and rolledback.
How do we identify that that report was killed (session was killed) due to this reason?
Should I write another query/service to poll system every 15 min or so to check such cases or is there any good way.
Can I add in the SP some method that can tell me the Sp was killed due to this reason? what can it be?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ken McKelvey SSCoach Points: 18856 More actions · Answer 1

Reports should not take this long to run so the real answer is to tune the report queries.

I would start by putting all the SQL into stored procedure(s) and only call the procedures from the reports. You can then start improving each procedure.

khushbu SSCarpal Tunnel Points: 4591 More actions · Answer 2

yea we are on it for improving /re designing.

Until then is there a way to identify the ask?

Ken McKelvey SSCoach Points: 18856 More actions · Answer 3

The session information is in sys.dm_exec_sessions. eg:

SELECT session_id, login_time, [host_name], [program_name], client_interface_name, DB_NAME(database_id)
,login_name, original_login_name
,last_request_start_time, last_request_end_time
FROM sys.dm_exec_sessions;

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 4

Are these processes that are taking more than 3 hours causing problems? If not, then I would not be looking to kill the process and force a rollback - instead I would be looking at identifying them so they can be fixed.

If they are causing issues - instead of killing them, maybe look at implement resource governor and blocking them from running at all.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

khushbu wrote:

yea we are on it for improving /re designing.
Until then is there a way to identify the ask?

You will never fix it if you do such a thing. It shouldn't take that long to do. And at least call the person that's running the report to let them know BEFORE you kill it. The job you save may be your own.

This type of thing is what gives IT Departments and DBAs a bad name.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mike Good SSCertifiable Points: 7415 More actions · Answer 6

I agree with the other comments. But if you want to do this in the short term you can add to your "check & kill" sp a raiserror or xp_logevent call to put a message in the sql errorlog saying that SPID NN was killed because it has been running for > 2hrs. Then the sp issues your kill stmt. Or instead of using the errorlog, you could create your own user table and store the information there, by inserting to it from your "check & kill" sp.