Sql-server – When the old execution plans changed in plan cache

If it's a case of different options causing different plans to be used, just check the options using sys.dm_exec_plan_attributes. If the options are not the same for a good and a bad plan, that can then be the reason.

A list of plan cache keys can be found in the following answer by Martin Smith:

What would cause parameter sniffing one one computer and not another?

I think you will be much better off testing your actual workload under sync and async, and see if you observe any differences. Coming up with a sweet spot to suit a particular feature is kind of backwards.

Generally, async is primarily beneficial when you don't care if the query that triggered the stats update still runs that last time with the old stats (or users can't wait long enough for the stats update to happen and the query to run). The threshold will not be easy for anyone else to tell you - it depends on how long the stats update takes (so depends on data size, I/O subsystem, sample size, etc.), how much the stats update helps the query (it may or may not generate a different plan, for example), and we don't know your end users' definition of "this query is taking too long."

Again, generally, sync is beneficial when you know that stats go south quickly, and that any query that runs with stale statistics will suffer. In this case, you should have a good idea that the duration of the stats update + the time of the improved query is on average less than the duration of the query using the stale stats.