So you're moving 3.3 million rows filtered down after the JOINs, etc., from 120 million rows. In short, you're moving close to 3% of the data. That's actually not usually...

Best of all, post the actual execution plan so we can understand how the optimizer is resolving your query and how well the statistics match your data.

One immediate...

Capture the wait statistics during the backups to understand what your server is waiting on. That can very quickly narrow down what's going on. However, everyone has already pointed out...

As everyone else has already said, it's almost impossible to tell you what to do without knowing the structures you're dealing with (beyond the fact that it's temp tables) and...

Just verified it to be sure, the calling databases cardinality estimation is used in cross-database queries. So, yeah, when you change the database to the one that has a different...

Also could be that the optimizer fixes are enabled/disabled on one of the databases and not the others. That would certainly lead to differences, especially if the query is already...

Ah, a restore won't move the logins. You'll need to script those out on the original server and move them over.

The missing indexes are, at best, suggestions. Be very cautious in simply implementing them.

As to the connections, step through it as granular as possible. What login do...

More than one drive and more than one drive controller would be better. SQL Server is IO intensive.

Part of the upgrade process should be testing. You should be moving the database or databases in question in a non-production environment where you can validate whether or not your...