Avatar of FranklinRaj22
FranklinRaj22
 asked on

Multiple calls to same procedure

All we have 2 process invoking the same stored procedure at the same time and both of them are hung . They start yielding other resources but they never progress or complete .

Is this something to do with deadlock or resourcing locking , has anybody come across it or throw an insight of how the whole thing works ? Any solutions or suggestions ?

The sp uses cursors !!
DatabasesMicrosoft SQL ServerSQL

Avatar of undefined
Last Comment
FranklinRaj22

8/22/2022 - Mon
Guy Hengel [angelIII / a3]

indeed, removing cursors can help alot; but the programming needs to be made correctly.
without seeing the code, impossible to proceed, except that indeed this should like a "deadlock", which normally are detected, but with some complex scenario it might not be detected ...
it might also be some 3rd connection (like the one you use to query for the status/progress) that could add up on the blocking ...
OriNetworks

Cursors are evil in most cases. Its a little difficult to identify where you might be going wrong without seeing the code but you should read up on cursors, especially why in most cases they should not be used. Also you might be able to change your approach in how the sp is working with the data by possibly batching which is a technique used to work with large recordsets and allowing delays for other transactions to squeeze in. If one sp is run by itself does it actually finish and how long does it take. Do they use the same resources?
FranklinRaj22

ASKER
ok can i replace the cursor with temp tables that should help right ? or any other better suggestions ?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Pavel Celba

What says sp_who2 about the two frozen processes? Are they still running are they suspended? Does the procedure finish if you execute it just once?
Guy Hengel [angelIII / a3]

I wrote up this quick article to show the difference in coding with cursors and temp tables:
https://www.experts-exchange.com/Database/MS-SQL-Server/A_13640-processing-cursor-vs-temp-table-syntax.html
hope this finds you well
FranklinRaj22

ASKER
@pcelbha The sp_Who2 does not show any blocks or idle process .
Guy hengel . Thanks  but i am unable to open you link
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Pavel Celba

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
FranklinRaj22

ASKER
I was able to replace cursors with temp tables to good improvement .Thanks a lot .