Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
haroldchen1999
Starting Member
7 Posts |
 Posted - 2009-03-26 : 12:41:55
|
| After upgrading the SQL Server from 2000 to 2005, I found one of the sql statement with more than 6 joins goes very slow (have to kill the process). But in SQL 2000, it works without problem. Anyone has same issue before, only more than 6 joins with this problem. Any idea about the configure of the database to improve the performance. |
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-03-26 : 12:59:00
|
| can you try running it again after updating statistics ? |
 |
|
|
haroldchen1999
Starting Member
7 Posts |
Posted - 2009-03-26 : 21:00:25
|
| Did the test, same result after updating statistics. Thanks |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-03-26 : 21:59:39
|
| Any join or table hints in your query? Have you tried UPDATE STATISTICS...WITH FULLSCAN? You might also want to run DBCC FREEPROCACHE and run the query again, you might have a stuck plan. Also consider DBCC DROPCLEANBUFFERS, it will clear the data caches and give you a more consistent baseline.If all else fails, try ALTER INDEX...WITH REBUILD if you can take the hit. Every once in a while (like today) I get a query that craps out and the only thing that brings it back to life is a reindex. I'll say this too, it happens a lot more frequently on 64 bit SQL Server, so if you're running 64 bit, give it a shot. |
|
|
|
|
|
|
SQL joins very slow after upgrading to SQL 2005