Consistent query performance over time

I suggest you first determine whether its the stats or if its parameter sniffing that is hurting you.

Regardless of above I suggest you read Erland's article on the subject.

What to do about it is difficult to say. We don't know whether it is stats or sniffing.

But possibly adding OPTIMIZE FOR can be "the" solution. It is cheaper than RECOMPILE since you don't have to take the plan production hit on each execution. And it gives you predictability. This, of course, assumes that you don't have the case where the stats differ so much so the same parameter input yield different plans because of stats reasons.

Try to identify one query. See if you have one or many plans for the query. Test with OPTIMIZE FOR and/or RECOMPILE. The one "global" option at database scale you have is to disable parameter sniffing for the database. This means the optimizer optimizes as it doesn't have a clue of the value. All this and more in Erland's article.

Parameter sniffing does not only apply to stored procedures. It also applies to parameterized SQL (typically executed using sp_executesql), which likely is far more common nowadays than stored procedures.

_{Answer generated from comments}

You could get the wrong query plans due to the wrong statistics you got after update statistics. But you also could get the wrong query plans due to parameters sniffing when after update statistics the first parameters that your query got were not as usual. It's impossible to understand from your question which of the issues is presented. Try to recompile the query plan when the query gets worse instead of updating statistics to split the two different issues. – Denis Rubashkin

There is many factor that can cause a "rebuild" of the execution plan. So it would explain why it works fine for a while and suddenly start to work slow. When you update the stats, all execution plan that have something to do with this object are invalidate and this will cause the next execution to build a new plan. Depending on the values used for it, it may fix or not the problem (most of the values fixes it while some other don't, which explains why sometime it works, sometime not).

Another way to "fix" the execution plan would be to use Query Store (I think it started with SQL Server 2016) and "fix" the plan to use. It could have some drawbacks if the data changes a lot (as SQL Server will not be able to produce a better plan) but it could fix that kind of issue (I have a query running with a fix execution plan since 2 years now and didn't have the parameter sniffing issue since). – Dominique Boucher