SQL Server Upgrade 2008 -> 2016 / 100 to 130 compatibility slow queries, new CE killing me
Microsoft has an upgrade strategy for changing the compatibility mode on SQL Server 2016. Quoting the linked article:
The recommended workflow for upgrading the query processor to the latest version of the code is:
Upgrade a database to SQL Server 2016 without changing the database compatibility level (keep it at prior level)
Enable the query store on the database. For more information about enabling and using the query store, see Monitoring Performance By Using the Query Store.
Wait sufficient time to collect representative data of the workload.
Change the compatibility level of the database to 130
Using SQL Server Management Studio, evaluate if there are performance regressions on specific queries after the compatibility level change
For cases where there are regressions, force the prior plan in the query store.
If there are query plans that fail to force or if performance is still insufficient, consider reverting the compatibility level to the prior setting and then engaging Microsoft Customer Support.
You could try a version of that for your situation. Change the compatibility mode back to 100, enable the query store, go through a full business cycle and get a good baseline, then change compatibility mode and use the query store to analyze poorly running queries and take further action on them.
You could be using a different version of the SQL Server Cardinality Estimator on the new instance
This is specifically for folks who have upgraded to SQL Server 2014 or higher.
Use this command to make it faster..
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON
GO