Do you get what you pay for?
See how the columnstore option for “The Aggregator” query compares between Standard Edition and Enterprise– there’s a measurable difference, even on this small test instance.
Get the scoop on the differences by Edition
Sunil Agarwal, Program Manager at Microsoft, breaks down the performance differences between Editions for Columnstore indexes in this blog post: https://blogs.msdn.microsoft.com/sql_server_team/columnstore-index-standard-and-express-editions-with-sql-server-2016-sp1/
Transcript
Lets talk a little bit about columnstore performance.
In SQL Server 2016, columnstore indexes also got a change in Service Pack 1, where if you were running SQL Server 2016 Service Pack 1 or later, you can also use columnstore indexes in Standard Edition, Web Edition, and lower editions.
With columnstore indexes though, there are some important differences in the performance that you’ll get, and they will vary by the edition that you’re using. As always, Developer Edition in SQL Server has the same features as Enterprise Edition.
In this demo, I have been showing you queries tested against Developer Edition and essentially showing you Enterprise Edition features. With columnstore indexes, though, we have some limits on how the queries can be executed, which optimizations they can take advantage of against the columnstore indexes, as well as how many resources they can use.
Let’s take a look at that.
Developer/Enteprise Edition example
Right now, this session is connected to our Developer Edition instance, think of it as Enterprise. I’ve got actual plans on, and I’m going to run the revised aggregator query against the instance. Let’s take a look at some of the details of the columnstore index scan operator.
With that operator highlighted, I can see that it was in batch mode. Batch mode’s also available in the lower editions. That shouldn’t change, that’s still going to be available. When I look at how it did, I did eliminate partitions with our rewrite.
Looking in our actual time stats, if I expand out, I can see that all four cores on my laptop got to scan the columnstore index. If I had more cores, I would be able to take advantage of even more, but I’m happy to get to use four on my laptop.
Standard Edition example
Going on over to Standard Edition– this query is connected to a Standard Edition instance. I’m going to show you my max degree of parallelism configuration setting by running this query. The instance is allowed to use all four cores as well, but when I go and I run the aggregator against Standard Edition, turn on actual execution plans for that.
And I have run this before. The data is in cache already for it.
It’s taking advantage of the memory its allowed, which you know the memory limit for Standard Edition is still well above the amount of data I’m testing here. So I’m not going to hit that limit. In a real production scenario, it would make a much bigger difference on most data sets.
Clicking on my columnstore index scan over here on Standard Edition, I’m in batch mode. I was able to do partition elimination, right. That stuff is still the same.
Looking at my actual time statistics and expanding that out, I can see that my batch mode operator was limited to just two cores, and for Standard Edition, it’s limited to two cores for batch mode queries.
That, on large data sets, is going to make a notable difference.
Comparing elapsed time
Well, how did the queries compare in terms of runtime? Let’s head over to our select operator, and looking at the query time stats on that, my elapsed time is about three and a half seconds for Standard Edition.
Head on back to Developer Edition, see how it did. My query time stats over there, close to two seconds this time. And in testing this, this is repeatedly true, even on my small data set on my laptop, where everything’s using the same SSD and memory.
Being able to use more cores makes a big difference. For queries where we’re doing big scans and aggregation, CPU power is going to make a notable difference.
Not that I’m complaining!
I am thrilled to see this columnstore index scan operator over in Standard Edition. It makes sense to me that the more data you have and the faster you want to process it, the more you might benefit from going up to Enterprise Edition.
I love the fact that it is an option in Standard Edition, even with the limits on the cores, and the limits on the memory, and the fact that we don’t get all the processing optimizations. This is still really, really cool.