Thank you for taking this course!

Transcript

Summing up what we’ve learned in this course.

When you’re looking at an actual execution plan, that’s when you get to see information about actual partition count and actual partition count is the total number of partitions that were accessed. The actual partitions accessed information is a list or range of the specific partition numbers that were accessed.

So in the screen shot, we had to look at 56 partitions and they were partition numbers one to 56. In some cases, on columnstore indexes, you may see actual partition count of zero. That doesn’t mean that data access didn’t happen. I see that as, okay, I didn’t get partition elimination, but maybe, maybe I was able to do some magic with rowgroup elimination. You’re going to have to do some more leg work to figure that out.

When you’re looking at the number of rows flowing through an execution plan, the actual number of rows flowing out may look weird in some cases. Here is the case where the actual number of rows flowing into the hash match aggregate showed as zero, but yet, one row flowed out of it. That was because in this case that columnstore index scan was able to use that cool locally aggregated rows feature to do the counting of compressed segments in compressed rows groups itself. And it didn’t have to make the hash match aggregate count them.

In other cases when we did see rows flowing into the hash match aggregate operator, they were the rows that were in an open rowgroup and the open rowgroup is that delta store, stored in a B-tree. So, the number of rows flowing into the hash match aggregate is the number of rows that couldn’t be, in this case, counted because the query’s doing a count, that couldn’t be aggregated inside of the columnstore index, pushed down into an aggregate there.

When you’re looking at statistics IO output, which can be incredibly useful for columnstore indexes, when you see things about segment reads and segment scripts, what it’s actually talking about there is rowgroup elimination, there’s a little bit of a typo.

And rowgroup elimination is a really powerful tool that where columnstore indexes can use information it has about what values happen to be in that segment, in that rowgroup to say, “Do I even need to look at this or not?” So, SQL Server can eliminate, in some cases, some rowgroups and this really helps it. It can use it with partition elimination or it can use it on its own.

We do not yet have rowgroup elimination for strings. Maybe someday, that would be very cool if we could have it, but we don’t have it for string columns yet.

Thank you so much for joining me for this session. I have drawn a desktop background that has some diagrams of a partitioned row store, disk-based table as well as a partition columnstore table. And it has a lot of the terminology and jargon around partitioning and columnstore. Check it out, I hope you enjoy it, and I look forward to seeing you in future courses as well.