June 24, 2012 at 12:50 am
I am facing a problem with some Stored Procedures that keep on running even after the execution time-out is reached.
The .Net application sets command timeout to 60 seconds for these SPs.
But when traced the SPs takes around 40 to 50 minutes non-stop! and completes execution.
If i run the SPs in SSMS, I noticed the SPs actually start fetching some rows before 60 sec and keeps on retrieving rows in batches and completes.
Note:
The SP's use TVFs joined to tables and many sub-queries.
My question is
1) How to Time-OUT a stored procedure if it reaches a certain time limit during execution?
2) Is there a way to automatically identify a process running certain SP's and kill it "if it consumes specific time"?
June 24, 2012 at 7:21 am
Is it an asynchronous query? The .net command timeout is ignored if the query is asynchronous.
You might consider looking at the Query Governor on your SQL Server, it might be able to handle some of this.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 24, 2012 at 8:30 am
The SP is used in the application for a certain report.
I haven't used Query Governor before, Can you provide more details?
If you are talking about the option "query_governor_cost_limit" then i think it may not help.
How do i set a proper value "query_governor_cost_limit"?
It is based on estimates and with different SP parameters the estimates will change!
Maybe i am wrong, you can enlighten me more on "Query Governor"
Thanks
June 24, 2012 at 7:24 pm
A much better thing to do, IMHO, is to find out why the sproc takes so long to run and fix it.
--Jeff Moden
June 25, 2012 at 12:38 am
Yes, the SP requires tuning and I will do that.
But in general i would like to know
Is there a way to set a limit for SP's Execution time?
Is there a way to abort an SP if it takes more than a certain limit?
FYI, The SP is taking such a long time because there is no LOCK time out limit and it is waiting to acquire a lock.
The application is like an ERP solution and has around 1500 concurrent users.
June 25, 2012 at 1:21 am
Hi
Please try to minimise the running time of the Procedure. If it begin to takes very less minutes, then you should directly call from the .NET code. Still it takes long times then create a job for the operation and invoke that from the .NET.
http://www.sqlusa.com/bestpractices2005/startjobfromstoredproc/
June 25, 2012 at 11:04 pm
I have tuned the SP and now it takes only 7 seconds to complete.
But guys i still want to know
How can I stop or limit an SP execution, if it takes more than a certain period to execute?
We use the execution time limit while making connection through .Net and it works fine for most of the SP's.
But some SP's keep executing beyond execution time limit.
Since they start fetching data before time limit and takes more than "execution time limit" to complete.
can someone provide me an answer?
June 26, 2012 at 12:13 am
Hi Mohammed Yousuf
Have you tried the CommandTimeOut? I am not sure is you want to setup the timeout from a Net client application, I believe you do based on this thread; The following MSDN article explains the SqlCommand.CommandTimeout Property
Personally, I had used it in the past with no dramas.
Cheers,
Hope this helps,
Rock from VbCity
June 26, 2012 at 12:24 am
Yes thats what we use "CommandTimeout".
For most of the SP's it works like a charm.
But as i mentioned in my earlier posts there are some exceptions - when SP starts fetching the data before "CommandTimeout" period.
June 26, 2012 at 2:03 am
Hi
Faced some like situations before, but realized that the timeout occurs when select tsql doesn't have nay response from server for a long time.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply