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
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
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.
I would suggest to keep the new compatilblity level and use
traceflag 9481 -
- If it is just one query, use query hint
OPTION (QUERYTRACEON 9481).
- if you are seeing a lot of queries being regressed, enable the traceflag as server wide option - dbcc traceon (9481,-1) or just add it as startup parameter so it gets persisted after reboots.
- make sure that you have proper indexes and stats are up-to-date.
Your execution plan is reporting some estimation issues (e.g. Index Seek on AK2AAG30000 is estimating 1.12 rows and actually returning 79204 rows). Try updating statistics as that may help the optimizer make better estimates and therefore produce a better plan.
There are also some missing indexes being recommended which may eliminate the clustered index scan in favour of a seek. These recommendations may not have been made by the old optimizer, so look at testing these new indexes in a lower environment.
Also, for testing purposes, you can use query options (
OPTION (QUERYTRACEON 9481)) to use the old cardinality estimator and compare the two plans to identify differences.
The execution plan is serial as Maxdop has been set to 1. If the server has multiple CPUs, try using
OPTION (MAXDOP 2) to see if a parallel plan is able to perform better. It may perform better or worse.
Lastly, you may need to look at refactoring your code. While it works, it may be inefficient and as you have stated it wasn't fast in 2012, just acceptable, your best performance gains may come from rewriting your view code.