Modification_Counter influence the Cardinality Estimator?

Question

I ran into something that is really mysterious to me and I would need your help to find an explanation (didn't find anything on Google).

Here is the situation. I have 3 tables and in one of them, there is like 40 millions rows with a column having null for all except 3 rows (let's call that column "param". There is a nonclustered index on that column and the statistics are good (something like:

1 NULL 0 3.596004E+07 0 1
2 bcd 0 1 0 1
3 cde 0 1 0 1
4 def 0 1 0 1

I have a query that is executed with "where Param = x".

When I executed the query with a value like "Param = 'cee'", my execution plan does a nonclustered index seek and then a merge join on the other table (with a scan of the cluster index) and it works in a millisecond.

When I run the same query with "where Param = 'abc'", the execution plan changes. It starts with a full scan of the second table (clustered index) and then the merge join on the million rows table (still seek on nonclustered index).

So far so good. Trying different thing, I rebuilt the NC index and right after that, I wasn't able to reproduce the issue. I wasn't gonna stop there! So I start changing values and putting it back to the original value so the Statistics remain good but the modification_counter increased. When the modification_counter reached 10,000, the issue came back!

As the table is huge (40 million rows), 10,000 modifications is not enough to trigger the update of the stats and as I got all of those changes during a day, my maintenance plan can't do anything to prevent the issue.

So what I'm trying to understand is:

How does the modification_counter influence the CE to take a different execution plan.

Please keep in mind that the statistics are always good when I run the query (only 3 rows in the 40 millions with a non null value and always the same 3 values) so it's not the statistics that cause that behavior.

Using SQL Server 2014 but same behavior Under 2016.

Answer 1

How does the modification_counter influence the CE to take a different execution plan?

One way this can happen is that the new CE in SQL Server 2014 may interpret statistics differently depending on how many modifications have occurred on that column since the last statistics update. From Seek and You Shall Scan Part II: Ascending Keys by Itzik Ben-Gan:

The new cardinality estimator identifies that the query filter exceeds the maximum value in the histogram. It has access to the column modification counter (call it in short modctr), so it knows how many changes took place since the last refresh. When the column is unique and of an integer type or numeric with a scale of zero, SQL Server assumes it’s ascending. So it interpolates the estimate based on the distribution of the values within the existing histogram and the number of changes that took place since the last refresh.

I cannot say if that behavior is leading to your issue. You could try running both queries with trace flag 9481 to see if your issue is caused by something new in the 2014 CE:

OPTION (QUERYTRACEON 9481)

You can also use query hints to force what you think is the better plan. Comparing the hinted plan with the unhinted plan may give you a clue as to why SQL Server is choosing the different plan.