September 14, 2004 at 3:39 pm
I have a very strange scenerio in one of my production site.
There is a stored procedure which keeps on running foreover. I took the code of the procedure and ran it separatly with no changes except setting the arguments manually and it returned my result in 4 seconds. Can anybody guess or point me where the problem is?
FYI. I already tried clearing procedure cache using DBCC FREEPROCCACHE
Thanks
Gopal
September 15, 2004 at 1:35 am
Check for locks.
It may be that you stored procedure requires a lock on an item that is already locked.
September 15, 2004 at 7:41 am
There is no process blocking.
Locks are mostly based on query inside stored procedure and I already pulled whole of the query stuff inside SP as it is, just I needed to set the argument variables with desired values which is also exactly same as run directly. And query returned in 4 seconds. I am not sure how can there be locks in such situation. But I will certainly look into trace dump from the site.
I suspect there could be some SQL server defect.
Gopal
September 15, 2004 at 8:09 am
Any chance we could see the Stored Procedure code???
Sam
September 15, 2004 at 8:51 am
Gopal,
If you don't want to post the sp, even the nature of it might help.
Like, is it accessing a linked server? Is it executing an extended stored procedure? Is it inserting based on an exec statement (i.e. insert into table exec storedprocedure)? Is it creating temp tables? Is it accessing temp tables? I have seen stored procs hang under varying conditions as mentioned above, but without knowing what the proc is doing, I see no way to help troubleshoot it.
Bill
September 15, 2004 at 11:35 am
Re: "keeps on running forever".
Is it executing code, or is it "stuck"? I recall that looking at the spid would tell me if the process was using CPU cycles or not.
Bob Monahon
September 15, 2004 at 1:35 pm
Query Analyzer may have different settings for QUOTED_IDENTIFIER and ANSI_NULLS that what the stored procedure is defined to use. Generate the SQL for the SP so that you can see the SET statements for those values and copy/paste those into Query Analyzer as well and see if it still runs in 4 seconds. If it doesn't then you can generate the ALTER SQL for the SP from Query Analyzer, toggle the offending setting, and apply (F5) the change to the SP. It should then run fine. Hopefully this is all that your problem is.
If not, try recording the execution plan as both an EXEC of the SP and the inlined SQL and compare them to see where they are different. A text based version vs graphical may be best so that you can save and compare the results using a file comparison utility.
Also, if you are plugging the SP parameters into your query directly instead of DECLAREing the variables and SETing them a more optimized plan will be used. Make sure you haven't turned a variable where clause into a constant one.
September 16, 2004 at 2:01 am
Of course, if MSSQLSERVER runs under a domain account you should be able to use the debugger within QA.
Open up the object browser in QA, right click on the proc and select debug from the bottom.
September 16, 2004 at 9:18 am
FYI. News for you guys the problem has gone away by itself. Our database adminstrators could not find any locks during the time when problem was happening.
It is hard to reproduce this problem. To answer few of the posted replies. This stored procedure psedo code would be like,...
if @isdyn = true
Begin
big select clause with uses of tables T1,T2,T3,T4. Lots of self joins and sub queries.
End
ELSE
Begin
big select clause with uses of tables T1,T2,T3,T5 Lots of self joins and sub queries.
End
I appreciate you guys time and various input for digging into this issue. I don't have answer yet.
Thank
Gopal
September 16, 2004 at 9:46 am
Problems don't just vanish. I suspect that a.n.other process is the true cause of the problem this will come back to haunt you.
I would build up a string message in the proc that details what parameters have been passed, what variables have been set etc and write that string to the server event log using RAISERROR and the WITH LOG option.
This will at least validate what has gone into the proc, and short of debugging, what is going on within the proc.
Has any of the underlying tables had a structure change in the recent past? If so it may be worth running an sp_recompile on the tables to ensure that any dependent views/procs take these changes into account.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply