April 23, 2003 at 2:46 am
Can anyone help me with this?!
The customer noticed that performance was getting poorer in the intranet. The problem started when upgrading SQL server to SP4. (SQL server 7.0)
One query took 33 seconds to run after upgrade, before it took 1 sec. If I check the execution plan I can see that it is a few nested loops joins that takes the most of the time. The tables involved has about 2000 rows. According to execution plan this leeds to two table scans with a row count of 2.8 miljon, and another row count 1.7 miljon.
When I run the query on another server with SP2 the query runs in 1 sec. And according to execution plan hash joins is made between the tables mentioned earlier.
For "fun" I installed SP4 on the server with SP2 and the query took over 20 seconds to run.
It seems as after upgrade the server i choosing a much poorer execution plan.
April 23, 2003 at 3:41 am
I haven't noticed any of our queries running any slower between the SP revisions. However, I did do a DBCC DBREINDEX after the update along with sp_updatestats and DBCC UPDATEUSAGE. You might try those to see if they help. Since you can duplicate the issue try each one seperately and see which if any fix your performance issue.
Edited by - antares686 on 04/25/2003 03:33:59 AM
April 24, 2003 at 1:25 pm
We had a similar problem and as said by Antares, reindexing and update stats worked.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply