Cardinality estimator

Recently I was asked by a software vendor to review a particular query that ran in under a second on a SQL Server 2014 installation at a compatibility level of 110 (SQL Server 2012), but when run under compatibility level of 120 (SQL Server 2014), the query took over 13 minutes.

The query had 6 left outer joins, not ideal but not excessive.

We made sure that we had the correct hotfixes applied.

https://support.microsoft.com/en-in/help/3044519/fix-query-performance-issues-when-new-cardinality-estimator-is-enabled

https://docs.microsoft.com/en-us/sql/relational-databases/performance/cardinality-estimation-sql-server?view=sql-server-2014&redirectedfrom=MSDN

We also did some testing with trace flag 4199, but it may no difference.

https://support.microsoft.com/en-au/help/974006/sql-server-query-optimizer-hotfix-trace-flag-4199-servicing-model

Whilst testing the query in compatibility level of 110 and getting the actual execution plan to compare with the query running in compatibility level 120. I noticed a missing index recommendation. Even though the query was running under a second I added the missing index and there was no significant improvement.

I then dropped the missing index and ran the query in compatibility level 120 to get the execution plan to see what was the big difference between the two compatibility levels. There was no missing index recommendation.

I then created the missing index from the 110 execution plan and ran the query again in compatibility level 120 and this time it returned in less than 1.5 seconds, down from over 13 minutes.

Moral of the story is to make sure you test before changing compatibility levels, but even then a simple index might be all that is required.