Post reply

Query that normally runs in Less than a second. Has been running long

  • July 21, 2009 at 1:34 pm

    #212465

    I have a stored procedure that normally will run in less than a second. I have noticed recently that it is taking longer and longer to run. the only way i have found to fix it is to reindex the underlying tables almost on a daily basis. While the statistics don't show that the indexes are defragmented it still seems to fix the problem. Is there something else that would make it look like this fixes the problem but that's not really it? Any insight would help.

  • July 21, 2009 at 2:38 pm

    #1027925

    it's statistics that is killing your query.

    as statistics get more and more out of date, the saved execution plan takes longer and longer, as the original plan is inaccurate.

    reindexing also rebuilds your statistics; and if you have auto update statistics turned on, that does not occur until 20% of the rows in the table has changed...on a big table, that might be a long time.

    add a job to update your statistics.

    Gail has some great detailed info on her blog about statistics if you want a more detailed explanation:http://sqlinthewild.co.za/

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 21, 2009 at 5:25 pm

    #1028018

    Perhaps it's also time to do a couple of other things...

    1. Re-evaluate what the clustered index is.

    2. Optimize the code to be a bit more bullet proof.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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