Recently upgraded from SQL Server 2008 to 2016, been running in compatibility mode 100 for 4 months everything was ticking along nicely (fast). After a fair amount of testing and running the migration adviser i decided to flick the "switch" and changed the compatibility level to 130...
This has had a detrimental effect on a quite a few sprocs/queries , some which where running in split seconds are now taking minutes. Also CPU has bumped up due to this.
These queries are written well, also I rebuild indexes and stats nightly..
Compared plans which look the same, with a couple of percentages slightly off, still the same plan though! i think CE isn't getting good row counts.
Also what is weird some times the queries run fine in 130 so i think all is good, but suddenly get an alert and the query is taken ages just by 2 rows being different then I have to add back OPTION (QUERYTRACEON 9481) to get it speedy again.
Are there any other things I can check / do to help get these queries get back to their former glory?
Should i remove all stored procedures plans from cache???