SQL Server – Identical query on near identical database takes 20 seconds on sql2012 but does not complete after 50+ hours on sql2014

I have 2 databases that contain nearly identical data.

• The first has compatibility level 100 and is restored on a sql2012 instance.
• The second has compatibility level 110 and is restored on a sql2014 instance.

I am running the same query on both databases.

On the first database the query finishes in approximately 20-30 seconds.

I tried running the same query on the second, and it did not finish after 50 hours. If i try running the query on the second, but changing the compatibility level to 120 with trace 2312, the query finishes in 40 minutes.

I have tried rebuilding statistics for all tables affected by the query, and i also checked fragmentation (the first database actually looks to have far more fragmentation)
Why is there such a difference in performance, and what can I do to fix this?

This is the actual query plan for the first database:

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

This is the estimated query plan for the second database:

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

The full query looks like this:

https://justpaste.it/53b9c

Why is there such a difference in performance…

Check out this part of the estimated plan for the slow query (second server):

This estimates that 21 rows will come out of the join to RatesByCategory (which is after _Results323 has been joined to AttributesTable twice, and DataStreamDirection). If we look at the equivalent part of the actual plan for the fast query:

There will actually be ~16,000,000 rows coming out of that join. These is bad news, because those millions of rows flow right into a wall of Nested Loops joins, which is likely where that query slows to a crawl.

You were on the right track looking at estimates and trying to update stats, but this actually seems to be because of a row goal set by the semi-joins later in the execution plan.

Here’s where the semi-join lives, and notice that everything “under it” has an estimate of 1 row:

…and what can I do to fix this?

Try adding this hint to the end of the query:

OPTION (QUERYTRACEON 4138)

This will disable the row goal and should you give you a different plan (possibly similar to the one on the lower compatibility level).

Since you said this query is generated, your best bet for solving this might be to create a plan guide so that you can add that hint without having to change the application that produces the query.

If i try running the query on the second, but changing the compatibility level to 120 with trace 2312, the query finishes in 40 minutes.

Changing compat level to 120 would have enabled the “new” cardinality estimator, resulting in a different query plan – likely one that was less affected by the row goal (and thus why you were able to see it finish in 40 minutes, where the compat 110 query ran for 50 hours without finishing).