Problem:
We upraded from SQL Server 2008 to 2016, after changing compatibility mode from 100 to 130 we found some SPs running very slow. Once we change back to 100 SPs working fine.
Workaround:
1. Please find below table to check compatibility mode.Currently, 2016 supporting 100, so we are not in danger.
2. Check all Index and Stats are updated
3. Cardinality estimation introduces since 2014, this will mostly cause some SP running slowly in new compatibility mode. If you want to use the old cardinality estimator when the database is in the new compatibility mode you can enable trace flag 9481
4. If you want to use the new cardinality estimator when the new database in the old compatibility mode you can enable trace flag 2312
5. If only a few SPs running slow, then I recommend profile the execution plan / read/ write etc of the offending SP on a test system in SQL Server 2016 with compatibility level 130 and rewrite the SP to cater for the new cardinality estimation.
No comments:
Post a Comment