Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2005
    Posts
    29

    Question Unanswered: Query call by app runs long, same query in QA doesn't.

    Hi Guys,

    I've got a stored proc used for order generation which runs long sometimes when called from within our app. A normal run will complete within 20s, a long run will get terminated by the app at the 6 minute mark.

    When it runs long once, repeated attempts will also do so until I execute the same query the app did, but from within Query Analyzer. At which time the problem will disappear for a day or two. The app connects to the SQL Server 200 SP4 database using ADO.

    I suspected statistics might be at fault here but have tried both "UPDATE STATISTICS table WITH FULLSCAN" and "DBCC DBREINDEX('table') to no avail. This issue has occurred and been worked around in this manner a few dozen times.

    Any idea what might be going on here?

  2. #2
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    Hi there

    I had a similar situation yesterday where a complicated stored procedure would execute in 4 or 5 mins in QA but when it was executed in a job the procedure would take at least an hour.

    The fix for this was to add SET NOCOUNT ON at the beggining of the procedure, I am not saying this is definitely your problem but certainly worth looking into

  3. #3
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by karlmdv
    Hi Guys,

    I've got a stored proc used for order generation which runs long sometimes when called from within our app. A normal run will complete within 20s, a long run will get terminated by the app at the 6 minute mark.

    When it runs long once, repeated attempts will also do so until I execute the same query the app did, but from within Query Analyzer. At which time the problem will disappear for a day or two. The app connects to the SQL Server 200 SP4 database using ADO.

    I suspected statistics might be at fault here but have tried both "UPDATE STATISTICS table WITH FULLSCAN" and "DBCC DBREINDEX('table') to no avail. This issue has occurred and been worked around in this manner a few dozen times.

    Any idea what might be going on here?
    Try index hints, nolock hints, etc.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The index hints may be a bad idea, especially if the data either grows or changes. Unless you want to go back once in a while to make sure that the index hint is still faster.

    Sounds like a bad plan is being generated. Next time it happens, run sp_recompile on some of the tables the procedure is based on.

  5. #5
    Join Date
    Jan 2005
    Posts
    29
    Thanks for all the suggestions.

    SET NOCOUNT ON -- Since I'm not using the affected rows counts I'll give this a try. Although if it helps, I'll be curious as to why

    I was hoping to find that it was a bad plan causing the procedure to take so long. My problem is that when I try and _get_ the plan the db is using the problem goes away.

    *A few hours pass*

    I've found some pertinent info and think I'm closer to a solution
    http://www.thescripts.com/forum/thread486412.html

    Seems the reason that I see different results in ADO as from QA is that the default ARITHABORT setting between the two differs and as such will get different execution plans. So when I SET ARITHABORT OFF in QA it uses the same long-running plan that the app was seeing.

    I see that running DBCC FREEPROCCACHE 'fixes' the problem (I assume it will only be gone a day or so) so I think that means the problem is definitely execution plan related.

    I think the next step is to get textual execution plans for both slow and normal so seeing the differences becomes easy. Now to wait for the issue to crop up again.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by karlmdv
    SET NOCOUNT ON -- Since I'm not using the affected rows counts I'll give this a try. Although if it helps, I'll be curious as to why
    This is standard good practice. I would bet that the reason this helped the OP is that it would have forced a recompile of the plan. I would wager it was the recompile rather than NOCOUNT that was the measurable difference.

    Quote Originally Posted by karlmdv
    I see that running DBCC FREEPROCCACHE 'fixes' the problem (I assume it will only be gone a day or so) so I think that means the problem is definitely execution plan related.
    Don't use this on a prod server - it clears out all procedure plans. Use sp_recompile on the offending procedure, or execute it with the recompile option.

    I haven't read your link (no time) but check out parameter sniffing in google - there are techniques to get around it.

    What version of SQL Server BTW? 2005 has some new plan management options.

  7. #7
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    Hiya

    It was nothing to do with recompiles, because we tested over and over again and it was always the same, job runs for 1hr but QA was 5 mins.
    Setting NO COUNT ON fixed the problem instantly.

    There is a KB article here detailing the same problem and fix

    http://support.microsoft.com/kb/q249730/

    As I said before it might not be the OP's problem

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I guess I lost my bet

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •