Columnstore Indexes – part 102 (“CCI with Secondary Rowstore Indexes on SQL 2014”)

Continuation from the previous 101 parts, the whole series can be found at http://www.nikoport.com/columnstore/.

In SQL Server 2014 we have received a possibility to update the Columnstore Index directly with an addition of the Clustered Columnstore Index. This addition had some important limitations, between which were the impossibility of adding secondary Rowstore Indexes (making the Clustered Columnstore Index the only index on the table) and the lack of the unique constraints (

Meet the good old and absolutely underused friend – the Indexed Views, which in fact (and for some cost) will provide you with the above mentioned features.
If you absolutely need those features, follow this article to find this absolutely trivial way.

Using the good old free database ContosoRetilDW, download the backup and restore it from the C:\Install\:

Let’s convert the FactOnlineSales table to the Clustered Columnstore one, by dropping the primary key and all the foreign keys:

Here is query that I would like to optimise today, which is a rather basic SalesAmount aggregation on the daily basis for some certain orders, where we try to sort the data based on the Total Sales Amount per Day:

This query takes almost 330 ms on the average to run on my virtual machine, at the same burning 1281 ms of the CPU time, doing over incredible 36.000 LOB logical reads!

Here is the execution plan for this query:

If you run the query and look at the actual execution plan, you will think about adding a nonclustered index:

this operation will naturally fail, as expected:

We can solve and work around this problem if we build an indexed view, and we shall use it: 🙂

We shall need a unique clustered index to turn this view an indexed one:

And let us add an additional nonclustered rowstore index, to make this view performing great:

Let us re-execute the query, this time against our indexed view to see if the query is performing well or not:

This time the results are returned with a different speed – it took just 23ms of the total execution time, while spending just 31ms of the CPUT time! From the read accesses we are talking here about less than 170 logical reads. Compare these results with the original ones with 330 ms of the total execution (10 times improvement), and over 36000 LOB logical reads (over 200 times improvement).

Here is the actual execution plan that was produced for this query:

Here we have a beautiful and a simple execution plan, which delivers what we need – great performance with a relative low overall cost. And in this way we enjoy the possibility to get the best out of the 2 worlds – Columnstore & Rowstore.
Should we need to run a similar query but agains the whole dataset, it will be redirected to our Columnstore Index which will deliver great performance:

Uniqueness

Another frequent requirements is the uniqueness for the Clustered Columnstore Indexes on SQL Server 2014, and this one can be achieved by simply adding unique indexes to our indexed view (and notice that there could be more than 1 default required unique index on it).
The operation that we have already executed to make an indexed view out of the simple view is the one which shall guarantee the uniqueness for the column OnlineSalesKey:

Should we try to insert a duplicate into our table FactOnlineSales, here by copying one of the rows into our test table FactOnlineSales:

The error message is very clear that it the uniqueness of the data is enforced and will be preserved by the unique index in our view:

Now, if only we could build primary keys/foreign keys to the views … Ok, Ok – I know, this is a different topic …

Final Thoughts

The only thing to add here is that one should evaluate costs very carefully, but if you are dealing with reasonably (not huge) tables in the low million rows, this might be a great solution for your unique constraints and secondary indexes.

to be continued with Columnstore Indexes – part 103 (“Partitioning 2016 vs Partitioning 2014”)

Leave a Reply

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