Dmitry Piliugin shows one way that we can optimize a query whose main problem is poor row estimates:
A model variation is a new concept in the cardinality estimation framework 2014, that allows easily turn on and off some model assumptions and cardinality estimation algorithms. Model variations are based on a mechanism of pluggable heuristics and may be used in special cases. I think they are left for Microsoft support to be able to address some client’s CE issues pointwise.
Today we are going to view some interesting model variation, that creates filtered statistics on-the-fly. I should give a disclaimer here.
Warning: All the information below is presented for purely educational and curiosity purposes. This is completely undocumented and unsupported and should not ever be used in production systems unless Microsoft support will recommend you. More to the point, the usage of this model variation may affect the overall server performance in a negative way. This should be used for experiments and in the test environment only.
It’s interesting reading, though do heed that warning. This also isn’t a quick operation (seeing as how the database engine is creating filtered statistics), so it’s not a first-best choice. But worth keeping your back pocket.