Clustered Columnstore Indexes – part 18 (“Basic Batch Mode Improvements”)

Posted on August 15, 2013 by Niko Neugebauer

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

This time lets see which improvements were delivered for the Batch Mode processing for the SQL Server 2014 CTP1. Batch Mode query execution was introduced in SQL Server 2012 specifically for the Columnstore Indexes. One of its differences to the traditional Row Mode is that it is processing data joined together in chunks of 1000 units instead of traditional 1 by 1, thus guaranteeing more CPU efficiency for the big amounts of data. Since Columnstore Indexes are being used specifically for the tables with a lot of millions of rows, this is exactly where the Batch Mode is shining.

In the SQL Server 2012 the batch mode implementation was very limited, with just a couple of Query Execution operators being supporting Batch Mode, and in order to get it activated one would have to completely rewrite the query, following the suggestion published by Microsoft.
Well SQL Server 2014 CTP1 is a very different beast. For real. Grab your cup of coffee, jump in – we are going for a very serious ride in the Batch Mode execution country

I downloaded and installed Contoso BI database on both of my Virtual Machines: on SQL Server 2012 and on the SQL Server 2014 CTP1.
Execute the following scripts on both of the Virtual Machines:

alter table dbo.[FactOnlineSales] DROP CONSTRAINT PK_FactOnlineSales_SalesKey

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 PK_FactOnlineSales_SalesKey

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

For SQL Server 2012, lets create a traditional clustered index, as well as the Nonclustered Columnstore Index:

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_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)

Now lets try to see how to we can get hold of a Batch Mode in SQL Server 2012, lets execute the following script, which actually is pretending to get the same results (total sum of the discounts) but in 2 different ways: 1st by writing a traditional query, and then following Microsoft’s suggestion to invoke the Batch Execution Mode by creating a CTE with group by and then summing the totals out of it:

-- Traditional Query:
select sum(DiscountAmount) as DiscountAmountSum
	from dbo.FactOnlineSales;

-- CTE Query:
with CteAmount as (
select sum(DiscountAmount) as Amount
	from dbo.FactOnlineSales
	group by StoreKey-- CustomerKey
)
select sum(Amount) as DiscountAmountSum
	from CteAmount;

-- Traditional Query:

select sum(DiscountAmount) as DiscountAmountSum

from dbo.FactOnlineSales;

-- CTE Query:

with CteAmount as (

select sum(DiscountAmount) as Amount

from dbo.FactOnlineSales

group by StoreKey-- CustomerKey

)

select sum(Amount) as DiscountAmountSum

from CteAmount;

Here are the actual execution plans for both of those queries:
Without looking any further, it is visible that on an environment with MAXDOP > 1, the second query with a CTE is much faster and the reason for it is the Batch Mode Execution which is happening on our “Columnstore Index Scan” and on the “Hash Match” operators in the 2nd execution plan. Here is a screenshot of the properties of the “Columnstore Index Scan” operator:

I have decided to discover the behavior of of those plans in the terms of the IO, and so I executed the following script before re-executing the previous script:

set statistics IO on

1	set statistics IO on

Here are the results that I have received, which show that in the terms of the IO the difference between the both queries is nothing more then 5%:

This all seems to be quite clear, but what about SQL Server CTP1 and the NonClustered Columnstore Indexes and Clustered Columnstore Indexes ?
I executed the very same scripts on the Virtual Machine SQL Server 2014 CTP1 and this what I have found:
Yes, you are looking at the correct plan – a simple & direct query is using Batch Mode even with the Nonclustered Columnstore Index ! The difference between execution plans in SQL Server 2012 & in SQL Server 2014 is that Steam Aggregate from 2012 became Hash Match (Partial Aggregate) in 2014 and that both operations (Index Scan & Steam Aggregate) are running fully in Batch Mode. Here you have the screenshot of the “Statistics IO”: I do not know why the number of reads is so significantly higher, since the difference between the sizes of the Columnstore Indexes were around 15 MB and it does not represent even 10% of the total space occupied by the Nonclustered Columnstore Index in that case.

It seems that the implementation of the Batch Mode in SQL Server 2014 was taken on a whole new level – on the level of what people like me are expecting from a good 1.0 version.
Goodbye query-rewritings – hello operational execution of “normal” queries. I am sure that there are thousands of bugs, and that there will be posts all over internet crying and blaming everyone, but for the moment let us celebrate that even query like this are running in a batch mode finally:

select count(*)
	from dbo.FactOnlineSales
</pre
Take it from here - such improvements right in the CTP1 version of SQL Server: this is amazing! :)

The last thing I was looking to measure is if this queries were really supported for the Clustered Columnstore Indexes and so I ran the following script:
<pre>
drop Index PK_FactOnlineSales
    on dbo.FactOnlineSales;

drop Index NC_PK_FactOnlineSales
    on dbo.FactOnlineSales;

Create Clustered Columnstore Index PK_FactOnlineSales
    on dbo.FactOnlineSales;

select count(*)

from dbo.FactOnlineSales

</pre

Take it from here - such improvements right in the CTP1 version of SQL Server: this is amazing! :)

The last thing I was looking to measure is if this queries were really supported for the Clustered Columnstore Indexes and so I ran the following script:

<pre>

drop Index PK_FactOnlineSales

on dbo.FactOnlineSales;

drop Index NC_PK_FactOnlineSales

on dbo.FactOnlineSales;

Create Clustered Columnstore Index PK_FactOnlineSales

on dbo.FactOnlineSales;

Then I ran the SELECT queries again and the execution plan was pretty much the same as for the NonClustered Columnstore Indexes, though the difference in performance were more visible – the first query got better results then the CTE one, though they are definitely not in orders of magnitude, but around 5-8% on my VMs’.

Wait, but then I took a more detailed look at the results and decided to hold my horses a little bit, take a look at the execution plan:

and now at the “Statistics IO”:
Did you notice “Table ‘Workfile’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.” on both of the execution statistics outputs…
I will make a guess, that this is exactly the Workfiles serving for the Hash Indexes & Hash Aggregates described at the respective MSDN article, but it is very interesting that CTP1 is making mention of them.

to be continued …

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

Posted on August 10, 2013 by Niko Neugebauer

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

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)

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 lets 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;

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 then 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 then 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)

create clustered Index PK_FactOnlineSales

on dbo.FactOnlineSales (OnlineSalesKey ASC)

WITH ( DATA_COMPRESSION = PAGE);

Create NonClustered Columnstore Index NC_PK_FactOnlineSales

-- **************************************

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;

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 …

Clustered Columnstore Indexes – part 16 (“Index Builds”)

Posted on August 9, 2013 by Niko Neugebauer

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

This post will be dedicated to the process of building Clustered Columnstore Indexes. I will be keep on playing with the Contoso BI database.

I have already mentioned in one of the first posts of this series, that the build process of the Clustered Columnstore Indexes includes a new improved mechanism which automatically determines the amount of memory available to the process, and thus effectively decides how many cores of the CPU will be used for the process – hence the quantity of memory.

We shall be working with FactOnlineSales table which has around 12 million rows, and our usual setup includes dropping all the related keys:

ALTER TABLE dbo.[FactOnlineSales] DROP CONSTRAINT [PK_FactOnlineSales_SalesKey]
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.[FactOnlineSales] DROP CONSTRAINT [PK_FactOnlineSales_SalesKey]

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]

Before taking a detailed look into the the Clustered Columnstore Index creation plan, let me explain the setup – in my case the virtual machine has 6 cores as well as the 8 GB of RAM, so I can effectively play. As you should already know, having a lot of fast CPU’s as well as big amount of memory is a must if you want to work effectively with Clustered Columnstore Indexes.

Lets create a Clustered Columnstore Index on our FactOnlineSales table:

Create Clustered Columnstore Index PK_FactOnlineSales on dbo.FactOnlineSales;

1	Create Clustered Columnstore Index PK_FactOnlineSales on dbo.FactOnlineSales;

It takes around 21 seconds to execute this statement on my Virtual Machine, and here is the execution plan that I did receive:

The most interesting thing for us at the moment are the properties of the “insert” box, lets check them out:
You can notice, that there is a new property in the SQL Server 2014 CTP1 – Effective Degree of Parallelism, which is described as a “max degree of parallelism during columnstore index build“. In our case the value corresponds the expected one, which is a 6, since we have not changed our default settings.

Lets change the amount of memory available to the SQL Server by setting it to 4 GB by executing the following script:

sp_configure 'max server memory (MB)', '4096'
Go
Reconfigure

sp_configure 'max server memory (MB)', '4096'

Reconfigure

Now let’s re-create our Clustered Columnstore Index and see what is different:

Create Clustered Columnstore Index PK_FactOnlineSales on dbo.FactOnlineSales
	with (DROP_EXISTING = ON);

1 2	Create Clustered Columnstore Index PK_FactOnlineSales on dbo.FactOnlineSales with (DROP_EXISTING = ON);

This time we have a process which took around 28 seconds on my Virtual Machine and here are the properties for the execution plan:
This time we have a 4 as a value for the Effective Degree of Parallelism property.
This is already different from the SQL Server 2012, in which for the Nonclustered Columnstore Indexes their creation or rebuild process would fail if there would not be enough memory. In SQL Serve 2014 we will be getting both Clustered as well as Nonclustered Columnstore Indexes using this mechanism and practically adopting memory usage to the currently available amounts. Should we lower the amounts of memory then SQL Server will progressively lower the MAXDOP until 1 effectively adopting the non-parallel process.

But what about the MAXDOP setting, can we actually set it to a lower number and see any effect? Lets try it out, with our current 4 GB of memory available, lets execute the following statement:

Create Clustered Columnstore Index PK_FactOnlineSales on dbo.FactOnlineSales
	with (DROP_EXISTING = ON, MAXDOP = 3);

1 2	Create Clustered Columnstore Index PK_FactOnlineSales on dbo.FactOnlineSales with (DROP_EXISTING = ON, MAXDOP = 3);

The result is quite clear – we are getting MAXDOP 3 as expected, but lets try to set it on 5 to see what kind of result will appear:

Create Clustered Columnstore Index PK_FactOnlineSales on dbo.FactOnlineSales
	with (DROP_EXISTING = ON, MAXDOP = 5);

1 2	Create Clustered Columnstore Index PK_FactOnlineSales on dbo.FactOnlineSales with (DROP_EXISTING = ON, MAXDOP = 5);

In this case we are still getting MAXDOP 4, as it was before, because the resources are not allowing us to get more MAXDOP.

One more interesting thing that I have noticed during my experiments with Clustered Columnstore Indexes is that the actual build process is kind of separated in 2 phases: on the first one we are just using 1 core of any available – and logically this is a phase where we create our dictionaries and then the 2nd phase when are using all our available/specified CPU cores to create the actual Clustered Columnstore Indexes.
Take a look this picture, you can clearly see both of the phases:

to be continued…

Clustered Columnstore Indexes – part 15 (“Partitioning Advanced”)

Posted on August 3, 2013 by Niko Neugebauer

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

This specific post is a direct continuation of the previous one from the part 14, so the database at the moment should have the FactOnlineSales table already partitioned.

Lets see what is actually happens when we shall start updating our partitioned database, but lets check on the structure of our Row Groups at the moment, by executing the following statement:

select *
	from sys.column_store_row_groups rg
	where rg.object_id = object_id('FactOnlineSales')
	order by rg.partition_number, rg.row_group_id;

select *

from sys.column_store_row_groups rg

where rg.object_id = object_id('FactOnlineSales')

order by rg.partition_number, rg.row_group_id;

It looks like we are actively using 3 partitions with a total of 14 segments distributed over them.

It is enough of the status-quo, lets update a number of rows belonging to the first partition:

update [dbo].[FactOnlineSales]
	set UpdateDate = GetDate()
	where DateKey = '2007-07-22 00:00:00.000';

update [dbo].[FactOnlineSales]

set UpdateDate = GetDate()

where DateKey = '2007-07-22 00:00:00.000';

Well, now it looks like we have got a new Row Group – a new Delta-Store, which was located on the partition that we have updated. Notice, that we have also have a number of rows deleted on each of the Segments, which makes a perfect sense since as I already explained before – the update operation is actually an Insert of a new rows into the Delta-Store plus an updated of the Deleted Bitmap with marking the respective original rows as deleted. The sum of the deleted rows over the 2nd partition is exactly equals to 11113 rows inserted into a new Delta-Store, and so this way we see that the operation was executed correctly.

Now let us try to update a different partition for the year 2008 (partition number 3), and lets see which result it will give us:

update [dbo].[FactOnlineSales]
	set UpdateDate = GetDate()
	where DateKey = '2008-08-16 00:00:00.000';

update [dbo].[FactOnlineSales]

set UpdateDate = GetDate()

where DateKey = '2008-08-16 00:00:00.000';

Lets check our internals situation: Awesome! We have updated a different partition and it has automatically created a new open Delta-Store which is local to this partition, and so now we have a situation of 2 Delta-Stores being simultaneously open for the same table.
Note: Partitioning has a lot of potential for the applications which require a lot of updates and inserts into the Clustered Columnstore Index – if you manage to do the inserts into different partitions there will be a great potential gain of speed and avoidance of the Clustered Columnstore Locking which as already tested uses IX and X locks on the active Delta-Stores.

It looks like these simple statements are actually touching on just one partition, lets see if it works for every partition and how the execution plan does look like. Lets update the year 2009 (last partition):

update [dbo].[FactOnlineSales]
	set UpdateDate = GetDate()
	where DateKey = '2009-07-07 00:00:00.000';

update [dbo].[FactOnlineSales]

set UpdateDate = GetDate()

where DateKey = '2009-07-07 00:00:00.000';

The results are as expected for the DMV’s, the new Delta-Store was created, but what about the execution statistics ? Scan count 3, logical reads 87748, physical reads 40, read-ahead reads 31660, … – well these numbers are quite big for a supposed one partition being updated, so lets take a look at the execution plan:

Looks fine actually Our partition number 4 was the only one being accessed, so those numbers should correspond to the sizes of the partition Segments. The execution mode is a Batch Mode so everything is fine here, and the operation itself is being paralleled and so the query is fine.

One other important mention deserves the “Sort” operator, which is being executed in the Row Mode, while Microsoft is already promoting that the Spill operation has been upgraded in SQL Server 2014 to be executed in Batch Mode. Maybe because there are just 32K rows being sorted, the batch mode decided to take a rest and only operations over 100K do Spill in Batch Mode, but I was quite disappointed to see it not being applied to the Clustered Columnstore Index update operation. Maybe this is a product of the CTP1 – I don’t know, but I will be testing this functionality in the upcoming blog posts more throghfully.

Locking escalation:
I decided to play with the Lock escalation on the Clustered Columnstore Indexes, and so I decided to update around 2/3 of the table, in the hope that this would produce enough locks to block the complete table.
Now I decided to set the escalation level explicitly to the table level:

ALTER TABLE dbo.FactOnlineSales  SET (LOCK_ESCALATION = TABLE)
GO

1 2	ALTER TABLE dbo.FactOnlineSales SET (LOCK_ESCALATION = TABLE) GO

after that I have executed the following t-sql statement in a different window:

begin tran

update dbo.FactOnlineSales
	set UpdateDate = GetDate()
	where DateKey < '2008-09-01'

begin tran

update dbo.FactOnlineSales

set UpdateDate = GetDate()

where DateKey < '2008-09-01'

but after updating almost 6.4 million rows this is what I have got with the following locking detection query:

-- What's being locked
SELECT dm_tran_locks.request_session_id,
       dm_tran_locks.resource_database_id,
       DB_NAME(dm_tran_locks.resource_database_id) AS dbname,
       CASE
           WHEN resource_type = 'object'
               THEN OBJECT_NAME(dm_tran_locks.resource_associated_entity_id)
           ELSE OBJECT_NAME(partitions.OBJECT_ID)
       END AS ObjectName,
       partitions.index_id,
       indexes.name AS index_name,
       dm_tran_locks.resource_type,
       dm_tran_locks.resource_description,
       dm_tran_locks.resource_associated_entity_id,
       dm_tran_locks.request_mode,
       dm_tran_locks.request_status
FROM sys.dm_tran_locks
LEFT JOIN sys.partitions ON partitions.hobt_id = dm_tran_locks.resource_associated_entity_id
JOIN sys.indexes ON indexes.OBJECT_ID = partitions.OBJECT_ID AND indexes.index_id = partitions.index_id
WHERE resource_associated_entity_id > 0
  AND resource_database_id = DB_ID()
ORDER BY request_session_id, resource_associated_entity_id

-- What's being locked

SELECT dm_tran_locks.request_session_id,

dm_tran_locks.resource_database_id,

DB_NAME(dm_tran_locks.resource_database_id) AS dbname,

CASE

WHEN resource_type = 'object'

THEN OBJECT_NAME(dm_tran_locks.resource_associated_entity_id)

ELSE OBJECT_NAME(partitions.OBJECT_ID)

END AS ObjectName,

partitions.index_id,

indexes.name AS index_name,

dm_tran_locks.resource_type,

dm_tran_locks.resource_description,

dm_tran_locks.resource_associated_entity_id,

dm_tran_locks.request_mode,

dm_tran_locks.request_status

FROM sys.dm_tran_locks

LEFT JOIN sys.partitions ON partitions.hobt_id = dm_tran_locks.resource_associated_entity_id

JOIN sys.indexes ON indexes.OBJECT_ID = partitions.OBJECT_ID AND indexes.index_id = partitions.index_id

WHERE resource_associated_entity_id > 0

AND resource_database_id = DB_ID()

ORDER BY request_session_id, resource_associated_entity_id

If you notice a big number of Row Groups being locked, its because some of them were created during update operation, while others were actually the product of several Update & Rollback attempts. Also, the Row Groups which have an U (Update Lock), are actually empty (no rows and they are marked at the OPEN state).
Don’t forget to rollback the transaction after the all requests were done.

Now I decided to set the escalation level explicitly to the partition (Auto) level:

ALTER TABLE dbo.FactOnlineSales  SET (LOCK_ESCALATION = AUTO)
GO

1 2	ALTER TABLE dbo.FactOnlineSales SET (LOCK_ESCALATION = AUTO) GO

In a new window lets do the same update on our table:

begin tran

update dbo.FactOnlineSales
	set UpdateDate = GetDate()
	where DateKey < '2008-09-01'

begin tran

update dbo.FactOnlineSales

set UpdateDate = GetDate()

where DateKey < '2008-09-01'

In this case we observe a different behavior: we still have our Row Groups U (Update) and X (Exclusive) locks, but we also have an IX (Intent Exclusive) lock on tow of our partitions (that we update completely, but note that partition 1 has no row at the moment), an IS (Intend Shared) lock on the partition we are updating a certain percentage of rows, and IX (Intent Exclusive) lock on the 4th partition which will not be updated at all.

Here comes the image showing the results of the content of the row groups, which were created as the result of this blog post – every time we update the segments – new Delta-Stores are being created, and since we execute rollback on our transactions, the Row Groups stay open until Tuple Mover should recycle them. Note: I have noticed a lot of times it does not happen correctly, but I think that it should be corrected before RTM.

Anyway I decided to correct this situation myself by invoking the following T-SQL on our Clustered Columnstore Index:

alter index PK_FactOnlineSales on [FactOnlineSales]
	REBUILD

1 2	alter index PK_FactOnlineSales on [FactOnlineSales] REBUILD

It seems that the partition level lock escalation works, though its functionality in a TABLE mode gets more granular locking, then the one on the PARTITION level. I would have expected it to be functioning otherwise, but lets wait until CTP2 before coming to any conclusions.

to be continued …

Clustered Columnstore Indexes – part 14 (“Partitioning”)

Posted on August 1, 2013 by Niko Neugebauer

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

Lets play with partitioning this time! Get the Contoso BI Database, plug it in and lets start playing!
We will be working with the FactOnlineSales table – first we need to drop all the relevant Primary and Foreign Keys:

ALTER TABLE dbo.[FactOnlineSales] DROP CONSTRAINT [PK_FactOnlineSales_SalesKey]
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.[FactOnlineSales] DROP CONSTRAINT [PK_FactOnlineSales_SalesKey]

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]

Now let us setup all the stuff necessary for the partitioning (note, that I expect you to pre-allocate your Primary FileGroup and T-Log sizes and growth, and since they are basic items, which are not the topic of this blogpost, I do not write about them.
I add 4 new Filegroups with each one containing 1 file to our Contoso BI Database – the names of those filegroups reflect their purpose (1st one is for the archival data which happened before the 2007, and then the filegroups for each of the years with 2009 containing the data for the 2009+ years. Then I created a new partitioning function and a new partitioning scheme, which is mapping the partitioning function to the respective filegroups.

-- Add 4 new filegroups to our database
alter database ContosoRetailDW
	add filegroup OldColumnstoreData;
GO
alter database ContosoRetailDW
	add filegroup Columnstore2007;
GO
alter database ContosoRetailDW
	add filegroup Columnstore2008;
GO
alter database ContosoRetailDW
	add filegroup Columnstore2009;
GO

-- Add 1 datafile to each of the respective filegroups
alter database ContosoRetailDW 
add file 
(
    NAME = 'old_data',
    FILENAME = 'C:\Data\MSSQL\DATA\old_data.ndf',
    SIZE = 10MB,
    FILEGROWTH = 125MB
) to Filegroup [OldColumnstoreData];
GO

alter database ContosoRetailDW 
add file 
(
    NAME = '2007_data',
    FILENAME = 'C:\Data\MSSQL\DATA\2007_data.ndf',
    SIZE = 500MB,
    FILEGROWTH = 125MB
) TO Filegroup Columnstore2007;
GO

alter database ContosoRetailDW 
add file 
(
    NAME = '2008_data',
    FILENAME = 'C:\Data\MSSQL\DATA\2008_data.ndf',
    SIZE = 500MB,
    FILEGROWTH = 125MB
) to Filegroup Columnstore2008;
GO

alter database ContosoRetailDW 
add file 
(
    NAME = '2009_data',
    FILENAME = 'C:\Data\MSSQL\DATA\2009_data.ndf',
    SIZE = 500MB,
    FILEGROWTH = 125MB
) TO Filegroup Columnstore2009;
GO

-- Create the Partitioning scheme
create partition function pfOnlineSalesDate (datetime)
AS RANGE RIGHT FOR VALUES ('2007-01-01', '2008-01-01', '2009-01-01');


-- Define the partitioning function for it, which will be mapping data to each of the corresponding filegroups
create partition scheme ColumstorePartitioning 
	AS PARTITION pfOnlineSalesDate
TO ( OldColumnstoreData, Columnstore2007, Columnstore2008, Columnstore2009 );

-- Add 4 new filegroups to our database

alter database ContosoRetailDW

add filegroup OldColumnstoreData;

alter database ContosoRetailDW

add filegroup Columnstore2007;

alter database ContosoRetailDW

add filegroup Columnstore2008;

alter database ContosoRetailDW

add filegroup Columnstore2009;

-- Add 1 datafile to each of the respective filegroups

alter database ContosoRetailDW

add file

(

NAME = 'old_data',

FILENAME = 'C:\Data\MSSQL\DATA\old_data.ndf',

SIZE = 10MB,

FILEGROWTH = 125MB

) to Filegroup [OldColumnstoreData];

alter database ContosoRetailDW

add file

(

NAME = '2007_data',

FILENAME = 'C:\Data\MSSQL\DATA\2007_data.ndf',

SIZE = 500MB,

FILEGROWTH = 125MB

) TO Filegroup Columnstore2007;

alter database ContosoRetailDW

add file

(

NAME = '2008_data',

FILENAME = 'C:\Data\MSSQL\DATA\2008_data.ndf',

SIZE = 500MB,

FILEGROWTH = 125MB

) to Filegroup Columnstore2008;

alter database ContosoRetailDW

add file

(

NAME = '2009_data',

FILENAME = 'C:\Data\MSSQL\DATA\2009_data.ndf',

SIZE = 500MB,

FILEGROWTH = 125MB

) TO Filegroup Columnstore2009;

-- Create the Partitioning scheme

create partition function pfOnlineSalesDate (datetime)

AS RANGE RIGHT FOR VALUES ('2007-01-01', '2008-01-01', '2009-01-01');

-- Define the partitioning function for it, which will be mapping data to each of the corresponding filegroups

create partition scheme ColumstorePartitioning

AS PARTITION pfOnlineSalesDate

TO ( OldColumnstoreData, Columnstore2007, Columnstore2008, Columnstore2009 );

Now that we have everything set up, lets try to create our Clustered Columnstore Index by using the partitioning scheme that we have just created:

Create Clustered Columnstore Index PK_FactOnlineSales on dbo.FactOnlineSales
	 ON ColumstorePartitioning (DateKey);

1 2	Create Clustered Columnstore Index PK_FactOnlineSales on dbo.FactOnlineSales ON ColumstorePartitioning (DateKey);

Msg 35316, Level 16, State 1, Line 1
CREATE INDEX statement failed because a columnstore index must be partition-aligned with the base table. Create the columnstore index using the same partition function and same (or equivalent) partition scheme as the base table. If the base table is not partitioned, create a nonpartitioned columnstore index.
Say what ? Does it mean that we have to create a partitioning for the traditional clustered index before we advance for creating partitioning for the clustered Columnstore index ? That might make enough sense for a Nonclustered Columnstore, but this is gibberish when we are talking about the Clustered Columnstore Index. Yes, things are working exactly this way and we shall have to accept it, especially since the partition alignment is a kind of an important topic from a lot of different angles, especially in the future when the nonclustered traditional indexes should become available for the tables with Clustered Columnstore Index.

Before advancing with creation of a traditional clustered index on the FactOnlineSales table, I thought that it would be a good idea to have some measures of comparison with a non-partitioned Clustered Columnstore Index and so I took up some statistics from the principal DMV’s available – sys.column_store_row_groups, since I believe (and I have confirmed) that the algorithm is stable between partitioned and non-partitioned tables, and the only thing which interests me at the moment is the compression results:

Create Clustered Columnstore Index PK_FactOnlineSales on dbo.FactOnlineSales;

1	Create Clustered Columnstore Index PK_FactOnlineSales on dbo.FactOnlineSales;

So I took off the statistics from the above mentioned DMV and decided to move on with creation of a traditional clustered index on the FactOnlineSales table and then we should be able to finally create our own partitioned clustered columnstore index:

-- Drop our Clustered Non-partitioned Columnstore Index, because we can't recreate directly for this set of options (which is a bug, if you ask me).
drop Index PK_FactOnlineSales on dbo.FactOnlineSales;

-- Creation of a traditional Clustered Index
Create Clustered Index PK_FactOnlineSales 
	on dbo.FactOnlineSales (DateKey)
        with (DROP_EXISTING = ON)
	 ON ColumstorePartitioning (DateKey);

-- Create Partitioned Clustered Columnstore Index
Create Clustered Columnstore Index PK_FactOnlineSales on dbo.FactOnlineSales
     with (DROP_EXISTING = ON)
	 ON ColumstorePartitioning (DateKey);

-- Drop our Clustered Non-partitioned Columnstore Index, because we can't recreate directly for this set of options (which is a bug, if you ask me).

drop Index PK_FactOnlineSales on dbo.FactOnlineSales;

-- Creation of a traditional Clustered Index

Create Clustered Index PK_FactOnlineSales

on dbo.FactOnlineSales (DateKey)

with (DROP_EXISTING = ON)

ON ColumstorePartitioning (DateKey);

-- Create Partitioned Clustered Columnstore Index

Create Clustered Columnstore Index PK_FactOnlineSales on dbo.FactOnlineSales

with (DROP_EXISTING = ON)

ON ColumstorePartitioning (DateKey);

Here is the graphic which speaks for itself, you can see the difference in number of Segments and accordingly the space occupied between Non-Partitioned and Partitioned Clustered Columnstore Indexes:

Knowing Clustered Columnstore behaviour a little bit, I decided to go and rebuild the Partitioned Clustered Columnstore Index a couple more times, and here are the graphic results of the results that I was able to obtain, while using the command:

alter Index PK_FactOnlineSales on dbo.FactOnlineSales
	REBUILD;

1 2	alter Index PK_FactOnlineSales on dbo.FactOnlineSales REBUILD;

So what actually is happen there – it looks like a non-partitioned index is winning the battle in the terms of the size and in the terms of the space occupied by segments: they look quite smooth in the Non-Partitioned Clustered Columnstore and they look with Ups and great downs in the Partitioned Clustered Columnstore Index case.
This make a lot of sense and this is a natural process – this is partitioning. Every time we see a major drop with a consequent rise of the size of consequetive Segments, we should be considering this point as end of a partition. Since Clustered Columnstore Index is created inside each of the physically separated partitions (files & filegroups), it is perfectly natural that the at those critical points the number of rows can be cut quite dramatically.

But that is not all that happens there, take a look at the number of Segments – it looks like it is going steady down with each consecutive Rebiuld – should we believe that the Clustered Columnstore Index actually improves rows distribution with each rebuild?

A very interesting detail – I decided to come back and revisit the creation process – so I dropped the partitioned Clustered Columnstore Index and created it again – and this time I have had no error messages or other sad transmissions: everything worked perfectly.
I guess that there is an alignment meta-information stored somewhere inside…

drop Index PK_FactOnlineSales on dbo.FactOnlineSales;

-- Create Partitioned Clustered Columnstore Index
Create Clustered Columnstore Index PK_FactOnlineSales on dbo.FactOnlineSales
	 ON ColumstorePartitioning (DateKey);

drop Index PK_FactOnlineSales on dbo.FactOnlineSales;

-- Create Partitioned Clustered Columnstore Index

Create Clustered Columnstore Index PK_FactOnlineSales on dbo.FactOnlineSales

ON ColumstorePartitioning (DateKey);

At this point I decided to take control of the compression and I thought that since most of the 2007 year data was used very rarely, I should point it to the newest COMUNSTORE_ARCHIVE compression, but before it check out the data from my current sys.column_store_row_groups DMV, by executing the following statement:

select OBJECT_NAME(p.object_id) as TableName, p.partition_id, p.data_compression, p.data_compression_desc, rg.state, rg.state_description, 
	rg.total_rows as SegmentRows,  p.rows as PartitionRows, rg.size_in_bytes as 'Size'
	from sys.partitions p
		inner join sys.column_store_row_groups rg
			on p.object_id = rg.object_id and p.partition_number = rg.partition_number 
	where p.object_id = object_id('dbo.FactOnlineSales')

select OBJECT_NAME(p.object_id) as TableName, p.partition_id, p.data_compression, p.data_compression_desc, rg.state, rg.state_description,

rg.total_rows as SegmentRows, p.rows as PartitionRows, rg.size_in_bytes as 'Size'

from sys.partitions p

inner join sys.column_store_row_groups rg

on p.object_id = rg.object_id and p.partition_number = rg.partition_number

where p.object_id = object_id('dbo.FactOnlineSales')

-- Rebuild the oldest partition which has some data with a different compression method:
alter Index PK_FactOnlineSales on dbo.FactOnlineSales
	REBUILD PARTITION = 2 
		WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);

-- Rebuild the oldest partition which has some data with a different compression method:

alter Index PK_FactOnlineSales on dbo.FactOnlineSales

REBUILD PARTITION = 2

WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);

Lets check out our results:

Surely one can see that the 2nd partition indeed has changed its compression algorithm and it is quite visible that the respective sizes of the Segments went also down. This is a nice proof of the partitioning funcionality that allows us to use different Columnstore compression algorithms for the same table.

And to add a little bit more fun, for the end of this part on the partitioning, lets try to rebuild our clustered Columnstore Index again, but this time doing it as an online operation, since Microsoft has invested vastly into Online operation in the last 2 versions of the SQL Server (including LOB’s for SQL Server 2012 and including support for online rebuild on a partition level for the upcoming SQL Server 2014l.

alter Index PK_FactOnlineSales on dbo.FactOnlineSales
	REBUILD 
		WITH (DATA_COMPRESSION = COLUMNSTORE, ONLINE = ON);

alter Index PK_FactOnlineSales on dbo.FactOnlineSales

REBUILD

WITH (DATA_COMPRESSION = COLUMNSTORE, ONLINE = ON);

This statement produces the following output:
Msg 35328, Level 16, State 1, Line 1
ALTER INDEX REBUILD statement failed because the ONLINE option is not allowed when rebuilding a columnstore index. Rebuild the columnstore index without specifying the ONLINE option.
Which basically says that there is no Online rebuild for the Clustered Columnstore Indexes. I have tried rebuilding just one partition online, but the error message stays the same – no Online operations for the Clustered Columnstore Indexes in this version.

To be continued …

Clustered Columnstore Indexes – part 13 (“Dictionaries Analyzed”)

Posted on July 30, 2013 by Niko Neugebauer

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

This time I decided to play a little bit more with Clustered Columnstore dictionaries in order to understand them a little bit better.

Continuing working with Contoso BI Database but this time I am kicking off with in a different way – by executing the following script which will help me to find the tables which might be suitable for the Clustered Columnstore Indexes (with over 1 Million rows) and it provides with some useful information about those parts where Clustered Columnstore are imposing some limits:
Warning: this is a code in development and it is based on the current CTP1 version of SQL Server 2014.
Warning 2: indeed you can convert any table with less then 1 Million rows, and some tables might greatly benefit from it, but this very script and this blog post is focusing just on the big tables.

-- Returns tables suggested for using Clustered Columnstore for the Datawarehouse environments
select object_schema_name( t.object_id ) as 'Schema'
	, object_name (t.object_id) as 'Table'
	, sum(p.rows) as 'Row Count'
	, (select count(*) from sys.columns as col
		where t.object_id = col.object_id ) as 'Cols Count'
	, (select sum(col.max_length) 
			from sys.columns as col
			join sys.types as tp
			on col.system_type_id = tp.system_type_id
			where t.object_id = col.object_id 
	  ) as 'Cols Max Length'
	, (select count(*) 
			from sys.columns as col
			join sys.types as tp
			on col.system_type_id = tp.system_type_id
			where t.object_id = col.object_id and 
				 (UPPER(tp.name) in ('TEXT','NTEXT','TIMESTAMP','HIERARCHYID','SQL_VARIANT','XML','GEOGRAPHY','GEOMETRY') OR
				  (UPPER(tp.name) in ('VARCHAR','NVARCHAR') and (col.max_length = 8000 or col.max_length = -1)) 
				 )
	   ) as 'Unsupported Columns'
	, (select count(*)
			from sys.objects
			where type = 'PK' AND parent_object_id = t.object_id ) as 'Primary Key'
	, (select count(*)
			from sys.objects
			where type = 'F' AND parent_object_id = t.object_id ) as 'Foreign Keys'
	, (select count(*)
			from sys.objects
			where type in ('UQ','D','C') AND parent_object_id = t.object_id ) as 'Constraints'
	, (select count(*)
			from sys.objects
			where type in ('TA','TR') AND parent_object_id = t.object_id ) as 'Triggers'
	, t.is_tracked_by_cdc as 'CDC'
	, t.is_memory_optimized as 'Hekaton'
	, t.is_replicated as 'Replication'
	, coalesce(t.filestream_data_space_id,0,1) as 'FileStream'
	, t.is_filetable as 'FileTable'
	from sys.tables t
	inner join sys.partitions as p 
		ON t.object_id = p.object_id
	where p.data_compression in (0,1,2) -- None, Row, Page
	group by t.object_id, t.is_tracked_by_cdc,  t.is_memory_optimized, t.is_filetable, t.is_replicated, t.filestream_data_space_id
	having sum(p.rows) > 1000000
	order by sum(p.rows) desc

-- Returns tables suggested for using Clustered Columnstore for the Datawarehouse environments

select object_schema_name( t.object_id ) as 'Schema'

, object_name (t.object_id) as 'Table'

, sum(p.rows) as 'Row Count'

, (select count(*) from sys.columns as col

where t.object_id = col.object_id ) as 'Cols Count'

, (select sum(col.max_length)

from sys.columns as col

join sys.types as tp

on col.system_type_id = tp.system_type_id

where t.object_id = col.object_id

) as 'Cols Max Length'

, (select count(*)

from sys.columns as col

join sys.types as tp

on col.system_type_id = tp.system_type_id

where t.object_id = col.object_id and

(UPPER(tp.name) in ('TEXT','NTEXT','TIMESTAMP','HIERARCHYID','SQL_VARIANT','XML','GEOGRAPHY','GEOMETRY') OR

(UPPER(tp.name) in ('VARCHAR','NVARCHAR') and (col.max_length = 8000 or col.max_length = -1))

)

) as 'Unsupported Columns'

, (select count(*)

from sys.objects

where type = 'PK' AND parent_object_id = t.object_id ) as 'Primary Key'

, (select count(*)

from sys.objects

where type = 'F' AND parent_object_id = t.object_id ) as 'Foreign Keys'

, (select count(*)

from sys.objects

where type in ('UQ','D','C') AND parent_object_id = t.object_id ) as 'Constraints'

, (select count(*)

from sys.objects

where type in ('TA','TR') AND parent_object_id = t.object_id ) as 'Triggers'

, t.is_tracked_by_cdc as 'CDC'

, t.is_memory_optimized as 'Hekaton'

, t.is_replicated as 'Replication'

, coalesce(t.filestream_data_space_id,0,1) as 'FileStream'

, t.is_filetable as 'FileTable'

from sys.tables t

inner join sys.partitions as p

ON t.object_id = p.object_id

where p.data_compression in (0,1,2) -- None, Row, Page

group by t.object_id, t.is_tracked_by_cdc, t.is_memory_optimized, t.is_filetable, t.is_replicated, t.filestream_data_space_id

having sum(p.rows) > 1000000

order by sum(p.rows) desc

These are the results that I have received when executing this script on my Contoso BI Database:

Basically, the results are telling me that I have 5 tables which are candidates to be converted to use the Clustered Columnstore Index: FactOnlineSales, FactInventory, FactSalesQuota, FactSales, FactStrategyPlan. Besides that we can see the number of columns that every table has as well as the number of the respective Primary and Foreign Keys, plus some other columns which are all fine for our exercise.

Lets go forward and drop all the respective Primary as well as the Foreign Keys:

-- Drop Primary Keys:
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]

-- Drop Foreign Keys:
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]

-- Drop Primary Keys:

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]

-- Drop Foreign Keys:

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]

Now lets create the respective Clustered Colunstore Indexes for each of those tables:

Create Clustered Columnstore Index PK_FactOnlineSales 
	on dbo.FactOnlineSales 
	WITH (DATA_COMPRESSION = COLUMNSTORE);

Create Clustered Columnstore Index PK_FactStrategyPlan 
	on dbo.FactStrategyPlan 
	WITH (DATA_COMPRESSION = COLUMNSTORE);

Create Clustered Columnstore Index PK_FactSales 
	on dbo.FactSales 
	WITH (DATA_COMPRESSION = COLUMNSTORE);

Create Clustered Columnstore Index PK_FactInventory
	on dbo.FactInventory 
	WITH (DATA_COMPRESSION = COLUMNSTORE);

Create Clustered Columnstore Index PK_FactSalesQuota
	on dbo.FactSalesQuota 
	WITH (DATA_COMPRESSION = COLUMNSTORE);

Create Clustered Columnstore Index PK_FactOnlineSales

on dbo.FactOnlineSales

WITH (DATA_COMPRESSION = COLUMNSTORE);

Create Clustered Columnstore Index PK_FactStrategyPlan

on dbo.FactStrategyPlan

WITH (DATA_COMPRESSION = COLUMNSTORE);

Create Clustered Columnstore Index PK_FactSales

on dbo.FactSales

WITH (DATA_COMPRESSION = COLUMNSTORE);

Create Clustered Columnstore Index PK_FactInventory

on dbo.FactInventory

WITH (DATA_COMPRESSION = COLUMNSTORE);

Create Clustered Columnstore Index PK_FactSalesQuota

on dbo.FactSalesQuota

WITH (DATA_COMPRESSION = COLUMNSTORE);

To analyze the situation with the dictionaries I have decided to run the following query, which would give me back the information about dictionary sizes for each of the tables:

select object_name(object_id), dictionary_id
	, count(*) as 'Number of Dictionaries'
	, sum(entry_count) as 'Entry Count'
	, min(on_disk_size) as 'Min Size'
	, max(on_disk_size) as 'Max Size'
	, avg(on_disk_size) as 'Avg Size'
	from sys.column_store_dictionaries dict
		join sys.partitions part
			on dict.hobt_id = part.hobt_id
	group by object_id, dictionary_id 
	order by object_name(object_id), dictionary_id

select object_name(object_id), dictionary_id

, count(*) as 'Number of Dictionaries'

, sum(entry_count) as 'Entry Count'

, min(on_disk_size) as 'Min Size'

, max(on_disk_size) as 'Max Size'

, avg(on_disk_size) as 'Avg Size'

from sys.column_store_dictionaries dict

join sys.partitions part

on dict.hobt_id = part.hobt_id

group by object_id, dictionary_id

order by object_name(object_id), dictionary_id

The results are here (already inserted into an Excel file):

According to the diciontary_id, here is an image of the development of the number of dictionaries available:

Maybe it is just me, but I see some very clear tendency of the number of dictionaries decreasing according to the dictionary_id progression. Since there is no information at all what should be considered a Primary (Global) dictionary and what is a Secondary one, I would guess that the first available dictionary_id is the Primary dictionary, based on the number of the dictionaries available.
Note: of course this is a speculation and just a one-man in the middle-of-other-things-research

Warning: FactInventory & FactSales are represented on the right scale.

If we look at the Number of Entries inside of a dictionary, aka Entry Count results, we might notice the following tendency – the very first dictionary looks like it is a kind of displaced, but starting with a dictionary_id = 1, the number of entries definitely goes up to dive down, which also points out at the location of the Primary dictionary – the first one which contains the smallest amount of entries -> dictionary_id = 0.

Other thoughts:
From the tables I have analyzed, there were different amount of space that different type of dictionaries have occupied, and from which I believe that the absolute minimum for the very first dictionary_id is 60 bytes, but what makes things even more interesting is that for the second and the third levels of dictionaries_id (which are =1 and =2 respectfully), the minimum number of bytes goes down to 56 bytes, which means that there is a 4 bytes difference between the first level of dictionaries_id and the consequent ones. I wish I had any explanation for all this stuff, but lets hope that someone will discover it quite soon, or that Microsoft shall publish more internal information about the Clustered Columnstore Indexes.

to be continued …

Clustered Columnstore Indexes – part 12 (“Compression Dive”)

Posted on July 20, 2013 by Niko Neugebauer

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

In the previous blog posts of this series on Clustered Columnstore Indexes, we have created some Clustered Columnstore Indexes and NonClustered Columnstore Indexes, we have compared different type of compressions and we have even considered some of the internals of the Columnstore Indexes. Now it is time to play with some data and see how what we can actually see and discover by looking at the internal information of the SQL Server 2014 CTP 1.

Once again, in order to play with the scripts you need to download and restore the Contoso BI Database. As in the previous post, I am dropping all the foreign and primary keys in order to be able to start creating Columnstore Indexes :

-- Drop all Foreign Keys
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]

-- Drop the Primary Key
ALTER TABLE dbo.[FactStrategyPlan] DROP CONSTRAINT [PK_FactStrategyPlan_StrategyPlanKey]

-- Drop all Foreign Keys

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]

-- Drop the Primary Key

ALTER TABLE dbo.[FactStrategyPlan] DROP CONSTRAINT [PK_FactStrategyPlan_StrategyPlanKey]

Now lets create a Clustered Columnstore Index and let us see what we can see inside of the Columnstore related DMV’s:

-- Create Clustered Columnstore Index
Create Clustered Columnstore Index PK_FactStrategyPlan 
	on dbo.FactStrategyPlan 
	WITH (DATA_COMPRESSION = COLUMNSTORE);

-- Get the dictionaries
select *
	from sys.column_store_dictionaries

-- Get the Segments
select *
	from sys.column_store_segments

-- Get all the Row Groups
select *
	from sys.column_store_row_groups

-- Create Clustered Columnstore Index

Create Clustered Columnstore Index PK_FactStrategyPlan

on dbo.FactStrategyPlan

WITH (DATA_COMPRESSION = COLUMNSTORE);

-- Get the dictionaries

select *

from sys.column_store_dictionaries

-- Get the Segments

select *

from sys.column_store_segments

-- Get all the Row Groups

select *

from sys.column_store_row_groups

Here are the screenshots of the respective results, already placed in the Excel tables:

Dictionaries:

Column Store Segments:

Row Groups:

What can we see there ? From 11 columns available inside of the dbo.FactStrategyPlan table, only 9 columns have deserved their right to have a dictionary. The very first columns is an Integer Identity and so far I have seen, Columnstore Indexes does not create any dictionaries for this types of columns. Another columns which did not receive any dictionary was a column Number 8, which has a MONEY datatype. At this point lets just remember it and move forward.
The second DMV returns us information about data distribution between compressed Segments. We can see that the columns 1 & 8 don’t have any primary (I assume that it means a global dictionary) dictionaries associated. Notice, that no Columns have any secondary dictionary at all (I assume that this means a local dictionary).
The last of the 3 consulted views (sys.column_store_row_groups) is showing us information about all available Row Groups, and in our case they are all compressed Segments.

That is all fine, but what about the NonClustered Columnstore Indexes ? Lets create the very same covering Nonclustered Columnstore Index, which will include every column of the table and see what the SQL Server DMV’s will show us:

-- Drop existing Clustered Index
Drop Index PK_FactStrategyPlan on dbo.FactStrategyPlan;
 
-- Create a Nonclustered Columnstore Index:
Create NonClustered Columnstore Index PK_FactStrategyPlan 
	on dbo.FactStrategyPlan (StrategyPlanKey, Datekey, EntityKey, ScenarioKey, AccountKey, CurrencyKey, ProductCategoryKey, Amount, ETLLoadID, LoadDate, UpdateDate)
	WITH ( DATA_COMPRESSION = COLUMNSTORE);

-- Get the dictionaries
select *
	from sys.column_store_dictionaries

-- Get the Segments
select *
	from sys.column_store_segments

-- Get all the Row Groups
select *
	from sys.column_store_row_groups

-- Drop existing Clustered Index

Drop Index PK_FactStrategyPlan on dbo.FactStrategyPlan;

-- Create a Nonclustered Columnstore Index:

Create NonClustered Columnstore Index PK_FactStrategyPlan

on dbo.FactStrategyPlan (StrategyPlanKey, Datekey, EntityKey, ScenarioKey, AccountKey, CurrencyKey, ProductCategoryKey, Amount, ETLLoadID, LoadDate, UpdateDate)

WITH ( DATA_COMPRESSION = COLUMNSTORE);

-- Get the dictionaries

select *

from sys.column_store_dictionaries

-- Get the Segments

select *

from sys.column_store_segments

-- Get all the Row Groups

select *

from sys.column_store_row_groups

Dictionaries:

Column Store Segments:

Row Groups:

The results from the DMV’s of the Nonclustered Columnstore Index are very self-explanatory: The dictionaries are absolutely identical, and so from here I assume that the data analysis is the very same process that has been used for the Clustered Columnstore Indexes.

The segments are different to the Clustered Columnstore Indexes: first of all we have a 12th column, which actually does not exist in the original table. What does it serve for ? In our case this is a unique identifier to serve as a connection between the clustered and so is the occupied space, like there is one for the traditional Nonclustered Indexes build on a HEAP.

Naturally the Row Groups DMV is showing us the aggregated info which is involving all Segments, because we don’t have any other type of the Row Groups.

Important Observation: at this point it is quite visible that the dictionary is exactly the same but what it looks like the compression itself is different, but let’s not forget that we are dealing with a HEAP table in the case of the Nonclustered Columnstore Index. To get a better insight, lets do the whole process from the start, this time creating a traditional Clustered Index with a Nonclustered Columnstore Index and then compare it to a freshly created Clustered Columnstore Index:

-- Drop existing Columnstore Index
Drop Index PK_FactStrategyPlan on dbo.FactStrategyPlan;

-- Create a traditional Unique Clustered Index
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)
	WITH ( DATA_COMPRESSION = COLUMNSTORE);

-- Get the dictionaries
select *
	from sys.column_store_dictionaries

-- Get the Segments
select *
	from sys.column_store_segments

-- Get all the Row Groups
select *
	from sys.column_store_row_groups

-- Drop existing Columnstore Index

Drop Index PK_FactStrategyPlan on dbo.FactStrategyPlan;

-- Create a traditional Unique Clustered Index

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)

WITH ( DATA_COMPRESSION = COLUMNSTORE);

-- Get the dictionaries

select *

from sys.column_store_dictionaries

-- Get the Segments

select *

from sys.column_store_segments

-- Get all the Row Groups

select *

from sys.column_store_row_groups

As expected, the dictionaries stays the same – untouched.
For the sys.column_store_segments DMV – also, as expected the 12th columns is completely gone and the results are quite different: The compression results are much better, which definitely should be attributed to the sorted order of the data, since we have built a traditional Clustered Index before creating a Nonclustered Columnstore one.

Lets run the following script to create a Clustered Columnstore to compare the results with the combination of the traditional Clustered Index + Nonclustered Columnstore Index:

-- Drop Nonclustered Columnstore Index:
Drop Index NC_PK_FactStrategyPlan on dbo.FactStrategyPlan;

-- Create Clustered Columnstore Index
Create Clustered Columnstore Index PK_FactStrategyPlan 
	on dbo.FactStrategyPlan 
	WITH (DROP_EXISTING = ON, DATA_COMPRESSION = COLUMNSTORE);

-- Get the dictionaries
select *
	from sys.column_store_dictionaries

-- Get the Segments
select *
	from sys.column_store_segments

-- Get all the Row Groups
select *
	from sys.column_store_row_groups

-- Drop Nonclustered Columnstore Index:

Drop Index NC_PK_FactStrategyPlan on dbo.FactStrategyPlan;

-- Create Clustered Columnstore Index

Create Clustered Columnstore Index PK_FactStrategyPlan

on dbo.FactStrategyPlan

WITH (DROP_EXISTING = ON, DATA_COMPRESSION = COLUMNSTORE);

-- Get the dictionaries

select *

from sys.column_store_dictionaries

-- Get the Segments

select *

from sys.column_store_segments

-- Get all the Row Groups

select *

from sys.column_store_row_groups

The results are absolutely equal, which makes perfect sense. At this point I am quite confident that this combination (Clustered + Nonclustered_Columntore = Clustered_Columnstore). Viewing the results of the DMVs proves that in my opinion. I have tried a couple of other tables and always have obtained the exact results.

At this point I decided to turn my attention to the new type of compression that exists in the SQL Server 2014 CTP1 – Columnstore Archive:

-- Create a traditional Unique Clustered Index
create unique clustered Index PK_FactStrategyPlan 
    on dbo.FactStrategyPlan (StrategyPlanKey ASC)
    WITH (DROP_EXISTING = ON, DATA_COMPRESSION = PAGE);

-- Create Clustered Columnstore Index
Create Clustered Columnstore Index PK_FactStrategyPlan 
	on dbo.FactStrategyPlan 
	WITH (DROP_EXISTING = ON, DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);

-- Get the dictionaries
select *
	from sys.column_store_dictionaries

-- Get the Segments
select *
	from sys.column_store_segments

-- Get all the Row Groups
select *
	from sys.column_store_row_groups

-- Create a traditional Unique Clustered Index

create unique clustered Index PK_FactStrategyPlan

on dbo.FactStrategyPlan (StrategyPlanKey ASC)

WITH (DROP_EXISTING = ON, DATA_COMPRESSION = PAGE);

-- Create Clustered Columnstore Index

Create Clustered Columnstore Index PK_FactStrategyPlan

on dbo.FactStrategyPlan

WITH (DROP_EXISTING = ON, DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);

-- Get the dictionaries

select *

from sys.column_store_dictionaries

-- Get the Segments

select *

from sys.column_store_segments

-- Get all the Row Groups

select *

from sys.column_store_row_groups

Here are the results, already stacked with the comparison between Columnstore and Columnstore Archive compression:

Analysis: The Columnstore Archival process is not only just a modified LZ77 compression. The dictionary size is clearly differs from the default Columnstore compression. In the case of the Columnstore Archive compression, for the majority of the columns the size of the dictionary is somehow bigger then in the default Columnstore compression by the precise amount of 20 bytes. The only case when the Columnstore dictionary is bigger then the Columnstore Archive – is the column number 3: EntityKey int. The difference is so significant in this case, that there is something going on and at this point I am not sure what exactly is. I am definitely looking forward into trying to get a look into the dictionary values to understand this difference. This column is an Integer, which in a lot of cases does not get any dictionary at all, but since we have just 306 distinct values it is clearly a column that can get a good compression.

If we look at the compressed segments we can also clearly notice the difference in the way that the data has been shuffled between the Segments. I can only guess, that some of the difference in the compression is achieved by this move of the data between different Segments. We shall see

Row Count for each of the Segments:

to be continued …

Nikoport

SQL Server, Community, Development and Web adventures

Clustered Columnstore Indexes – part 18 (“Basic Batch Mode Improvements”)

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

Clustered Columnstore Indexes – part 16 (“Index Builds”)

Clustered Columnstore Indexes – part 15 (“Partitioning Advanced”)

Clustered Columnstore Indexes – part 14 (“Partitioning”)

Clustered Columnstore Indexes – part 13 (“Dictionaries Analyzed”)

Clustered Columnstore Indexes – part 12 (“Compression Dive”)