| Author |
Topic |
|
sqldba20
Posting Yak Master
183 Posts |
 Posted - 2007-11-06 : 13:21:35
|
| Recently we moved few of our databases from SQL 2000 to SQL 2005 (SP 2) using backup and restore. After the restore I did Reindex and update stats on the databases. Since then we have observed performance issues on SQL 2005 databases but this performance problem vanishes the moment we run (sp_updatestats). Is this a problem with SQL 2005 that we have to run sp_updatestats 2 times a days or 3 times a day. In SQL 2000 we ran it only Once a week and still we never had any performance issues. Is there any config change we need to do to fix this problem in SQL 2005?Thanks ! |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-11-06 : 13:26:33
|
| Did you change the compatibility mode to 90 after you restored the db's?Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
sqldba20
Posting Yak Master
183 Posts |
Posted - 2007-11-06 : 13:29:15
|
| Yes... I changed the compatibility to 90 after the restore. |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-11-06 : 13:32:27
|
quote:
Originally posted by sqldba20
Yes... I changed the compatibility to 90 after the restore.
and before the reindex?Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
sqldba20
Posting Yak Master
183 Posts |
Posted - 2007-11-06 : 14:02:58
|
| Yes... I restored the databases, changed Compatibility to 90 and then run the REINDEX and also sp_updatestats. One correction: Migrated SQL 2000 databases to SQL 2005 SERVICE PACK 1 and not SERVICE PACK 2. |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-11-06 : 14:14:56
|
| any particular reason for not going to SP2? There were lot of changes made in SP2 although I dont know the details..Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
sqldba20
Posting Yak Master
183 Posts |
Posted - 2007-11-06 : 14:19:50
|
| On the same server we have one more third party vendor application database and the vendor's application doesn't support SP 2 for now so we have to leave with SP1 for a while. Do you think by changing the Compatibility to 80 (now that it is already 90) will have any impact or performance improvement? |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-11-06 : 16:04:16
|
| not really..the DB engine has changed for 2005. so if you change the compatibility back to 80 you will not be able to run 2005 specific commands but that might not help with performance.Do you have a auto update stats turned off? Do you see any particular procs that are bevaving badly?Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
sqldba20
Posting Yak Master
183 Posts |
Posted - 2007-11-06 : 17:01:40
|
| I turned the Auto Update Stats OFF (earlier it was ON) thinking that might be causing the performance problem. The surprising thing is the moment we run sp_updatestats the same stored procedure will finish in less than a minute |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-11-06 : 17:22:35
|
| Perhaps you might want to try leaving it ON? It does cause little performance overhead in updating the stats but if the procs benefit from the updated stats the cost is worth the gain.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-06 : 17:43:14
|
| "The surprising thing is the moment we run sp_updatestats the same stored procedure will finish in less than a minute"Might be worth checking the Query Plan being sued before/after the Update Stats - knowing how it changes might help pinpoint the cause.Kristen |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-06 : 22:04:16
|
| Maybe need new index since optimizer works different from sql2k's. |
|
|
|
|
SQL 2005 Performance and Update Stats