SQL 2016 – Updatable Columnstore Index finally OLTP and DW workloads delivered

So SQL 2016 comes with updatable non clustered columnstore index. Are you wondering why this is a big deal. It is and here’s why

In SQL 2012 the Non clustered Columnstore index was introduced but was severely handicapped by the fact that once implemented the table was read only.

In SQL 2014 the Clustered Columnstore index was introduced that offered better compression and comparable performance to a Non Clustered Columnstore index but this also has several limitations such not being able to implement Primary key – Foreign Key relationships etc. which was not practical from a relational database stand point. Especially when most clients who wanted to use this feature in order to improve performance of already existing TB scale database. It did however allow DML on the table.

In SQL 2016 we now have the ability to insert data into a table with a Non Clustered Columnstore index this gives us the power of performing DW workloads without compromising the OLTP workloads seek performance. Now you could argue that a Clustered columnstore index would provide the best of both worlds but you will see in the screenshots below that the clustered columnstore index doesn’t really match up to a traditional index when it comes to seek. Now this would have been alright if we were allowed to create additional indexes on the table alongside the clustered columnstore index however that is not the case.

So SQL 2016 is the version where we finally have a properly functioning index that actually delivers on the promise on OLTP and DW workloads running side by side without compromising basic design practices like referential integrity.

Comparisons

Datawarehouse Workload

I created a test table on a VM running Windows 2012 and SQL 2016 with 3 GB RAM. The PK table is 1000 rows and the FK table is 2.5 M rows approx. 1.7 M rows qualify for the below query.

I create a Non Clustered Columnstore index and run the query and the average query execution time is 408ms.

Now I drop the Nonclustered columnstore index and replace it with a Clustered columnstore index and the average query execution time is now 293 ms.

Now I drop the Clustered columnstore index and replace it with a traditional index with key columns on id and Qty and included columns on price and Logdate supplemented with a clustered index on id giving the index the best possible chance to perform. The average query execution time is 818 ms

OLTP WORKLOAD

Now we explore the OLTP workload using the same indexes and notice that the Nonclustered columnstore index performs a scan on the table and there is a missing index suggestion to improve the seek behavior.

Next we try creating the traditional Non Clustered index and execute the query

The final numbers

Read performance

**This number gets better with the number of rows added.

Write performance

While I did see some decrease in write performance for a combination for Non Clustered index with Non Clustered columnstore index I don’t think it is significant enough to be of concern. If you have a high concurrency system where these times matter then you’re probably better off not hosting both OLTP and DW on the same database to begin with.

My Code

select cd.country,cd.city,cd.Currency, ft.Logdate , sum(ft.price ) as price
from [dbo].custinfo cd
inner join fk_table ft
on cd.id = ft.id
where ft.Qty >= 3 and ft.Qty < = 8 Group by cd.country,cd.city,cd.Currency,ft.LogdateGo select cd.country,cd.city,cd.Currency, ft.Logdate , sum(ft.price ) as price from [dbo].custinfo cd inner join fk_table ft on cd.id = ft.id where cd.id= round(rand()*1000,0) Group by cd.country,cd.city,cd.Currency,ft.Logdate

Please Consider Subscribing

Subscribe