First of all, defining the terms, a row store table or index is a classical table without any index (heap) or with a CI (clustered index) in a row store format. This was not regularly mentioned or discussed, because it was the only way for years…(decades…). Except starting with SQL Server 2014 it was the first introduced a new data store format: “the CCI – clustered columnstore index (in SQL 2012 it was read-only, so nonclustered columnstore index-NCCI). Therefore, the columnstore index is a new type of “index” (or table storage) that stores data in a columnar format.
A good use of a clustered columnstore index is in data warehouse workloads, because it improves query performance up to 10 times, ss well as that it reduces the physical storage cost (a classical table occupying 50 GB of storage with columnstore index would be around 5-7 GB). The columnstore data format is a highly compressed data format that has several benefits from the analytical standpoint.
Benefits:
- faster (analytical) queries (faster table joins and group by-type of queries)
- (new) batch execution mode (a.k.a. vectorized execution)
- reduces physical I/O (when querying only some – not all – table’s columns)
- smaller memory footprint (because of data compression)
Not to use for:
- operational workloads (lots of small no. of rows to insert – trickle insert)
- large updates, or delete (instead truncate and bulk insert) – large meaning in % of total table rows
Table Partitioning
So for smaller tables, say up to 50-150 million of rows (mostly depending on the number of columns, also on hardware you have available) you can just create a columnstore index from a heap table or a clustered index table (with the drop_existing hint). In having larger tables you might want to consider using table (index) partitioning. Partitioning means just slicing the large table in more manageable parts – still using the columnstore index (compression). We dive into code in the next lines, respectively. Examples work in the WideWorldImporters sample database.
Code for creating a columnstore index (on a heap/unindexed table):
CREATE CLUSTERED COLUMNSTORE INDEX [CCI_Order] ON [Fact].[Order] WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0)
If you already have a clustered index on the table, you just change the drop_existing hint to on.
CREATE CLUSTERED COLUMNSTORE INDEX [CCI_Order] ON [Fact].[Order] WITH (DROP_EXISTING = ON, COMPRESSION_DELAY = 0)
Code for creating a partitioned columnstore index:
CREATE PARTITION FUNCTION [pf_OrderDate](date) AS RANGE LEFT FOR VALUES (N'2013-12-31', N'2014-12-31', N'2015-12-31', N'2016-12-31', N'2017-12-31'); CREATE PARTITION SCHEME [ps_OrderDate] AS PARTITION [pf_OrderDate] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY] ,[PRIMARY]); select top 0 * into [Fact].[Order_CCI_part] from [Fact].[Order]; CREATE CLUSTERED COLUMNSTORE INDEX [CCI_Order_part] ON [Fact].[Order_CCI_part] WITH (DROP_EXISTING = off, COMPRESSION_DELAY = 0) on [ps_OrderDate]([Order Date Key]);
Msg 35316, Level 16, State 1, Line 21
The 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.
Oh, wow… partition-aligned…soo, what? – was also my reaction when I was first confornted with the error. But, when you understand what SQL Server is trying to do, or just how to tell it what you want it to do is not that hard.
We solve the partition alignment problem by creating a clustered index on the same partititioning function and then create a clustered columnstore index with the drop_existing hint on. Let’s do it.
CREATE PARTITION FUNCTION [pf_OrderDate](date) AS RANGE LEFT FOR VALUES (N'2013-12-31', N'2014-12-31', N'2015-12-31', N'2016-12-31', N'2017-12-31'); CREATE PARTITION SCHEME [ps_OrderDate] AS PARTITION [pf_OrderDate] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY] ,[PRIMARY]); select top 0 * into [Fact].[Order_CCI_part] from [Fact].[Order]; CREATE CLUSTERED INDEX [CCI_Order_part] ON [Fact].[Order_CCI_part] ( [Order Date Key] )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [ps_OrderDate]([Order Date Key]) CREATE CLUSTERED COLUMNSTORE INDEX [CCI_Order_part] ON [Fact].[Order_CCI_part] WITH (DROP_EXISTING = ON, COMPRESSION_DELAY = 0)
This code will now work. The difference is in the creation of a clustered index so as to align partitions and then on top create the columnstore index.
Inserting data
When inserting lots of data (over 100.000 rows or over 1.000.000 rows) you should always bulk insert which is quite straightforward to do (just use tablock hint). This means the engine will automatically compress data (may be CPU instensive) before writing data to the disk – this is what you want. This i sthe faster way. The other way inserting without the tablock hint triggers the old “row by row” insertion which is bad for inserting in the columstore index, because it will go in two steps, first, classical insertion and when it reaches some treshold (100.000 rows), and only then the compression process will be triggered reading the rows in memory, applying the compression and writing data to the disk. Hence, you’d be doing writing 2 times to the disk.
Inserting in a columnstore index:
INSERT INTO [WideWorldImportersDW].[Fact].[Order_CCI] WITH(TABLOCK) SELECT [Order Key] ,[City Key] ,[Customer Key] ,[Stock Item Key] ,[Order Date Key] ,[Picked Date Key] ,[Salesperson Key] ,[Picker Key] ,[WWI Order ID] ,[WWI Backorder ID] ,[Description] ,[Package] ,[Quantity] ,[Unit Price] ,[Tax Rate] ,[Total Excluding Tax] ,[Tax Amount] ,[Total Including Tax] ,[Lineage Key] FROM [WideWorldImportersDW].[Fact].[Order]
The important part is the tablock hint, so as to bypass the sequential write and to write directly in columstore format (applying data compression) – this way the operation is much faster.
Inserting in a partitioned columnstore index:
So for insertion into a partitioned table (hence: also with columnstore compression) is not just trivial. It includes a couple of steps to make it enterprise grade acceptable. These include at least these:
- Insert part of data (same partition data) in a work non-partitioned table (that has CCI applied)
- Call the Switch partition statement – switch the work table to a partition from the partitioned table
- Clean the work table and do it all over again for all the data (partitions)
There are some technical details (creating constraints and dropping them) that go as part of the main step, but all in all, these three steps are crucial to a partitioned insert in a manageable and quick way.
declare @currDate varchar(10) = '2013-12-31' declare @currDate_next varchar(10) declare @maxYear varchar(10) = (select convert(date,DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0))) declare @msg varchar(max), @SQLStmt nvarchar(max) declare @debug bit = 1 SET ANSI_WARNINGS OFF while convert(date,@currDate) < convert(date,@maxYear) begin set @currDate_next = convert(varchar,dateadd(year, +1, convert(date,@currDate))) set @msg = 'Current: ' + @currDate + ', maxYear: ' + @maxYear set @msg = @msg + char(13) + char(10) + 'Date condition: dateCol > ' + @currDate + ' and dateCol <= ' + @currDate_next RAISERROR(@msg, 0, 1) WITH NOWAIT if (select count(*) from [Fact].[Order_CCI_switch]) != 0 begin RAISERROR('The work/switch table is not empty!', 0, 10) break end -- clean variables set @SQLStmt = '' set @SQLStmt = ' alter table [Fact].[Order_CCI_switch] drop constraint if exists OrderDateCheck; ' print @SQLStmt if @debug = 0 exec sp_executesql @SQLStmt set @SQLStmt = ' insert into [Fact].[Order_CCI_switch] with(tablock) select * from [Fact].[Order] where [Order Date Key] > ''' + @currDate + ''' and [Order Date Key] <= ''' + @currDate_next + '''' print @SQLStmt if @debug = 0 exec sp_executesql @SQLStmt set @SQLStmt = ' alter table [Fact].[Order_CCI_switch] add constraint OrderDateCheck check ([Order Date Key] > convert(date,''' + @currDate + ''') and [Order Date Key] <= convert(date,''' + @currDate_next + ''') and [Order Date Key] is not null) ' print @SQLStmt if @debug = 0 exec sp_executesql @SQLStmt --select $partition.[pf_OrderDate](convert(varchar,@currDate)) --select min(daT_knj), max(dat_knj) from [RP_PROMET_ALL_YEAR_CCI_work] set @SQLStmt = ' alter table [Fact].[Order_CCI_switch] switch to [Fact].[Order_CCI_part] partition ' + convert(varchar, $partition.[pf_OrderDate](@currDate_next)) + ' ' print @SQLStmt if @debug = 0 exec sp_executesql @SQLStmt set @currDate = dateadd(year, 1, convert(date,@currDate)) end
Hope this overview gets you started in giving legacy databases a new shine, saving disk space and improving query speed!
Hi,
In your first example, you create the clustered index to align and sort
the data into the CCI partitions. This pattern I do see in other tutorials.
Data ends up sorted and thus partition elimination work properly.
— step 1: make the table
SELECT TOP 0 * INTO [Fact].[Order_CCI_part] FROM [Fact].[Order];
— step 2: rowstore sort by date
CREATE CLUSTERED INDEX [CCI_Order_part] ON [Fact].[Order_CCI_part]
(
[Order Date Key]
) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
ON [ps_OrderDate]([Order Date Key])
— step 3: insert data
INSERT INTO [WideWorldImportersDW].[Fact].[Order_CCI] WITH(TABLOCK)
SELECT
[Order Key]
,[City Key]
,[Customer Key]
,[Stock Item Key]
,[Order Date Key]
,[Picked Date Key]
,[Salesperson Key]
,[Picker Key]
,[WWI Order ID]
,[WWI Backorder ID]
,[Description]
,[Package]
,[Quantity]
,[Unit Price]
,[Tax Rate]
,[Total Excluding Tax]
,[Tax Amount]
,[Total Including Tax]
,[Lineage Key]
FROM [WideWorldImportersDW].[Fact].[Order]
— step 4: replace rowstore with columnstore
CREATE CLUSTERED COLUMNSTORE INDEX [CCI_Order_part]
ON [Fact].[Order_CCI_part]
WITH (DROP_EXISTING = ON, COMPRESSION_DELAY = 0)
But in your partitioned example,
a. It doesn’t seem complete. You never create table [Fact].[Order_CCI_switch]
b. You don’t load first into a rowstore in order to sort the data, and then replace index with CCI
I’m hoping that you could post a full example.
Thanks, Chris
LikeLike
Thanks for your comments.
It should be complete, one should load the data into the _switch table (which should already contain the CCI index, as the targert table also contains it), and then switch into the partitioned target table. Why would you want to first insert the data into rowstore? It is not needed as it would just make the process slower with +1 (not needed) step.
LikeLike
If you don’t first load into a rowstore, then the data won’t be sorted in the columnstore and you won’t get the benefits of segment elimination – one of the biggest benefits of CCI. See
https://joyfulcraftsmen.com/blog/cci-how-to-load-data-for-better-columnstore-segment-elimination/
and
https://www.sqlservercentral.com/steps/stairway-to-columnstore-indexes-level-8-optimizing-clustered-columnstore-indexes
LikeLike
You will have a hard time converting a large table (i.e 3 billion rows) with rowstore into a partitioned CCI, as it will not fit memory. And this is the exact point why I created and wrote this post, as an alternative with partitioned CCI table, where partition elimination will happen, and is a very good trade of in terms of query exec, and also table administration.
What are you going to do when it will be required to add a new column to this table, hence a full load? For us in the case of a 3B rows table it took to full reload 11 hours on a very powerful machine. I don’t imagine how much time it would take also sorting everything to perfection (MAXDOP 1) everything was sorted, 2 days, 4 days?… anyways not feasible in a real case scenario.
LikeLike
So to make your example complete, i s this what’s needed?
1. add code to create [Fact].[Order_CCI_switch]
2. add code at beginning of loop to empty [Fact].[Order_CCI_switch]
LikeLike
Also meant to say that even as an incomplete example, it’s still the best such example I’ve found. Thanks.
LikeLiked by 1 person
One more question. I found your example when looking for examples of maintaining
a partitioned CCI, where there’s a need to load more recent data and then swap
into a partition. But for the initial data load, you don’t have to do that, right?
You can just load the partitioned CCI and let the engine manage the partitioning.
For example:
http://www.nikoport.com/2017/01/06/columnstore-indexes-part-94-use-partitioning-wisely/
where the steps are basically:
1. create partition function
2. create partition scheme
3. create new table
4. create clustered index on date col
5. copy the data to new table
6. replace rowstore index with cci
LikeLike
I agree with you, but your 6th step will consume a very large amount of memory if you don’t slice the table in partitions and do as I explain in the post above. If you can manage that amount of RAM or if the table is not especailly big, by all means you can. But then you probably also don’t need a partitioned CCI table.
LikeLike
To respond to your “what do you do if a new column is added” question, the answer is that we’d never add a new column to the base table. If additional data is needed in a report, it would be in a new table which would be joined to the base table. We already do that for “adjustments” data.
To respond to your “You will have a hard time converting a large table”, I will answer “to be determined”. I will do an experiment where I load it without a loop, and I will do a test where I load in a loop as in your example. But my point for my initial comment here was to point out that your logic doesn’t result in sorted segments – which is very important for performant queries. After I create my version of the loop/batch sql, I’ll report back with the pseudocode.
LikeLike