July 21, 2008 at 3:57 pm
We have an ASP.NET web application that queries our SQL Server 2000 database, and one action in the web app is throwing database time-out errors. There are several database actions that take place from the one web app action, but there is one primary stored procedure which consumes the most time. It normally runs to completion successfully in 4-6 seconds. All seems to function fine for a while, and then we start getting timeouts. This happened last Friday and again today (Monday). There does not appear to be a significant change in work load on the server at these times, and in fact, we have two other instances of this web app and database (identical code) running on the same servers which continue to operate fine while the one instance fails, so this suggests to me that it must be database or web site specific rather than server or network specific.
The "fix" that we stumbled upon is a bit strange to me. We have done a significant overhaul of this one key procedure for our next version. If we deploy it (drop the old / create the new) and then re-deploy the original (another drop / create script) then the time-outs disappear. This led me to consider something with the procedure cache, but when the problem recurred today, we issued a DBCC FREEPROCCACHE statement to no avail. In fact, we also tried just using the original script to drop the procedure and recreate it identically (without using the new code) and that did not seem to make any difference. Perhaps this pseudo-upgrade approach is a red-herring, but it has "worked" both times now. But, as I mentioned, the problem came back, and I fear it will return again soon.
So... Any ideas of possible causes or better fixes?
SQL Server details:
- Windows 2003, Service Pack 1
- SQL Server 2000, Service Pack 3a
- Quad 3.3 GHz Pentium 4 (Hyper-Threaded)
- 8 GB RAM
July 21, 2008 at 4:53 pm
Try executing the stored procedure after re-creating it with WITH RECOMPILE option.
Manu
July 22, 2008 at 2:11 pm
But Manu, I don't want it to recompile the procedure EVERY time it runs. This is a pretty core procedure that is called numerous times.
And when I ran DBCC FREEPROCCACHE, wouldn't that have caused a recompile? Or at least it should have purged the execution plan from cache. And certainly when I dropped and recreated it, it would have recompiled, no?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply