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.