ADO Timeout/Query Analyzer is not
-
I have a stored procedure that will start timing out if run through our app (C++ Middle Tier using ADO), but when run in Query Analyzer it runs in under 2 seconds. Stop and Start SQL/Server and then the stored procedure runs in under two seconds in the app for a period of time (hours) and then starts timing out again. All the time it runs in Query Analyzer in under two seconds.
Obviously I can't keep stopping and starting SQL/Server everytime this happens. Any clues as to what's going on would be appreciated. I'm stumped!
-
I have had similar problems. In one instance, some of my permissions were off. In the others, I had to move away from teh SP and put that code into the app itself.
-
Thanks.
Here is some new info. It happened again this morning and instead of stopping and starting SQL/Server, I did an Alter on the stored proc to refresh it and it started working in the App again. This still is not a viable solution, but maybe sheds some more light as to what the actual problem is.
-
Are you running the stored procedure with the same arguments and the same login for both you application and query analyzer? Did you copy exactly what was returned from the profiler into query analyzer under the same account? Have you tried a tiny program that only invokes the stored procedure from a script or small application? Are any locks occuring at the same time? Are you or ADO using cursors to grab the data? The only reason query analyzer should give you faster results is that you are waiting on I/O from your application. There is probably more to your puzzle than you believe.
-
You mentioned before that the refresh of the stored procedure helped. Try setting your stored procedure to compile every time you run it.
-
Stored procedure is being run with the same arguments in both App and Query Analyzer. Although the App times out with any arguments. We have tried a test VB app that uses the same COM object running in COM+, it also times out. Will try a test script that calls the stored proc directly next time, thanks for the idea. Also, will try COM object running outside COM+. No cursors are involved.
-
We have done a couple of different things to work around this behavior. You can force the proc to recompile like:
ALTER PROCEDURE [usp_testRecompile]
@begin bigint,
@end bigint,
with recompile
as
set nocount on
SELECT column1, column2, ....
Or you can issue these 2 commands at anytime which will have the same effect:
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
-
Are you using a command object with parameters to address the storedproc ?
Is the commandtype "storedproc" ?
Are the parameters defined with _exactly_ the same db-column-type (datatype, length, precision, scale, ...) ?
Is your stored procedure being reused ? (master..syscacheobjects)
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
First when it is timing out have you tested QA at that moment?
If so what was the outcome?
altering and bouncing seem to fix temporarily so I believe the answer lies in you com object.
Would you be willing to send me the VB com object or the C++ object (of course changed for security, just need the code to see maybe what is going on)? Or post it here if all code can be posted easily, again remember to alter security info such as server, login name, and password.
-
Hi there,
Just observing this stream, because I had the same problem a couple of times and I have found no solution.
This also happens when there is no com envolved. When a Procedure is complex enough, the server can't decide on the correct execution plan anymore. This got to do with the statistics vs. the estimated cost per different execution plan. The server seems to get in a loop at one point trying to decide on the optimal plan.
This is partly the reason Microsoft gives, however, short from the mentioned solutions, I don't know of any solution.
For me what always helped was to declare the same procedure local variables and set those to the parameters and then use these local variables in the SP. This seems to force a recompile, but not completly
So, please if anybody got a good suggestion... post it please!!
Greetz, Robert.
-
We ran it via a VBScript just using ADO to connect to the database. Therefore eliminating the possibility of it being our COM object. It still timed out.
I noticed that when it is failing, ADO and Query Analyzer report different execution plans for the failing query in the stored proc. After recompile of the stored proc. ADO goes back to the execution plan that Query Analyzer is using and that it had been using up until it gets into its failed state.
So if anybody has an answer as to why all of the sudden ADO would start using an execution plan that is different than the one it has been using and is different to the one that when the stored proc is run via Query Analyzer it uses, let me know. Because I believe therein lies the solution to this problem.
One thing I might try is using index hints in the proc to always force the good execution plan and not let the optimizer choose the plan.
Also, the query that it times out on in the stored proc is not a complicated query.
-
I do have similar problem with complex SQL within SP. Did you check when this problem occured, is your execution plan same as when it behave ? I had discussed this with some senior engineer, they believed this may cause by the way Microsoft assemble the statistics. As most of you know, most of the time SQL only assemble the statistics using 10% of the 'sample' data. If somehow, if the data you populate is beyond their 'sample' algorithm, the SQL may actually pick the wrong query plan and hence cause the same SP slow down.
We come up with few ways, they all seems fix the problem,
1. Our solution is to put 'keep plan' option in the SP (using alter). It works for us.
2. run db-reindex on all the tables involved with the SP/SQL everyday. Which also seems resolve this issue.
-
The only solution that I could ever find was to move away from Stored Procedures and call the T-SQL code directly from my program code. I haven't noticed all that much of a slowdown- especially with ADO.NET. Otherwise, I have had nothing but problems with Stored Procedures, which is probably why some of the other big RDBMS' don't have a Stored Procedure per se, but an equivilant system in some cases.One thing that could be looked at, is the performance options of the SQL Server, I've noticed that if you allocate too much memory, or too much CPU resources to SQL, it causes very strange issues with both SQL and the OS. I've noticed this is also true if you use NT Fibers instead of threads, or boost SQL priority. I'm running a Dual Xeon 2.4 in Hyper-thread configuration, so I have a Quad processor server with 1024 MB RAM. I dedicate CPU 0 and CPU 2 to SQL, and only give SQL 512 MB RAM, and it seems to be running just fine, actually, it's faster than if I ramp it out, and I have had very little "restart" issues.Anyhew, that's just my 2 sense, hope it throws another point of view into the mix. I only request one thing, if you slam on me, back me up with either some good observation, or articles because I've noticed alot of poeple read a book, and think it's gospel.tibmeister
-
The only way of explaining why all of a sudden your proc is using an other execution plan is that is has been explicitly recompiled or that is has been thrown out of the procedure cache (because of least recent used) and later on used again and therefor had to be compiled again.
At compilation time statistics must have been so that the other access was prefered by sqlserver.
Before tweaking with hints, try to optimize your query !
- Are you telling sqlserver _everything_ you know about the query ?
- Try to explain why sqlserver chooses the "wrong" plan. (what's going on with the statistics ?)
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I've tried running some procedures from VB and from Query analyzer. No actions where taken on the database and no time (hardly any) was between both executions. Still the one from query analyzer finnished in normal times and the one from VB never finished and timed out. Actually this is probably linked to the fact that I've had it happen that running the query directly did finish in query analyzer and running the SP in query analyzer didn't..
It seems to be related to the execution plan and the one is chosen at run-time. However, this seems strange to me, since the execution-plan is bound to the SP and not to the calling-client software.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply