We have a query on a SQL Server 2016 SP2 CU12 Enterprise where the Query Optimizer estimates that only 1 row would come out of the Nested Loops Join operator, in reality 108501 rows came back. This caused that the
Sort operator spilled to TempDB.
The estimates on the inner (index seek) and outer input (index seek) of the Nested Loops Join are correct.
I added trace flags 2363 (Selectivity Computation) and 3604 (redirect the output to the messages window) and here I found that there was a badly-formed histogram:
Plan for computation: CSelCalcExpressionComparedToExpression( QCOL: [Object1].Column1 x_cmpEq QCOL: [Object3].Column18 ) Loaded histogram for column QCOL: [Object1].Column1 from stats with id 1 *** WARNING: badly-formed histogram *** Loaded histogram for column QCOL: [Object3].Column18 from stats with id 9 Selectivity: 1.07973e-009 Stats collection generated: CStCollJoin(ID=4, CARD=1 x_jtLeftSemi) CStCollBaseTable(ID=1, CARD=5.01133e+007 TBL: Schema1.Table2 AS TBL: AA) CStCollFilter(ID=3, CARD=108210) CStCollBaseTable(ID=2, CARD=2.00511e+006 TBL: Schema1.Table1 AS TBL: A) End selectivity computation
Above is only a part of the output, the full text can be found here
When I updated the badly-formed histogram with fullscan the estimates are correct (without fullscan this issue is not resolved).
But as soon as one record is inserted in the table, the histogram is badly-formed again.
The query plan (with badly-formed histogram) can be found here and here you can find the query plan after update statistics.
No query optimizer fixes are enabled. When I enable the original cardinality estimator for this query, using trace flag 9481, I'm getting the same query plan as after update statistics.
What can cause the badly-formed histogram?
Is there a way to solve this issue?
I tried the
PERSIST_SAMPLE_PERCENT option but it didn't make a difference, the histogram gets also badly-formed.