I've got an odd problem that's cropped up twice now. I know how to fix it but not how to prevent it from happening again. We've got a .Net Webform using VB.Net 2003 that calls a stored procedure called usp_sp1 (names have been changed to protect the innocent). The stored procedure is in a SQL 2000 database on another server in the same room. When I call this stored procedure, it takes longer than 30 seconds and times out. I've got another web page that lets administrators run SQL code directly. When I type in usp_sp1 and run it in this other page, it times out after 30 seconds. When I open the stored procedure, copy all of the code, and run it in this other admin web page, the results come back in 5 seconds.
When I open Query Analyzer with the same SQL authenticated account that the web pages use, and call usp_sp1, the results come back in 5 seconds. At this point, we were pretty lost and could not figure out where the problem was coming from. DBAs pointed to .Net, .Net developers pointed to the database.
We created a new stored procedure called usp_sp2 and pasted in the contents from usp_sp1. When we call that stored procedure from the admin webform, it comes back in 5 seconds. In Query Analyzer it comes back in 5 seconds.
We renamed usp_sp1 to usp_sp1_bad, opened it and edited it's name within as well. We then renamed usp_sp2 to usp_sp1, opened it and edited it's name within. Now when we call the original webform, the results come back in 5 seconds. The ASP worker process was NOT restarted, the web server was NOT rebooted, essentially, nothing was changed on the web server.
We then decided to try and figure out what was wrong with usp_sp1_bad. We called it from the admin page and it came back in 5 seconds. Huh? When it was called usp_sp1 it timed out after 30 but now that it's called usp_sp1_bad, it comes back in 5 seconds. When we call the new usp_sp1 from the original webform, from the admin page, or from Query Analyzer, it also comes back in 5 seconds.
Basically, at this point, we're thinking that had we renamed usp_sp1 to usp_sp2 then renamed it back to usp_sp1 it probably would have fixed it as well.
The question though, is not how to fix it. The question is why the heck is this happening and how do we prevent it from happening again? This same stored procedure has done this twice in the last two months. Both times we fixed it the same way. This time we let it stay broken for at least a half an hour if not longer to try and troubleshoot what the heck is causing it and got nowhere. Nothing out of the ordinary is going on at the web server or database server when the problem occurs.
Anyone have any ideas or ever seen this happen before?
Thanks,
Check
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
You current on sql server service packs? We had similar problems, generally a select with a left join and lots of records. Sql server seemed to get a bad cached execution plan for the proc. The problem would go away by flushing the proc cache (dbcc), effectively what you accomplished with renamings. The caching is based on connection properties so it would only effect some clients - others would run fine.
Use SQL Profiler to see what is actually hitting the database and how long it takes. This will either prove or disprove the database as the bottleneck.
I would also use PerfMon and add the .NET counters for connection pooling. You may find that the connection pool is quite high which means that connections aren't being closed in the standard application.
I had a similar issue and the use of SQLProfiler and PerfMon pointed me to where the actual problem was. I would never have found out otherwise.
James :-)
mastoo: The caching is based on connection properties? So it could cache a bad execution plan with the connection information coming from .Net and use a different execution plan from Query Analyzer? If that's the case, then we may have our culprit.
Basically, this page worked fine for a month, nothing in the code changed, then one day, it timed out. We renamed it and it's back to normal. The whole time, it worked fine in QA.
Is there a command to manually flush the proc cache? I'm a .Net programmer and not a DBA but occasionally have to do some of the DBA tasks as well.
Thanks to the other responses as well. It wouldn't be so strange if it hadn't worked just fine for a month and then one day decide to time out. I guess it's possible that it worked when we put it out there and as data got added to the tables involved, the execution time got longer and longer until finally it timed out.
We could look up a reference probably but I'm pretty sure caching is based on the sql and the connection properties. It is common that you get different default connection properties in query analyzer vs. ado. My notes say DBCC FREEPROCCACHE would make the problem go away. I could bore you with all the workarounds we tried, but most important is that it seems to have stopped happening since SP4 for us. Much like your situation, we had a proc that routinely took < 1 sec to run and it would suddenly start timing out for the client app but continue working fine when we tried it from QA.
Business Accounts
Answer for Membership
by: slamhoundPosted on 2007-06-25 at 17:12:13ID: 19360200
A couple of thoughts...
The SQL server will learn from (cache) your queries so they run faster next time.
The original stored procedure got stuck in a loop or SQL had it locked out. Renaming (in effect, destroying and recreating) the stored procedure has either stopped the loop or freed the lock/resources.
There could be lag with the stored procedure being run over a network rather than on the same server. (but I'm guessing you've already figured on this one)
The ASP .Net code could be sending the stored procedure some strange variables to work with so you get bigger delays (or total failures). When you run the stored procedure manually you put some real variables in so the thing works fine.
Sorry that I've got no specific solution (without seeing your code in detail) but some of these should give you your next testing direction.