January 31, 2005 at 1:53 pm
I know this has been brought up many times but I am not finding anything fast...
We upgraded from SQL7 to SQL2K sp3a this past weekend and all went mostly well.
The problem is a small number of our stored procs are runnining super long.
A stored proc that was running around 35 seconds has now been running between 5 - 11 minutes.
I ran sp_updatestats on the database and I am running the Optimize portion of our DB Maint Plan tonite which I assume does a DBREINDEX on all the indexes in the database.
Is there anything I should be looking at?
Thank you very much.
RandyB
February 1, 2005 at 6:33 am
I would also force a recompile on all of your stored procs, it's possible that the execution does not match what it would have done in sql7.
February 1, 2005 at 9:42 am
Thanks Nicolas.
I tried that on your advice but it didn't help.
February 1, 2005 at 9:47 am
Have you run execution plans for the procs that are causing the problems to see if there are any glaring problems like table scans etc?
February 1, 2005 at 11:20 am
I did run execution plans and they used index seeks extensively.
Here is what we ended up doing.
The developer was joining several tables; he used 5 Left outer joins and 4 inner joins.
He changed the first 4 joins to inner joins leaving 8 inner joins and one outer join.
This dropped the query execute time from 5 - 8 minutes down to 5 seconds.
I never realized outer joins were that much of a performance hit.
Again, thanks for your help.
February 1, 2005 at 11:25 am
You have to bear in mind that an outer join will return every record from the left table, whether there is a matching record in the right table or not. You have a couple of outer joins and things can really start getting out of control.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply