one of my stored proc is getting stuck in activity monitor for 2 hours.

Question

Post reply

one of my stored proc is getting stuck in activity monitor for 2 hours.

rajemessage 14195

Hall of Fame

Points: 3299
More actions
November 27, 2016 at 1:52 am

#332338

hi,
one of my spid is getting stuck for two hours or 15 min or 1 hours etc, and showing up in "activity monitor" or "top trasction by age report" and in
SELECT d.name, sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time,req.*
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
LEFT JOIN sys.databases d on d.database_id = sqltext.dbid
i known the stored procedures name WHICH IS GEtting stuck by using the above query, i used dbcc opentran to find if the transactionl is
still running because , this stored proc uses transaction but there were no transaction open. we found on query shown by "top transaction by age" of the above mentioned store proce. used dbcc inputbuffer also.
but strange thing is after killing the spid , when i run the same stored proc it runs nicelly , only thing i could see is, that it is very big. so how can i find what is the problem is.
yorus sincerly

Viewing 4 posts - 1 through 3 (of 3 total)

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

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 1

Quick question, what is last_wait_type in sys.dm_exec_requests showing for the spid?

😎

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 2

rajemessage 14195 (11/27/2016)
but strange thing is after killing the spid , when i run the same stored proc it runs nicelly , only thing i could see is, that it is very big. so how can i find what is the problem is.

That's usually a symptom of one of two things... bad parameter sniffing or "out of date" statistics.

Could also be some performance challenged code that requires unnecessary resources if the fastest the code ever runs is 15 minutes.

Since it appears to be a batch job, you might try adding OPTION(RECOMPILE) to the offending part of the proc as a short term "patch" while you figure out a faster, less resource intensive code.

--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)

Ed Wagner SSC Guru Points: 287024 More actions · Answer 3

I think Jeff's got the right idea in adding OPTION (RECOMPILE) as a short-term fix. In fixing the problem, you should look for what's calling it, as well as finding out why it's taking so long. If it's just a SELECT statement and isn't writing the data anywhere, then you have to ask yourself if there's a person running it.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions