Niko's blog can be found here:
And Joe's blog can be found here:
Today I was tinkering with some CCIs... doing something that I often do: speculating about a system I haven't been able to access directly, trying to come up with theories to explain unexpected behavior. Along the way I found something interesting I hadn't heard anyone mention: a tipping point for count(*) aggregate pushdown against a CCI.
*****Update 26 August 2017*****
Niko checked out my plans and tried to reproduce on both SQL Server 2017 and SQL Server 2016... no dice. That reminds me - I should have included this for system context :-)
Without the same system context, results may differ. I'll update the blog post again once I determine if trace flags, compatibility level, optimizer hot fixes, CE version are necessary components for this behavior. (Turns out the main consideration for tipping point of Bigint CCI column is MAXDOP. CE plays a minor role - new CE gives integral row estimates for BIGINT columns.)
Here's a quick repro if you want to follow along.
Let me walk through it quickly.
I use Itzik Ben-Gan's getnums function a lot, so I'm more than happy to keep citing it :-)
So create the getnums function and the Tipper CCI in my database.
OK, lets use the getnums function to add 50 fully populated (1024 * 1024 rows) to the CCI, with incrementing values in the [key] column.
Now lets create fullscan stats on the [key] column. Fullscan stats and values starting at 1, incrementing by 1 give a little more predictability to row estimates in the plans.
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.
Look what happens if the upper limit is 27213.8 at MAXDOP 8. Row estimate of 27213.8 and... no more predicate pushdown!
Above I mentioned that DOP is an important part of determining the tipping point, with the cardinality estimator version playing a minor role. Here are the tipping point values for legacy and new CE, at DOP 1 to DOP 8.
The tipping point values are row estimates, remember. That's why the CE only plays a minor role here - since it's responsible for the estimates. The estimates of the new CE are slightly different than the legacy CE - and for this case where the filter is against a BigInt column it looks like the new CE is giving integral row estimates, while the legacy CE estimates are not necessarily integral.
Anyway... a graph of the Legacy CE tipping point values against MAXDOP makes the nearly linear relationship clear.