Optimizer failing to load statistic when creating plan in SQL Server 2017 "Calculator failed. Replanning."

Question

This morning, the statistics on one of our filtered indexes updated, and when a plan that uses that statistics recompiled it stopped using that index (and started scanning the entire clustered index).

As this filtered index has included columns to cover the query, I had to wonder why.

When I tried the statement with options (RECOMPILE, QUERYTRACEON 3604, QUERYTRACEON 2363), I saw that the optimizer failed to load the statistic for the filtered, including column index:

Plan for computation:

  CSelCalcColumnInInterval

      Column: QCOL: [VendorPost1].StatusCode

Loaded histogram for column QCOL: [VendorPost1].StatusCode from stats with id 14

Calculator failed. Replanning.

We recently changed the compatibility mode in our databases to 2017 (having migrated in early Aug), and this is second time we've had an issue with stats not loading. The first time, a stat update with full scan fixed the issue, but this seems like it should be unnecessary.

Unfortunately, we can not find anything online that describe the issue, and I can't seem to recreate with test tables.

Does anyone know how to fix or even where additional information can be found? I'm starting to worry this is a bug we'll need to involve Microsoft in.

Answer 1

For a calculator to fail does not necessarily mean that loading of stats failed.

See Joe Sack's post with the query_optimizer_estimate_cardinality event indicating CSelCalcColumnInInterval CalculatorFailed:

The CSelCalcAscendingKeyFilter Calculator

Regarding the default for any values not in the histogram, Itzik Ben-Gan has a great summary in Seek and You Shall Scan Part II: Ascending Keys, but focuses on numeric data types.