Clustered Columnstore Indexes – part 17 (“Resources 2012 vs 2014”)

Continuation from the previous 16 parts, starting from http://www.nikoport.com/2013/07/05/clustered-columnstore-indexes-part-1-intro/

Lets compare to check if there were any improvements between the handling of the memory for Columnstore Indexes between SQL Server 2012 & upcoming SQL Server 2014. I will be keep on playing with the Contoso BI database, since it is working perfectly with both SQL Server versions. This time all my tests will be executed on the VM’s with 2 Cores and 4 GB of RAM.

My SQL Server 2012 has a version 11.0.3128, which means that I am running a Service Pack 1, if you are running any different versions then your results my vary.
Lets start off with dropping all primary and foreign keys from the 5 principal Fact table that I have been playing with:

Since there are no Clustered Columnstore Indexes on SQL Server 2012, I will be creating the Nonclustered Columnstore Indexes and comparing their resources usage with the NonClustered & Clustered Columnstore Indexes on the SQL Server 2014 CTP1.
Lets check the data on the SQL Server 2012:

Here is the plan that I have received on the Nonclustered Columnstore Index creation on SQL Server 2012 SP1:
Screen Shot 2013-08-10 at 18.15.18
I have taken some of the statistics for later comparison, but let’s do the very same operations on the SQL Server 2014 CTP1 and compare with the results:
Screen Shot 2013-08-10 at 6.11.45 PM
Now that is interesting – the plan is very different and we have an additional scan of the table as well as the Index insert operation. Judging by the subtree cost the difference is quite clear – we have ~91 for SQL Server 2012 SP1 vs ~161 on the SQL Server 2014 CTP1, the max used memory management differs as well – 364840 vs 216528 respectively. This gives a very interesting view into the functioning of the Columnstore Indexes – while having a much more resource intensive execution plan for the SQL Server 2014 CTP1, the Nonclustered Columnstore Indexes are actually using less memory.

Lets drop our traditional and Nonclustered Columnstore indexes and lets create a new Clustered Columnstore Index on the SQL Server 2014 CTP1:

Screen Shot 2013-08-10 at 6.13.00 PM
The execution plan looks quite the same with the changes that we have a nonclustered table scan as well as the Clustered Index Insert operation, but apparently everything else look very same. Lets check the numbers: ~153 for the Subtree Cost, and 208008 for the MaxUsed Memory – this is definitely better than the Nonclustered Columnstore Index in both resources spending on the CPU & IO costs as well as used memory.

Take a look at the following 2 images:Screen Shot 2013-08-10 at 18.34.59

Screen Shot 2013-08-10 at 18.34.51

As far as I am concerned, they speak quite good for themselves – the SQL Server 2014 CTP1 shows that thew memory usage has been improved as well as the better support for the parallel operation has been introduced. Clustered Columnstore Indexes seem to be faster as well as using less resources than the comparable Nonclustered Columnstore Indexes on the SQL Server 2014 CTP1.

Lets run the same comparisons with the other 4 tables, here is the code for the Nonclustered Columnstore Indexes:

For the SQL Server 2014 CTP1, I ran exactly the same query to extract the data for the Nonclustered Columnstore Indexes, but additionally the following script to analyze the Clustered Columnstore Indexes:

From the Memory Grant perspective, most of the time the amounts are equal for all Columnstore Indexes on the SQL Server 2014 CTP1. From the Desired Memory, it looks like it is always Clustered Columnstore that is asking for around 10MB less of memory.
The tree cost of the queries seems to follow the pattern of increasing for the Nonclustered Columnstore Indexes on SQL Server 2014 CTP1, but then dropping a certain percentage for the Clustered Columnstore Indexes.
All in all, all tested tables and indexes are following pretty much the described patterns, here are a couple of images with the results of this post:
Screen Shot 2013-08-10 at 21.11.46

Screen Shot 2013-08-10 at 21.11.34

to be continued with Clustered Columnstore Indexes – part 18 (“Basic Batch Mode Improvements”)

Leave a Reply

Your email address will not be published. Required fields are marked *