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.