Changing database compatibility level to 2008 and back to 2016 improves performance - how can it be?

Question

Our customer reported an interesting phenomena that I can't explain: after they change database compatibility level from 2016 to 2008, and then back to 2016 the performance of a function that is used from a stored procedure significantly improves - from 9 seconds to 4. They don't execute the procedure with 2008 compatibility, only reset the compatibility.

I executed the procedure a couple of times, then flipped the compatibility, and executed the same procedure with the same parameters again - and it was using the same plan, but completed much faster. What can be the reason? Here is the query that I used to compare performance and its plan.

Query plan

Answer 1

Probably there is some newer feature that is affecting this old stored procedure. Since you are not showing the code, it's hard to pinpoint the source of the problem.

Answer 2

When we change a compatibility level of a database, it will cause to remove the cached stored procedure plan. For example, when we execute the following stored procedure in the Adventureworks database, it will insert it into the plan cache.

   EXEC uspGetEmployeeManagers 96

And then we will change the compatibility of the database and this operation will remove the cached stored procedure plan.

ALTER DATABASE AdventureWorks2017 SET COMPATIBILITY_LEVEL= 100

As a result after changing the compatibility level of a database, the stored procedure will use a fresh query plan. Using a freshly stored procedure query plan may improve query performance.