Post reply

Poorly performing SP

  • August 30, 2004 at 11:28 am

    #109001

    Hi,
     
    We have an application that is timing out due to a poorly performing stored procedure. However the poorly performing stored procedure is only poorly performing on a sporadic basis:
     
    The said prodcedure usually takes about 2 seconds to run. However, sometimes it decides to take about 30 seconds to run. The joined tables w/in the query are being updated frequently. However logical index fragmentation remains relatively low (under 5%). There are no blocking processes or deadlocks occuring in the DB.
     
    We have a nightly maintenance job that defrags the index and then manually updates statistics. We also have auto create / auto update statistics enabled on the DB.
     
    I have 2 questions:
     
    1) Why would this query take 2 seconds to run, then almost sporadically 30 seconds to run, then back to 2 seconds? Would this have anything to do with inserts / updates happening on joining tables before auto update statistics occurs (thus re-optimizing the execution plan)?
     
    2) In my maintenance plan that runs, I reindex only if logical scan fragmentation is greater than or equal to 5%. This is referenced via BOL. Should I also be checking for Scan Density as well? I notice some of my indexes have a Logical Scan Fragmentation around 5% but a Scan Density of well under 90%.
     
    Thank you.
    Chad

  • August 30, 2004 at 12:44 pm

    #521101

    Chad,

    Everything you described is accurate. Your only option, given the supplied information, is to have your procedure perform a "dirty read". I think all the performance items you have added are not costing you time, in their up keep. But rather your query is waiting for a chance to take a snapshot of the data. You are the only one that can determine if a dirty read is acceptable for your users.

    Ed

  • August 30, 2004 at 12:57 pm

    #521103

    I agree that a dirty read would might be a good idea, given the users are cool with not having potentially new data added after the select statement has begun. If my select statement is "waiting" for a process to release, would this be a lock wait, an actual blocking process, or something totally different?
     
    I appreciate the response.
    Chad

  • August 30, 2004 at 1:27 pm

    #521109

    It would be a wait due to a lock and the SPID would never be blocked.

    What happens internally is SQL Server starts a snapshot read. If the data changes before it completes the read, it starts over.

    To resolve, take a look at the locks when it is occurring. You want to ensure there are no Table locks and a minimum of page locks.

    Ed

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

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