We migrated from SQL 2008R2 to SQL 2016 a few years ago and have been running on Compatibility Level 100 since that point.
We are looking to make use of memory optimization introduced in SQL 2016 and that requires setting the compatibility level to 130.
We have had a few instances where a certain query will max out the CPU (that normally only takes a few seconds to run on Compatibility Lvl 100, but when we change it to Level 130 the CPU maxes out and does takes 2 minutes and 37 seconds to run vs about 2 seconds on the old compatibility level)
I did pull the execution plan for each of these
I am not a DBA so I can see there are differences between the execution plans but I could not see any reason for it to max out the CPU and take almost 6000% longer to run the same query with the same indexes etc.
One thing i noticed is that the slow running query was using 192 worker threads, whereas the fast running query only used 32. But I still couldn't figure out what was causing the high CPU usage.
Be glad of anyone's help or direction of something I may have missed!