Sudden stored proc problem in 2000.

Question

Sudden stored proc problem in 2000.

Brendon Davies

Grasshopper

Points: 16
More actions
November 2, 2006 at 6:38 pm

#174833

Hi, not sure if anyone can help out, but here goes:
I have a stored proc on SQL 2000, which hasn't been touched for 2 years. If I run the proc from Query Analyzer, it's running fine. However, I also have an ASP page which has been calling the stored proc for the last 2 years fine.
Last night, we suddenly had a problem. When the stored proc gets called from the ASP page (on a seperate web server), the SQL Server box goes into spasms over the query. The end result from the ASP side is that it times-out. Yet I run the stored proc in Query Analyzer and it runs in 5 seconds flat, every time.
I've tried running the profiler, but I can't seem to see any problems there. My ASP page is designed to show any errors that occur, but all it states is that the query timed-out. The query does eventually finish on SQL Server when called this way, but it struggles and strains to finish.
Is it a statistics problem perhaps? But shouldn't the same problem occur under QA?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Will1922 SSCertifiable Points: 7189 More actions · Answer 1

Does the stored procedure have parameters? Are you running the stored procedure with the same parameters as the ASP page runs it?

Live to Throw
Throw to Live
Will Summers

debrucer1 SSCertifiable Points: 5366 More actions · Answer 2

You might try using an explain plan, first against your test environment, and then against your production site (or just production data). Explain plans modify themselves over time based on what the optimizer sees as the best plan - and this can really screw up performance.

Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982

Brendon Davies Grasshopper Points: 16 More actions · Answer 3

ok, have come in today to look at the problem in detail - except now there is NO problem. Query is called fine from the ASP page. Something very fishy here. I've not changed anything, yet my problem has mysteriously gone away.

Logically, I would say that this is the result of a data issue, or a corrupt index perhaps.

This system has previously run under SP3 fine, but we recently upgraded two weeks ago to SP4. However, this issue didn't occurr under testing, nor first days.