Problem after migration from compatibility level 100 to 120.

My colleague had problem with one query, this problem occured after migration from SQL Server 2008 R2 to SQL Server 2014, when he switched compatibility level from 100 to 120 one query slowed down, he used special hint in query. Entire query looked like this.

I didn't remeber why he used this hints, probably for better performance. Query ran around 16 second under CL 100, but suddenly after switch to CL 120 it tooks 4 hours.Problem was in combination these two hint QUERYTRACEON 2312, QUERYTRACEON 9481 on end of this query. What do these two hints do?

QUERYTRACEON 9481 - This hint is used with compatibility level 120 and above for forcing cardinality estimator version 70, which means from SQL 7.0 to SQL Server 2012. So, it forces query plan for CL 110 and lower.

QUERYTRACEON 2312 - This trace flag does exactly same but of opposite way. For example when we have database with compatibilty level 100(SQL Server 2008) which have cardinality esimator 70 and need to for query plan for SQL server 2014 we have to use this trace flag.

So, we cannot use this trace flags in combination for database with compatibility level 120, but only you can use thsi one QUERYTRACEON 9481.

I sent a modified query to my colleague and query runs around 13 second, additionally i created cluster index and now his query runs to 3 seconds.

I hope it will help you! See you.