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:
This is the estimated query plan for the second database:
The full query looks like this: