We upgraded our datawarehouse sql server from 110 to 140, RTM - CU17. In the new version, the query in the link below was allowed to run for 24+ hours before killing it.
If I add option(use hint('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110')), the query runs in 5 minutes. This query is from our cube processing job so I can't embed the hint in the cube partition without getting syntax errors.
A quick work around was to change the compatibility mode to 110 on the DB, process the cube, then revert back to 140. Not ideal....
I was able to spot on the execution plan that the period_dim is retrieving over 5 billion rows, but there are only 80K in the table. Assuming this is the issue, how do I help sql get a better estimate? All stats have been rebuilt using 100% sample.