Question :
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.
Answer :
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.