Drenlin (3/26/2015)
Phil Parkin (3/26/2015)That's exactly what the compatibility level is for, though it does come with downsides: all of the new features in 2014 are turned off for the databases you set this for.
And there is a solution: do your performance testing / analyses before you migrate and work out where the problems are. The chances are that the new cardinality estimator is the culprit. With some work, those issues can be resolved.
I thought Grant said 'somewhere besides the cardinality estimator'.
Would 2012 compatibility turn off the new estimator?
Yes it does.
You can use a traceflag (forget the number) to turn off the cardinality estimator at the server, database, or query level. Using it at the query level is a much more surgical way of solving the problem and would be preferred in most cases. Doing this instead of killing the compatibility level and removing ALL 2014 functionality makes more sense. After all, you upgraded to 2014 for a reason I assume.
Not sure why you'd see massive increases in tempdb unless, again, the cardinality estimator is leading to execution plans that dump to tempdb more often due to sorts or hash tables or something.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning