Query Execution Plan

  • rambo

    Right there with Babe

    Points: 739

    Dear Listers,

    Scenario:

    A stored procedure executed on SS7 SP2 completed in approx. 5mins.

    Following an upgrade to SP3 the same proc. completed in approx. 55mins. !! (Same server, same db, yada, yada, yada)

    i) Could the QEP have changed ?

    ii) Is there any means by which one can guarantee that a stored procedure uses the same QEP as generated by the QA ?

    If so - how can one tell ?

    iii) Could sp_recompile be of any benefit ?

    Thanks In Advance

    John

  • Andy Warren

    SSC Guru

    Points: 119655

    I dont recall any problems, but yes, the query plan could have changed. Recompiling is an excellent place to start. The only way to tell would be to save/print the query plan output and compare manually as as far as I know (a diff tool would help). Before you spend too much time on that, I would suggest running sp_updatestats first.

    Andy

  • zach_john

    SSCertifiable

    Points: 5922

    Running sp_updatestats is an excellent start! Do remember to due this off hours on a production database. Depending on the size of your tables, this process can be time consuming and resource intensive.

    Zach


    John Zacharkan

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714708

    Nothing should have changed, but it is possible that the query plans were lost or the stats were removed with the service pack.

    You might start with the sP. recompile it and see if that helps (or use sp_recompile). If not, then try updating stats for the tables used in the sproc. Be sure you sp_recompile the sproc after updating stats.

    Steve Jones

    steve@dkranch.net

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

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