6

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.

Improve this question
0

1 Answer 1

Reset to default
2

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.

Improve this answer
2
  • 1
    Thanks for the reply, Brent. Plan guides would in general be a very useful tool to fix this, so I accepted the answer. However, as the query in question comes from Hibernate, I'm not entirely certain that they won't change, as a change in the ORM would very likely create a whole new query. I'm therefore waiting with 2016 or compat level 130 in our deployment until the issue can be fixed, I've made a connect-item with MS, connect.microsoft.com/SQLServer/feedback/details/3124217/….
    – user22674
    Feb 27, 2017 at 11:22
  • Good call about the Connect item. If your company will allow it, I'd open a support call. It's $500, and they'll refund it to you when they understand that it's a bug in the engine. You can even get early access to a patch that'll fix it. Connect items don't have any kind of urgency. I get the sense that this might matter enough to you that the company might be willing to pay the $500.
    – Brent Ozar
    Feb 27, 2017 at 13:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.