Ascending keys getting bad plans in SQL 2016 (new CE), replacement for TF 4139 et al.? The new cardinality estimator (CE) in SQL Server 2016 has an unfortunate regression which hits our deployment hard. The query in question is a run-of-the-mill Hibernate generated update; however the updated table is also part of an indexed view. This is a one-row update that will typically also affect ~5 rows in the indexed view.

I've managed to narrow the problem down to cardinality estimation of the PK-column of a 2.5M table, if the query is first compiled for a PK-value inside the current statistics for this column all is well and an optimal  plan is generated.

If the PK was newer, id est outside the statistics range, CE 130 generates a very suboptimal plan, essentially estimating that the whole indexed view will be affected by the update. This was never an issue for us with CE 120 or CE 70, both estimators generating good plans. The leading column in question is "branded" as UNKNOWN.

I have however found that there are several trace-flags (2388-90, 4139) that could be used for the old query planner, but these have no effect on CE 120+.

Is there any newer settings or trace-flags I can invoke to ensure that the normal query plan will be found every time?

I have tried TF 9481 to force the old CE, then it works brilliantly, but this is hard to combine with Hibernate (it's hard to inject hints into the query, and I really don't want to muck about in Hibernate's internals). I've also tried TF 4199 and the newest CU, to no avail.
If all of these are true:


You only need to fix a relatively limited number of queries (dozens to hundreds)
You know exactly what they are, and you can get the T-SQL
They don't change (end users can't dynamically build their own, or add search conditions)
You can guarantee they won't change
You can control which context they execute in (for example, they're not executing in TempDB with fully qualified object names pointing to user databases)
They're in a limited number of databases (as opposed to a software-as-a-service approach where every client is in their own database, and you want to fix all of the queries in all of the databases)


Then plan guides are a good fix. Just know that if you violate any of these rules (like if the queries start changing) then the plan guides won't work.