I have 2 remote SQL Server 2014 instances: Server A with version 12.0.4100.1 and Server B with version 12.0.4213.0.
I have exported a DB from server B, copied and restored it to server A for tests. I have a query (really long) which I execute on both servers. The Server B returns the results in 2 min and server A in 1-2 sec. I have tried to export back the DB from Server A to server B, the same DB and nothing changes.
I have noticed the following on server B: if I export all the tables of the DB into a TempDB, then delete all the tables from DB and then import all the tables from TempDB back into DB the problem is gone. This happens for both the old (original) DB and the new (exported back from server A) one as well.
My problem: what can cause this “problem” and how to prevent it?
Firstly as @jonhg points out the servers can give you different execution plans as the tables and indexes are rebuilt. The server hardware (memory, cpu, hard drives) can be different and finally you don’t have the same version of SQL server in both cases. Any of these would be sufficient to give different results.
Other queries could be interfering, as the cache on the servers can be different. All the answers to this are found here: http://download.red-gate.com/ebooks/SQL/eBOOK_SQLServerExecutionPlans_2Ed_G_Fritchey.pdf