Continuation from the previous 16 parts, starting from https://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:
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:
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;
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:
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:
to be continued with Clustered Columnstore Indexes – part 18 (“Basic Batch Mode Improvements”)