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:

alter table dbo.[FactOnlineSales] DROP CONSTRAINT PK_FactOnlineSales_SalesKey
alter table dbo.[FactStrategyPlan] DROP CONSTRAINT PK_FactStrategyPlan_StrategyPlanKey
alter table dbo.[FactSales] DROP CONSTRAINT PK_FactSales_SalesKey
alter table dbo.[FactInventory] DROP CONSTRAINT PK_FactInventory_InventoryKey
alter table dbo.[FactSalesQuota] DROP CONSTRAINT PK_FactSalesQuota_SalesQuotaKey

alter table dbo.[FactOnlineSales] DROP CONSTRAINT FK_FactOnlineSales_DimCurrency
alter table dbo.[FactOnlineSales] DROP CONSTRAINT FK_FactOnlineSales_DimCustomer
alter table dbo.[FactOnlineSales] DROP CONSTRAINT FK_FactOnlineSales_DimDate
alter table dbo.[FactOnlineSales] DROP CONSTRAINT FK_FactOnlineSales_DimProduct
alter table dbo.[FactOnlineSales] DROP CONSTRAINT FK_FactOnlineSales_DimPromotion
alter table dbo.[FactOnlineSales] DROP CONSTRAINT FK_FactOnlineSales_DimStore
alter table dbo.[FactStrategyPlan] DROP CONSTRAINT FK_FactStrategyPlan_DimAccount
alter table dbo.[FactStrategyPlan] DROP CONSTRAINT FK_FactStrategyPlan_DimCurrency
alter table dbo.[FactStrategyPlan] DROP CONSTRAINT FK_FactStrategyPlan_DimDate
alter table dbo.[FactStrategyPlan] DROP CONSTRAINT FK_FactStrategyPlan_DimEntity
alter table dbo.[FactStrategyPlan] DROP CONSTRAINT FK_FactStrategyPlan_DimProductCategory
alter table dbo.[FactStrategyPlan] DROP CONSTRAINT FK_FactStrategyPlan_DimScenario
alter table dbo.[FactSales] DROP CONSTRAINT FK_FactSales_DimChannel
alter table dbo.[FactSales] DROP CONSTRAINT FK_FactSales_DimCurrency
alter table dbo.[FactSales] DROP CONSTRAINT FK_FactSales_DimDate
alter table dbo.[FactSales] DROP CONSTRAINT FK_FactSales_DimProduct
alter table dbo.[FactSales] DROP CONSTRAINT FK_FactSales_DimPromotion
alter table dbo.[FactSales] DROP CONSTRAINT FK_FactSales_DimStore
alter table dbo.[FactInventory] DROP CONSTRAINT FK_FactInventory_DimCurrency
alter table dbo.[FactInventory] DROP CONSTRAINT FK_FactInventory_DimDate
alter table dbo.[FactInventory] DROP CONSTRAINT FK_FactInventory_DimProduct
alter table dbo.[FactInventory] DROP CONSTRAINT FK_FactInventory_DimStore
alter table dbo.[FactSalesQuota] DROP CONSTRAINT FK_FactSalesQuota_DimChannel
alter table dbo.[FactSalesQuota] DROP CONSTRAINT FK_FactSalesQuota_DimCurrency
alter table dbo.[FactSalesQuota] DROP CONSTRAINT FK_FactSalesQuota_DimDate
alter table dbo.[FactSalesQuota] DROP CONSTRAINT FK_FactSalesQuota_DimProduct
alter table dbo.[FactSalesQuota] DROP CONSTRAINT FK_FactSalesQuota_DimScenario
alter table dbo.[FactSalesQuota] DROP CONSTRAINT FK_FactSalesQuota_DimStore

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:

create clustered Index PK_FactStrategyPlan 
    on dbo.FactStrategyPlan (StrategyPlanKey ASC)
    WITH ( DATA_COMPRESSION = PAGE);

-- Create a Nonclustered Columnstore Index:
Create NonClustered Columnstore Index NC_PK_FactStrategyPlan 
    on dbo.FactStrategyPlan (StrategyPlanKey, Datekey, EntityKey, ScenarioKey, AccountKey, CurrencyKey, ProductCategoryKey, Amount, ETLLoadID, LoadDate, UpdateDate)
    

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:

drop index NC_PK_FactStrategyPlan on dbo.FactStrategyPlan
drop index PK_FactStrategyPlan on dbo.FactStrategyPlan
create Clustered Columnstore Index PK_FactStrategyPlan on dbo.FactStrategyPlan;

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:

create clustered Index PK_FactOnlineSales 
    on dbo.FactOnlineSales (OnlineSalesKey ASC)
    WITH ( DATA_COMPRESSION = PAGE);
 
 Create NonClustered Columnstore Index NC_PK_FactOnlineSales 
    on dbo.FactOnlineSales (OnlineSalesKey, DateKey, StoreKey, ProductKey, PromotionKey, CurrencyKey, CustomerKey, SalesOrderNumber, SalesOrderLineNumber, SalesQuantity, SalesAmount, ReturnQuantity, ReturnAmount, DiscountQuantity, DiscountAmount, TotalCost, UnitCost, UnitPrice, ETLLoadID, LoadDate, UpdateDate)

-- **************************************
create clustered Index PK_FactInventory 
    on dbo.FactInventory (InventoryKey ASC)
    WITH ( DATA_COMPRESSION = PAGE);
 
 Create NonClustered Columnstore Index NC_PK_FactInventory 
    on dbo.FactInventory (InventoryKey, DateKey, StoreKey, ProductKey, CurrencyKey, OnHandQuantity, OnOrderQuantity, SafetyStockQuantity, UnitCost, DaysInStock, MinDayInStock, MaxDayInStock, Aging, ETLLoadID, LoadDate, UpdateDate)

-- **************************************
create clustered Index PK_FactSalesQuota 
    on dbo.FactSalesQuota (SalesQuotaKey ASC)
    WITH ( DATA_COMPRESSION = PAGE);
 
 Create NonClustered Columnstore Index NC_PK_FactSalesQuota 
    on dbo.FactSalesQuota (SalesQuotaKey, ChannelKey, StoreKey, ProductKey, DateKey, CurrencyKey, ScenarioKey, SalesQuantityQuota, SalesAmountQuota, GrossMarginQuota, ETLLoadID, LoadDate, UpdateDate)

-- **************************************
create clustered Index PK_FactSales
    on dbo.FactSales (SalesKey ASC)
    WITH ( DATA_COMPRESSION = PAGE);
 
 Create NonClustered Columnstore Index NC_PK_FactSales 
    on dbo.FactSales (SalesKey, DateKey, channelKey, StoreKey, ProductKey, PromotionKey, CurrencyKey, UnitCost, UnitPrice, SalesQuantity, ReturnQuantity, ReturnAmount, DiscountQuantity, DiscountAmount, TotalCost, SalesAmount, ETLLoadID, LoadDate, UpdateDate)

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:

drop index NC_PK_FactOnlineSales on dbo.FactOnlineSales;

drop index PK_FactOnlineSales on dbo.FactOnlineSales;

create clustered columnstore index PK_FactOnlineSales
	on dbo.FactOnlineSales 

-- **************************************************************
drop index NC_PK_FactInventory on dbo.FactInventory

drop index PK_FactInventory on dbo.FactInventory

create clustered columnstore index PK_FactInventory
	on dbo.FactInventory 

-- **************************************************************
drop index NC_PK_FactSalesQuota on dbo.FactSalesQuota

drop index PK_FactSalesQuota on dbo.FactSalesQuota

create clustered columnstore index PK_FactSalesQuota
	on dbo.FactSalesQuota 

-- **************************************************************
drop index NC_PK_FactSales on dbo.FactSales;

drop index PK_FactSales on dbo.FactSales;

create clustered columnstore index PK_FactSales on dbo.FactSales;

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 *