Legacy CE vs current

Question

I recently updated a SQL server to the current version. I have major performance issues with some queries for example this query takes 8 seconds with legacy CE:

But 5 minutes with CE 150:

https://www.brentozar.com/pastetheplan/?id=Bkj1RbGrP

I updated statistics for the whole DB and ran a full scan update to the tables in this query.

Any idea how can I fix this performance issue?

Answer 1

Microsoft has guidance for your scenario where upon upgrading you experience query performance degradation. You may not be able to follow the exact steps outlined here if you were upgrading from a version without Query Store, but the general principles are still the same. It is expected that some queries will run slower after upgrade as Microsoft makes changes to the cardinality estimator. A first defense against that is to find the queries that are running for too long and to force them to use a previous plan. Methods of doing that:

1. Forcing a previous good plan with Query Store
2. Setting compatibility level at the database level or query level
3. Enabling the legacy CE at the database level or query level

For the root cause of why this particular query is slow, 98% of the query's time is spent on a nested loop join against Object4. It is hard to give specifics for an anonymized plan, but the following broad principles apply:

1. Use a temp table or some other method to improve the cardinality estimate coming into the nested loop join. The optimizer is likely picking a loop join because the estimate is just 1 row.
2. Define an index on Object4 which allows for more effective seeking against that table. This can only be a partial fix and won't get the query execution time down to 8 seconds.
3. If required, force a different join type for the table access on Object4. The nested loop join reads a total of 173,354,476 rows from a table with just 38,775 rows. A hash or merge join would be more efficient.
4. If required, achieve a different join order.

As a final note, you really can't expect to get good cardinality estimates for query constructs like this:

Function2(Function3(Object3.Column38)) LIKE Function2(Function3(Object2.Column11)) + ?

It may be true that the legacy cardinality estimator gives you good performance just due to luck. To keep the same performance on a newer CE you may need to use the invasive techniques that I described above.