Some problem for Stored Procedure

Question

Some problem for Stored Procedure

only4mithunc

SSCrazy Eights

Points: 8434
More actions
April 18, 2010 at 11:49 am

#138489

Hi Friends
I had one SP which was taking almost 7 minutes to complete the execution. But now I could seen that when I tried to execute the sp it is still executing even after 20 minutes!!! Then I just tried to execute it as a script rather than an SP , the script got completed the execution by the same 7 minutes.
What could be the reason that the SP getting hanged..?
Regards,
MC
Thanks & Regards,
MC

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

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Please, please help those who want to help you by posting your problem following the second link in my signature block,for what will help you get a good answer

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 2

Hi

Ron thanks for reply and sorry for not following the style of posting which you mentioned.

In my case as I told when I executed it as a script it completed the execution as expected and when I dropped and recreated the sp , it got completed execution by 7 minutes as expected.

So I thought it is nothing to do with tuning, and the reason for why the SP took a long time is something else which I dontknow...

Regards,

MC

Thanks & Regards,
MC

Grant Fritchey SSC Guru Points: 398913 More actions · Answer 3

There could be any number of reasons and without more detail, it will be very difficult to guess.

The first two guesses that come to mind are either parameter sniffing or resource contention. But I couldn't be sure either way without a lot more detail. What does the query plan look like when the query runs long? Is it different than when it runs quickly? Have you checked blocking processes on the server while running the query?

That's just the start of the kind of detail that would be needed to provide you with a detailed answer.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 4

Thanks for the reply.

Unfortunately I didn't check these two things... And as I mentioned, when I dropped and recreated the SP it was fine, so no chance to replicate it.. 🙁

Regards,

MC

Thanks & Regards,
MC

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

only4mithunc (4/19/2010)
Thanks for the reply.
Unfortunately I didn't check these two things... And as I mentioned, when I dropped and recreated the SP it was fine, so no chance to replicate it.. 🙁
Regards,
MC

That would certainly cause a "recompile" of the proc which will temporarily solve a "parameter sniffing" problem.

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

Grant Fritchey SSC Guru Points: 398913 More actions · Answer 6

Jeff Moden (4/19/2010)
only4mithunc (4/19/2010)
Thanks for the reply.
Unfortunately I didn't check these two things... And as I mentioned, when I dropped and recreated the SP it was fine, so no chance to replicate it.. 🙁
Regards,
MC
That would certainly cause a "recompile" of the proc which will temporarily solve a "parameter sniffing" problem.

Exactly. You might see this problem reappear occasionally.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning