October 15, 2001 at 12:19 am
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
October 15, 2001 at 4:56 am
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
October 15, 2001 at 2:39 pm
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
October 15, 2001 at 2:47 pm
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply