We recently upgraded from SQL Server 2012 to SQL Server 2016. We noticed an unusual amount of IOps on our Dev and Integration servers, but not on production. The only difference is that production has not yet been set to SQL Server 2016 compatibility mode 130.
Running the following script I can turn off/on the extra IOps on our integration server. Why would compatibility mode make such a huge difference in storage?
EXEC sys.sp_MSforeachdb @command1 = N' ALTER DATABASE ?
SET COMPATIBILITY_LEVEL = xxx'
Where xxx is 110 for higher IOps or 130 for lower IOps.
Best Answer
Presumably something is causing the query planner to produce a less optimal plan for some of your regular statements, resulting in more IO being needed (perhaps index scans where seeks were previously used, or maybe the worse plan requires extra sort operations and/or sorts over larger rowsets so is spilling more into tempdb).
A key difference between 2016 and 2012 is the updated cardinality estimator, added in 2014 and made default in 2016. To tell for sure we would need to see query plan output for the two circumstances. The new estimator produces better plans in many cases, but worse ones in a few specific cases.
For more general detail search for "sql server cardinality estimator" and you'll find many articles on the change and its implications.
For more specific help, we would need you to edit query text and query plan output from both circumstances into your question.
It could be that you need to turn the new estimator off, it could be that you have hints in the queries designed to help the older planner which make the newer one have a harder time, it could be that the queries are odd/complex and need hints to help the new planner/estimator and so on.