Cardinality estimator and query plans
The cardinality estimator has been redesigned in SQL Server 2014. It has been redesigned in order to improve the quality of query plans and thus improve the query performance. The new cardinality estimator includes assumptions and algorithms that work well on modern transactional systems (OLTP) and data warehousing databases. Microsoft has made these changes based on customer feedback. The following is the feedback from a Microsoft customer:
while most queries will benefit from the change or remain unchanged, a small number might show regressions compared to the previous cardinality estimator.
The changes to the cardinality estimator means that you should allow additional testing of your database as you migrate and move them to SQL Server 2014 to ensure that this change does not affect your database performance in a negative way.
