MS SQL Server CPU load goes up dramatically when turning on 2014 features by setting compatibility level

Question

I'm running some Microsoft SQL Server 2014 load testing on a decently spec'ed machine. I'm certainly not an expert on the SQL Server tuning specifics, but with the bits an pieces I've gathered from various places I think the setup is quite OK.

One element in the performance does puzzle, hence I'm reaching out here. The database that I'm testing against was built up historically and was originally created against 2008r2. The compatibility setting was carried over from that version. I've raised that setting last week in two stages, but when I raised it from 2012 to 2014 compatibility, all of sudden the CPU load went through the roof.

When changing this setting back an forth it seems to be systemic. I can run X simulated users (web app in front of the DB) on an 8 core database with the 2012 setting. If I raise it to 2014 I only get away with 60% of X simulated users. And the other way around for X users the batch requests number is 40% lower for the 2014 setting.

As I understand, switching compatibility level back doesn't revert the DB but only turns off features. This situation seems to point to a feature that eats CPU with no apparent gain, so I was wondering which feature that could be and if there's a way to turn of that specific feature separately?

Other posts on serverfault have pointed towards rebuilding the indexes after an upgrade, I've done so, but that doesn't really seem to make a difference.

It's probably important to note that the application at hand generates a lot of queries that are not prepared statements. This is probably leading to the high number of compilations I see in perfmon (compilations/batch requests : 0.25). I can't do too much about that, so telling me to do something about that first won't really help me forward.

Answer 1

I would suspect the new cardinality estimator as a responsible for this behaviour, you can read up on it in the msdn article

It is clearly stated that the new cardinality estimator takes effect when you put the database in the new compatibility mode.

Upgrade all existing databases to use the new cardinality estimator. To do this, use ALTER DATABASE Compatibility Level (Transact-SQL) to set the database compatibility level to 120 or higher.

There are trace flags that control this behavior. If you want to use the new cardinality estimator when the database is in the old compatibility mode you can enable trace flag 2312 If you want to use the old cardinality estimator when the database is in the new compatibility mode you can enable trace flag 9481

If you can isolate the problematic query you could try to tune it so it performs well with the new cardinality estimator or if that doesn't work you can use the QUERYTRACEON keyword on that query only. That is also documented in the msdn link.

Also note that when, setting a trace flag you might need to use DBCC TRACEON (<traceflag>,-1) where the -1 sets it globally.