SQL 2005 - Long duration Stored Procedure

  • I use a Stored Procedure (with parameter) which uses 3/4 joins to retrieve some fields.i.e., a select from 3/4 tables. The problem is whenever the SP is executed for a particular input for the first time, it takes 5 to 6 mins to retrieve records(1 to 100). But the same SP for the same parameter after that completes in a shorter duration. If the parameter is changed, then it takes similarly long time, but the second execution for the same parameter in shorter duration.

    Execution plan does not have any table scan.

    There are indexes present in all the tables.

    recompiling SP, Freeing cache does not have desired effect.

    SQL 2005

    Any recommendations?

  • balasach82 (3/19/2010)


    I use a Stored Procedure (with parameter) which uses 3/4 joins to retrieve some fields.i.e., a select from 3/4 tables. The problem is whenever the SP is executed for a particular input for the first time, it takes 5 to 6 mins to retrieve records(1 to 100). But the same SP for the same parameter after that completes in a shorter duration. If the parameter is changed, then it takes similarly long time, but the second execution for the same parameter in shorter duration.

    Execution plan does not have any table scan.

    There are indexes present in all the tables.

    recompiling SP, Freeing cache does not have desired effect.

    SQL 2005

    Any recommendations?

    Freeing cache will make it slower on the next run as well.

    Do you have appropriate indexes?

    Have you checked for parameter sniffing?

    how do the statistics appear for your tables?

    How is the index fragmentation level?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

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

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