Very complicated query is suddenly taking 35 minutes to finish in SQL Server 2014 I have a very complicated query that produces correct results slowly but ok in SQL Server 2012. After upgrade to SQL Server 2014 and after setting compatibility mode to 2014 (120) for the database, it is suddenly taking 35 minutes to finish. I have uploaded the actual plan on SQL Server 2014.
SQL Server 2014 with compatibility level 120 uses the new CE. SQL Server 2012 uses the legacy CE. The new CE uses a fundamentally different model for cardinality estimation that is expected by Microsoft to result in better performing queries for most workloads. However, they acknowledge that some workloads or queries may perform worse with the new CE. It is possible that is the situation that you're in.

When investigating a query that isn't finishing quickly enough, one of the first things I try to determine is why the query is slow. Your query is slow because over four billion rows are read from a single index seek operator:



You can verify that this is the most expensive part of the query by looking at the actual time statistics shown at the operator level. The index seek uses 222360 ms of CPU time and the overall query uses 224382 ms of CPU time. The most straightforward way to get better performance is by improving the index on the RSTReclassedJournalEntries table. This table only has 127787 rows, so it's unlikely that the additional maintenance required for a better index would be a concern. Creating an index with key columns Posted and ReclassedJRNENTRY should suffice.

The IX_RSTReclassedJournalEntries_1 index already exists on the table and is a covering index, but you want a index that can filter on Posted and ReclassedJRNENTRY as seek predicates instead of as filter predicates. With such an index SQL Server will read a maximum of 34646 rows from the index instead of 4382443766 rows. This will dramatically improve performance.

The index change that I'm recommending addresses the symptom of the problem instead of the root cause, but sometimes that's enough to meet the business need. The root cause appears to be a join cardinality estimation problem with [SOLMQ].[dbo].[AAG30000].[AK2AAG30000]. Addressing that is likely to be more difficult than making the suggested index change, although it's possible that you'll get lucky and updating statistics on that table will fix the problem as well.