Below is what we want post-execution plans to look like when counting rows in a range – the thin arrow coming out of the Columnstore scan is a hint that predicate pushdown was successful. I didn’t specify MAXDOP in a query hint, and Resource Governor isn’t supplying MAXDOP; MAXDOP 8 is coming from the Database Scoped Configuration. The degree of parallelism turns out to be a significant factor in determining the tipping point. The [key] column is a BigInt. Maybe its surprising that I’m using 27213.9 as the upper bound. But… check out the estimated number of rows 🙂 Again – this estimate is coming from the Legacy CE, specified in the database scoped configuration for my database.
Interesting findings, although it looks like the specific values are going to be more settings-dependent than the usual finding of this nature.