I have a new 2019 instance. I migrated a database from a 2012 instance where it had 2005 as the Compatibility Level. The database was also set to Comp Level 2019.
A query was running for 80 seconds to return the resultset.
I rolled it back to 2008 which is the lowest level possible on a 2019 instance. I ran the query and it returned the resultset in 28 to 30 secs.
I switched back to 2019 and ran it. 87 seconds. Switched back and forth a few times and the 30 seconds seems to be the average when the comp level is 2008.
30 secs is still slow but just wanted to ask if anyone has experienced performance issues related to Compatibility Levels
Thanks
Do you use stored procedures in your application that uses this database?
Yes the stored proc selects from a view.
Could be that the procedures are cached with the previous compatability level. Read up on caching and how to clear it..carefully
I'm thinking about Cardinality Estimation, maybe you can test if the performance is better with the hint
OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));
Good video explaining:
Official doc:
Cardinality Estimation (SQL Server) - SQL Server | Microsoft Docs
2 Likes
@RogierPronk made the very same suggestion that I was going to make. I've experienced this very problem when we first made the jump from 1012 to 2016. The change occurred in SQL Server 2014.
1 Like