Question: I found that SQL Server 2012 and SQL Server 2014 use different non-clustered index for the same query and execution plan for SQL Server 2014 sucks.

I try to update all related tables and all indexes with fullscan even kick the old plan off, however SQL Server 2014 is not using the same index as SQL Server 2012.

Has anyone faced this problem, too?

How does optimizer on 2012 and 2014 make a decision for indexes selectivity?

Answer: 
SQL Server 2014 uses a new Cardinality Estimator, which can sometimes cause bad plans.

It is basically a new way for SQL Server to "guess" how many rows will be returned by each operator in the plan.

You can disable it on a specific query (if the estimations are causing a bad plan) by appending `OPTION (QUERYTRACEON 9481)` to the query.

You can disable it server wide by setting the compability level to something lower than 120:

```
ALTER DATABASE AdventureWorks2012 SET COMPATIBILITY_LEVEL = 110;

```

You might also be able to rewrite the query so that the new cardinality estimator actually performs better than the old estimator.

There is a lot of information about the new Cardinality estimator, I would take some time to read some of it. This is a good start:
<http://sqlperformance.com/2013/12/t-sql-queries/a-first-look-at-the-new-sql-server-cardinality-estimator>
Query optimisers are highly complex pieces of software and it will be better for you to do research into this on your own time, they calculate execution plans based on the most resource efficient route to the data and then execute the very most efficient plan. However, in short. 

First off, the cardinality estimator in SQL Server 2014 has been redesigned so the query plan chosen in SQL Server 2014 is probably more optimal compared to the query plan executed on SQL Server 2012 given the underlying schema design of the tables on both platforms are the same. 

It will help if you could post your query along with all the various table schema definitions to see exactly what you are working with.

[Here](https://msdn.microsoft.com/en-us/library/dn600374.aspx) is a link to Cardinality Estimation for SQL Server 2014

Hope this helps