1

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?

Improve this question
2

1 Answer 1

Reset to default
1

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

Improve this answer
2
  • Thanks for the link and suggestion. I will follow the eBooks guidelines and post back here when I have a result.
    – Claudiu
    Commented Feb 18, 2016 at 7:39
  • Although the Hardware and SQL Version were not the same, I think the Problem was indeed in the Server Cache. I have restarted the server and the Query returned the results in about 3 sec.
    – Claudiu
    Commented Mar 7, 2018 at 15:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.