On SQL Server 2014, this query is taking about 24 minutes to complete. If I add OPTION (QUERYTRACEON 9481) to force the optomizer back to 2012, this query takes less than 1 second.
I noticed that there is a Hash Match (Inner Join) joining 70k rows against 77k rows but is producing 58.8 million rows. I would have expected it to produce less than 70k since only a portion rows are common to both sets.
Edit Uploaded a second attachment that shows the query plan with 9481 enabled.
By poke 18 asked Dec 08, 2014 at 03:19 PM
The optimizer considers all sorts of transformations. One of these involves computing a
Exactly what this transformation is aimed at is hard to say without being able to see the view definition, but I can say that the decision to apply this transform or not is a cost-based choice, which depends in part on cardinality estimates. The new cardinality estimator (CE) chooses a plan with this transformation, but the old CE model does not. The root cause is a simple change in estimated costs between the alternatives.
These sorts of plan changes and possible performance regressions are possible - indeed expected - when changing CE model.
Unless this query absolutely needs to be monolithic - perhaps for use in an in-line function or view - I would encourage you to break the CTEs apart and use temporary tables to store intermediate results. You will, generally speaking, get better quality plans this way, because the optimizer has better information to work with. As written, the cardinality estimates and value distributions are likely to be little better than complete guesses at most points.
By SQLkiwi ♦ 6.6k answered Dec 08, 2014 at 04:23 PM