Strange behavior with ADO/Stored Procedure

  • I've been having a new problem recently that popups up once every couple of weeks and I haven't been able to figure it out, maybe someone else here knows.

    Occasionally when executing a stored procedure through ADO on a remote web server the SP will take up to 30 seconds to return the results - however if I run the same SP through query analyzer the results are returned in less than a second, and this is repeatable. At a glance you would think the problem is with ADO or the web server, but restarting the web server doesn't have any effect.

    The solution I've found is to actually open the stored procedure in Enterprise Manager and click the APPLY button to re-save it - immediately this fixes the problem and ADO is nice and fast like it's supposed to be.

    Any ideas on this one? It's happened maybe 5 times in the last 3 months and simply re-saving the SP fixes it every time. What the heck could be causing this?

    The SQL server is SQL 2000 on Windows 2000 with 4 GB of RAM, 4 Xeon CPU's, and plenty of disk space. It's not a resource issue.

  • How did you execute it in QA

    Did you manually set the parameters' value?

  • Yeah, I run the exact same line in QA as I do through ADO (using ASP). At first I thought the problem was with the web server (logical assumption) but since the "fix" is to simply re-save the stored procedure, it doesn't make any sense. And it always worked fine in QA even when it was running slow through ADO.

    Also I should point out that this exact same SP and server have been running for over a year unaltered, this problem just started recently without any updates or changes to the code.

  • run sp_updatestats on the server to update the statistics, then recompile the proc.

    If it happens again, post back on this board with the keywords parameter sniffing, and we'll be able to help you some more.

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

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