Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
REDDY
Starting Member
43 Posts |
 Posted - 2003-04-28 : 12:07:48
|
| we have a strange problem with one of a "Stored Procedure(SP)" that is giving time out error at the SPS exec statement line inside a ASP page, and same SP running fine from Query Analyzer,connection between the web server and the Databaserver is OK bacause other SPS are working fine from webserver,There are no lock issues,indexes on the tables used inside the "Stored Procedure" are being recreated every week through maitainence plan,so I guess there should not be any missing statistics problem too.we temporarily solved this problem by recompiling the SP by "sp_recompile", but it works fine untill the week end but the same problem starts from Monday(next week) onwards,during the week end we have a index recreation job running on the Database server.I have observed that the excution plan before and after recompile of the SPS are same,The strange part of the problem I could not understand is why the SP works fine from QA and why not from webserver???we have sqlserver 2000 & windows 2000 with latest service packs.Any help is Greately appriciated...with regardsReddy |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-04-28 : 12:27:40
|
| Queries do not time out in Query Analyzer. Query Analyzer is setup by default to never time out. You can have the stored procedure automatically recompile each time it is called by modifying the stored procedure and adding WITH RECOMPILE. Please see SQL Server Book Online for the specifics on this.You could also change your time out settings on the web server. The problem is probably just that the stored procedure takes a long time to execute and the time out value is set too low. Query Analyzer has time outs set to 0, which means don't time out so that's why you don't get the same error there. What is your time out set to on the web server? You might want to try to figure out why your stored procedure takes so long to execute rather than just changing the time out value.Tara |
 |
|
|
REDDY
Starting Member
43 Posts |
Posted - 2003-04-28 : 13:25:48
|
| Tara The SP exec time was 3 secs in QA which is far bellow than the default script time out property of our web server(IIS) which is 90 secs.As you suggested "WITH RECOMPILE" is a good option, but we want to take the advantage of the procedure cache, for quick excution and as I mentioned earlier once I recompile SPS on monday it works fine untill the week end,some thing happening in the weekend,a job is running for regular rebuild of indexes on all the table.Thanks for taking timeReddy |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-04-28 : 17:13:38
|
| Try including a sp_recompile of the SP as part of your optimisation job - after updating the statistics.Sounds like something is going wrong with it's plan.Is the SP run over the w/end when an index it requires isn't there? Thaat would cause a plan which wouldn't be changed when the index is created.Just noticed it is ok from query analyser.A few optionsMaybe it is getting a cache hit from the match on the sql call from the web which linked to an invalid plan. The first sql call from qa generates a cache miss (slightly diffent sql) so finds the plan for the SP object id. The recompile clears both plans.Is the call from the app running in a transaction whereas the call from qa isn't?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.Edited by - nr on 04/28/2003 17:20:17 |
|
|
|
REDDY
Starting Member
43 Posts |
Posted - 2003-04-28 : 18:23:30
|
| I will include sp_recompile of the SP inside the optimization job. Temporarily it will solve my problem. Thanks for your adviceCould you please more elaborate on your comments "May be it is getting a cache hit from the match on the sql call from the web which linked to an invalid plan"This is interesting to know.... that the execution plans for the same SPS called from different places (QA & web) be different???? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-04-28 : 18:33:44
|
| They shouldn't be.The sql being executed is checked against the existing plans. If it is there (cache hit) it will resolve the plan for the SP and execute. If not it will resolve the object id for the sp, find an existing plan or create one.The cache hit above will depend on the sql being executed including spaces, parameters, capital letters ...This is a bit of a guess as to what is happenning by looking at the profiler so might be completely wrong.Be worth seeing ifdbcc dropcleanbuffersordbcc freeproccachehave any affect.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.Edited by - nr on 04/28/2003 18:42:09 |
|
|
|
|
|
|
|
|
Stored Procedure Recompile