Performance problem on copy of db

I have a performance problem here I could really do with some help on.
I am running SQL Server 7 on Win2k on a Dual Xeon box.
I moved the database from a much older machine and had some initial problems
running a particular complex query because of parallelism. Essentially,
doing an sp_updatestats and using only 1 of the 2 CPUs for parallel
execution of queries solved these problems. Clearly, this was an issue with
the more powerful processors on the new box.
Anyhow, once the performance problem was solved I made a copy of the
database using Backup/Restore and then changes were made to the data in the
new database copy (essentially, the application that uses the db was rolled
out to a different group of users).
All was well when I was last involved in this project back in October.
Recently, however, the same performance problems reappeared following a
reboot. I managed today to solve the performance problems on the original
database by running sp_updatestats.
However, running sp_updatestats on the second database did not solve the
performance problems. I've tried making another copy of the well-performing
database and the copy performs fine. Not sure what I was trying to prove by
doing this, but I was running out of ideas!
I also did a copy of the badly performing db using backup and restore and,
surprise, surprise, this made no difference.
Now that several months have passed, the copy I made contains different data
to the original so I can't simply overwrite it.
Apart from running sp_updatestats, I'm at a bit of a loss in terms of what
else to try. To give you some idea of the magnitude of the problem, the
query in question takes 30 seconds to run on the "good" database and 30
minutes to run on the "bad" one.
Looking at the execution plan, SQL Server does it completely differently.
The good db involves lots of "nested loops/inner join" processes. The bad
db involves lots of "has match/inner join" processes and these, I think, are
what slow things down. The good db has a "subtree cost" of 3.43 whereas the
bad one has something like 250.
Any ideas appreciated.Paul Welsh wrote:
> Looking at the execution plan, SQL Server does it completely differently.
> The good db involves lots of "nested loops/inner join" processes. The bad
> db involves lots of "has match/inner join" processes and these, I think, are
> what slow things down. The good db has a "subtree cost" of 3.43 whereas the
> bad one has something like 250.
sounds like you could have different indexes on the two databases.
you should also read up on fragmentation (dbcc showcontig, dbreindex,
dbindexdefrag, and defragging heaps).