5

This question already has an answer here:

We are trying to migrate from SQL Server 2012 to SQL Server 2014 on Windows 2012 R2 OS.

After the upgrade, we are doing performance testing and observed huge difference in running a simple query from only one table (No joins at all).

The query is getting executed in 4 seconds in SQL Server 2012, but taking more than 1 minute in SQL Server 2014.

The SQL Servers are installed on 2 different VMs (different IP addresses). They have only one SQL Server instance on each machine.

The processor / ram / hard drive space is same between 2 machines.

The machines have the same applications installed. We are running the same select query from the SQL Server Management Studio directly.

marked as duplicate by Max Vernon, Tom V, Philᵀᴹ, ypercubeᵀᴹ, Andriy M Dec 3 '15 at 11:01

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

migrated from stackoverflow.com Mar 18 '15 at 17:25

This question came from our site for professional and enthusiast programmers.

  • 6
    Have you compared the execution plans of both queries? – Tony Mar 18 '15 at 11:40
  • 1
    Verify that the two VM's perform the same, just because they're set up with the same specs does not mean the underlying systems and/or disk systems act the same. Also make sure "power save" isn't turned on, on one of the machines or the underlying systems. – Allan S. Hansen Mar 18 '15 at 11:46
  • 3
    Did you follow common guidelines when migrating/upgrading SQL Server instances? See for example Upgrading to SQL Server 2014: A Dozen Things to Check. – MicSim Mar 18 '15 at 11:47
  • This may be due to new CE in 2014.Please change compatibility level to 110 and see if that helps. Make sure SQL Server 2014 is patched to latest CU(I guess its CU6) – Shanky Mar 18 '15 at 14:54
  • 1
    Is the max memory same on both the servers ? – Kin Shah May 4 '15 at 13:36
0

This most probably has something to do with the new cardinality estimator.

See my reply to this question.

If performance improves when you change the compatibility mode you have a good chance you are running in the same problem.